select * from foo where organization_id = 1 order by updated_at limit 10 offset 0;
I think foo_updated_at_idx is the better choice and not foo_organization_id_idx. If it chose foo_organization_id_idx then there would be ~1e7 matching rows whose pages would have to be randomly accessed and then sorted. I'll try the same exercise in SQLite, but right now I can't see how it could possibly do better.> When I removed the use of the wrong index, (ORDER BY update_at + range '0 day' forces the use of the correct index), the query used less resources and was much faster.
I don't know about the "range" keyword. When I try that in PostgreSQL, I get a syntax error, though the "interval" keyword works. However, that produces a much worse plan:
explain analyze select * from foo where organization_id = 1 order by updated_at + interval '0 day' limit 10 offset 0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1759524.82..1759525.99 rows=10 width=64) (actual time=2008.442..2011.879 rows=10 loops=1)
-> Gather Merge (cost=1759524.82..2740241.49 rows=8405556 width=64) (actual time=1983.144..1986.580 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1758524.80..1769031.74 rows=4202778 width=64) (actual time=1974.717..1974.718 rows=7 loops=3)
Sort Key: ((updated_at + '00:00:00'::interval))
Sort Method: top-N heapsort Memory: 27kB
Worker 0: Sort Method: top-N heapsort Memory: 27kB
Worker 1: Sort Method: top-N heapsort Memory: 27kB
-> Parallel Seq Scan on foo (cost=0.00..1667704.28 rows=4202778 width=64) (actual time=36.576..1573.855 rows=3332288 loops=3)
Filter: (organization_id = 1)
Rows Removed by Filter: 30001046
Planning Time: 0.079 ms
JIT:
Functions: 14
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.734 ms, Inlining 61.765 ms, Optimization 44.619 ms, Emission 28.563 ms, Total 135.681 ms
Execution Time: 2012.205 ms
As expected, because of the high-cardinality (~1e7) for any given organization_id, because the pages will have to be accessed randomly, and because they all have to be sorted before the limit can be applied, there's no benefit to the using foo_organization_id_idx. Sure enough, it has to do a table scan, then a sort, before it can apply the limit, and it's about 6000x slower.