22 ms·
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
by throwaway613745 10mo 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).
- tehlike 10mo agothere 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.
- karolinepauls 10mo agoAnd 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.
- magicalhippo 10mo agoWe 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.