Comment by throwaway613745
1 month ago
Dont' forget to ANALYZE your tables sometimes too.
Just recently was trying to optimize a 12s index scan, turns out I didn't need to change anything about the query I just had to update the table statistics. 12s down to 100ms just form running ANALYZE (no vacuum needed).
And make sure your `random_page_cost` is about 1.1 if running on an SSD or if >~98% of your hot pages fit in memory. Rather than 4 by default which makes the planner afraid of using indexes.
We added a weekly job to do that during low activity hours as a preventive measure. It's not often the planner incorrectly goes for a table scan due to bad statistics but when it does it's a big issue.
So we just so it proactively now.
there are ways to see out which indexes get used and which are not. It's surprising to find out table scans or incorrect indexes getting used.