Yukkuri on Nostr: Increasing postgres statistic level turned out to be way more useful than ...
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.
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.