5 ms·
The sample data is distributed equally, each page contains data from all organizations. The actual data is much more grouped by organization, like this: inse
by zac23or 3y ago
The sample data is distributed equally, each page contains data from all organizations.
The actual data is much more grouped by organization, like this:
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;
- dventimi 3y agoOK. 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
- dventimi 3y agoI see how created_at and updated_at are generated here but I don't see how organization_id is being generated. How is that being done? Also, if each page contains data from all organizations then sounds very much like it's not clustered by organization_id. Can you clarify?
- zac23or 3y ago> how organization_id is being generated 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_id The sample data is clustered by foo_created_at_idx, I changed it to be clustered by foo_organization_id_idx