Conversation

Notices

1. Yukkuri (iamtakingiteasy@eientei.org)'s status on Thursday, 04-Jan-2024 18:31:12 JST Yukkuri

   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.

   •  likes this.
   •  (mint@ryona.agency)'s status on Thursday, 04-Jan-2024 18:33:38 JST 

     in reply to
     @iamtakingiteasy Is it applicable to RUM-enabled instances as well?
   • Yukkuri (iamtakingiteasy@eientei.org)'s status on Thursday, 04-Jan-2024 18:58:30 JST Yukkuri

     in reply to

     • 
     @mint Eientei has RUM enabled, but RUM's tsvector is applied to objects table, rather than activities. Still, if you observe search queries opting for sequential scan instead of using rum index, increasing fts_content sampling rate may help nudge the planner in the right direction, but use explain analyze to verify it's indeed filtering instead of doing bitmap/index scan and beware of increased analyze times, greater accuracy for planner estimates isn't free.
      likes this.