In all my queries, the correct index is in the where clause, not the order by clause: 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.
Of course, there may be cases where the index in the order by clause is the best one to use, but in my use this never happened.
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. explain query plan select * from foo where organization_id = 1 order by updated_at limit 10;
QUERY PLAN
|--SEARCH foo USING INDEX foo_organization_id_idx (organization_id=?)
`--USE TEMP B-TREE FOR ORDER BY
Just as with Postgres, it starts by ordering the data, though in this case it uses a TEMP B-TREE rather than the index on updated_at (not sure why it's doing that). Then, it filters on organization_id=1, though in this case it uses the index to filter on organization_id. That takes 3.8s on my machine (postgres took ~260ms for the same operation). If I drop the index on organization_id, this is the query plan QUERY PLAN
`--SCAN foo USING INDEX foo_updated_at_idx
It's back to scanning the foo_updated_at_idx index, just like Postgres does. Interestingly, this takes mere milliseconds. Dropping the index on organization_id actually improves the performance.Finally, just for good measure, if I also drop the foo_updated_at_idx index, this is the query plan
QUERY PLAN
|--SCAN foo
`--USE TEMP B-TREE FOR ORDER BY
Running the query in this case takes 4.2s. It's back to using a TEMP B-TREE index for ORDER BY clause, but it has to scan the table.For me, it's hard to draw any hard-and-fast lessons from this. In my experience, database performance is highly context dependent. It depends on the volume and distribution of data, the hardware, its current load, the indexes in place, and how the query is written. These little experiments to me just serve to confirm that.
I guess the one hard-and-fast lesson I personally take from this is not to be so certain that there is "a correct query plan" and that I or really anyone knows better than the database what that is. Sometimes in certain circumstances that may be true, but usually it's not.
> is not to be so certain that there is "a correct query plan"
Yes, data distribution, type, row size, machine power, SSD vs HD can affect which is the “best plan”.
insert into foo (id, created_at, updated_at) select generate_series(1, 4000000),
generate_series('2010-01-01 00:00:00'::timestamp, now(), ('2010-01-01 00:00:00'::timestamp - now())/4000000),
generate_series('2010-01-01 00:00:00'::timestamp, now(), ('2010-01-01 00:00:00'::timestamp - now())/4000000);
cluster foo using foo_organization_id_idx;
vacuum analyze;
Clustered by organization, the best index is organization_id_idx;Also, if each page contains data from all organizations then sounds very much like it's not clustered by organization_id. Can you clarify?
The default value of the column:
organization_id int default floor(random()*10)
> Also, if each page contains data from all organizations then sounds very much like it's not clustered by organization_idThe sample data is clustered by foo_created_at_idx, I changed it to be clustered by foo_organization_id_idx
truncate table foo; --start over
drop index foo_updated_at_idx1; --drop the partial index for organization_id = 10. don't need it.
insert into foo ( --insert the data exactly as prescribed
id,
created_at,
updated_at
)
select
generate_series(1, 4000000),
generate_series('2010-01-01 00:00:00'::timestamp, now(), ('2010-01-01 00:00:00'::timestamp - now())/4000000),
generate_series('2010-01-01 00:00:00'::timestamp, now(), ('2010-01-01 00:00:00'::timestamp - now())/4000000);
cluster foo using foo_organization_id_idx; --cluster it by organization_id exactly as prescribed
vacuum analyze; --ditto
explain analyze select * from foo where organization_id = 9 order by updated_at limit 100000 offset 0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=30928.71..42596.19 rows=100000 width=57) (actual time=35.177..52.661 rows=100000 loops=1)
-> Gather Merge (cost=30928.71..69134.11 rows=327452 width=57) (actual time=35.173..49.672 rows=100000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=29928.68..30338.00 rows=163726 width=57) (actual time=25.208..27.079 rows=33940 loops=3)
Sort Key: updated_at
Sort Method: external merge Disk: 9808kB
Worker 0: Sort Method: external merge Disk: 5112kB
Worker 1: Sort Method: external merge Disk: 5144kB
-> Parallel Index Scan using foo_organization_id_idx on foo (cost=0.43..9592.75 rows=163726 width=57) (actual time=0.065..12.548 rows=133674 loops=3)
Index Cond: (organization_id = 9)
Planning Time: 0.173 ms
Execution Time: 55.360 ms