3 ms·
Postgres' query planner is likely considering random vs sequential page costs, and preferring an index scan on created_at. What are the current values of `rand
by AaronFriel 3y ago
Postgres' query planner is likely considering random vs sequential page costs, and preferring an index scan on created_at.
What are the current values of `random_page_cost` and `seq_page_cost`?
SHOW seq_page_cost;
SHOW random_page_cost;
The default is typically 4, and in practice with modern disks you should use a lower value closer to 1.
- zac23or 3y agoseq_page_cost is 1; random_page_cost is 2; I have many other problems with the planner, this is the most absurd due to the simplicity of the query.
- zacmps 3y agoOn an SSD I'd drop random to 1-1.2.
- franckpachot 3y agoIf changing random_page_cost from 4 to 2 makes a difference, then probably there are no good indexes. The choice between Seq Scan and Index Scan should be obvious without depending on small adjustments or one day, with slightly different data distribution the plan will flip to a bad one
- zac23or 3y agoI changed random_page_cost to 1! The query continue to use the wrong index.