Increasing postgres statistic level turned out to be way more useful than anticipated; in case of a new user, with page size exceeding number of activities matching user timeline, planner was estimating over 200-300k rows and had to do filtering on id desc nulls last index instead of using bitmap scan on recipients gin index when filtering for recipients intersection, causing query to stall for several minutes.
alter table activities alter column recipients set statistics 1000;
analyze activities;
(a 10 times increase from base 100)
reduced estimates to ~1-3k rows and query time to 20-50ms with just bitmap scans of respective indices.
Another option might've been to force planner to scan ordering index after applying bitmap scan with putting everything but order/limit into subquery, but for it to work, subquery must include all Ecto preloads, which isn't something Ecto currently supports.
ORMs are a lie, as usual.
Yukkuri /
npub1j0…lw8rl
2024-01-04 08:55:59
Author Public Key
npub1j05dh9hemkteevv6env2ssv6zrlhj9s904zzknsmylqm2p03w4qsxlw8rlPublished at
2024-01-04 08:55:59Event JSON
{
"id": "25eb60b8aab9a43621db731cc305b4037f4011033b008f2bfd5800ae0673e8eb",
"pubkey": "93e8db96f9dd979cb19accd8a8419a10ff7916057d442b4e1b27c1b505f17541",
"created_at": 1704354959,
"kind": 1,
"tags": [
[
"proxy",
"https://eientei.org/objects/d6dcb25f-a7e1-4c5e-bccc-34264a1ad325",
"activitypub"
]
],
"content": "Increasing postgres statistic level turned out to be way more useful than anticipated; in case of a new user, with page size exceeding number of activities matching user timeline, planner was estimating over 200-300k rows and had to do filtering on id desc nulls last index instead of using bitmap scan on recipients gin index when filtering for recipients intersection, causing query to stall for several minutes.\n\nalter table activities alter column recipients set statistics 1000;\nanalyze activities;\n\n\n(a 10 times increase from base 100)\n\nreduced estimates to ~1-3k rows and query time to 20-50ms with just bitmap scans of respective indices.\n\nAnother option might've been to force planner to scan ordering index after applying bitmap scan with putting everything but order/limit into subquery, but for it to work, subquery must include all Ecto preloads, which isn't something Ecto currently supports.\n\nORMs are a lie, as usual.",
"sig": "f8217fbfd3f0954807e960bbb748f27dc6d519d0808525c2ee83dc46f7a963595d0f4fb283482fc1a041c24575eeb6ad9c5d623b61c67f69d911bb7980e8380c"
}