3 ms·
OK. I dropped the data, recreated it according to the recipe, and re-ran the query. The plan is different, using the index on organization_id rather than on u
by dventimi 3y ago
OK. I dropped the data, recreated it according to the recipe, and re-ran the query. The plan is different, using the index on organization_id rather than on updated_at, but that's to be expected given that the table is clustered differently. I agree that given this new way of organizing the data, foo_organization_id_idx is a better candidate for the "best index", but that's exactly what Postgres picks. Sorry, but I continue not to see the problem.
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