11 ms·

I've not been able to reproduce this behavior. Maybe someone else will have better luck. create unlogged table foo (id int primary key generated by default

I've not been able to reproduce this behavior. Maybe someone else will have better luck. create unlogged table foo (id int primary key generated by default as identity, organization_id int default floor(random()*10), created_at timestamptz default timestamp '2010-01-01' + random()*(timestamp '2020-01-01' - timestamp '2010-01-01')); create index on foo (created_at); create index on foo (organization_id); alter table foo add column updated_at timestamptz null default '2010-01-01 00:00:00'::timestamp without time zone + random() * ('2020-01-01 00:00:00'::timestamp without time zone - '2010-01-01 00:00:00'::timestamp without time zone); create index on foo (updated_at); insert into foo (id) select generate_series(1, 1e8); cluster foo using foo_created_at_idx; vacuum analyze; explain analyze select * from foo where organization_id = 1 order by updated_at limit 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.57..169.50 rows=10 width=24) (actual time=0.088..0.242 rows=10 loops=1) -> Index Scan using foo_updated_at_idx on foo (cost=0.57..173267047.88 rows=10256672 width=24) (actual time=0.086..0.237 rows=10 loops=1) Filter: (organization_id = 1) Rows Removed by Filter: 73 Planning Time: 0.168 ms Execution Time: 0.269 ms

zac23or 3y ago
You reproduced it. See the index being used. foo_updated_at_idx, not foo_organization_id_idx. See a FILTER on Organization_id, not a Cond index. If you create an index using Organization_id and updated_at the plan changes to: Limit (cost=0.42..3.26 rows=10 width=24) (actual time=0.013..0.024 rows=10 loops=1) -> Index Scan using foo_organization_id_updated_at_idx on foo (cost=0.42..2011.50 rows=7063 width=24) (actual time=0.012..0.023 rows=10 loops=1) Index Cond: (organization_id = 1) Planning Time: 0.154 ms Execution Time: 0.033 ms Now the plan is using Index Cond, not filter.
- dventimi 3y ago
  Without adding that index, what's the correct index to use?
  - zac23or 3y ago
    foo_organization_id_idx is the correct index, in my case using foo_organization_at_idx is 100x, 1000x faster. 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.
    - dventimi 3y ago
      With only two indexes, one on organization_id and one on updated_at, for the query 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.
pgaddict 3y ago
Now try adding a new organization with ID 11 and timestamps on the tail end (>2020-01-01). And query for that ID.
- dventimi 3y ago
  Sure. -- 1e7 entries for organization_id = 10 distributed evenly over the 10 -- years after '2020-01-01 00:00:00' as requested insert into foo ( organization_id, created_at, updated_at) select 10, --9 was the previous max organization_id so presumably 10 is OK rather than 11 generate_series('2020-01-01 00:00:00'::timestamp, '2020-01-01 00:00:00'::timestamp + interval '10 years', (interval '10 years')/1e7), generate_series('2020-01-01 00:00:00'::timestamp, '2020-01-01 00:00:00'::timestamp + interval '10 years', (interval '10 years')/1e7); cluster foo using foo_created_at_idx; --should be a no-op but why not vacuum analyze; --same thing create index on foo (updated_at) where organization_id = 10; --stats will be highly-skewed so we had better fix that explain analyze select * from foo where organization_id = 10 order by updated_at limit 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..23.35 rows=10 width=57) (actual time=0.073..0.079 rows=10 loops=1) -> Index Scan using foo_updated_at_idx1 on foo (cost=0.43..23495662.12 rows=10254706 width=57) (actual time=0.071..0.075 rows=10 loops=1) Planning Time: 0.185 ms Execution Time: 0.101 ms BTW, SQLite was no better at solving this riddle. It also failed miserably in its query plan until I created the same partial index.
  - pgaddict 3y ago
    Not sure, but I see this ... I simplified the SQL a little bit - I don't think it's necessary to have two timestamps and cluster on one of them. The other timestamp is not correlated, so the access through that index will be random anyway. And I used 10M rows only. create unlogged table foo ( id int primary key generated by default as identity, organization_id int, created_at timestamptz ); insert into foo (id, organization_id, created_at) select i, floor(random()*10), timestamp '2010-01-01' + random()*(timestamp '2020-01-01' - timestamp '2010-01-01') from generate_series(1, 1e7) s(i); insert into foo (id, organization_id, created_at) select 1e7 + i, 10, timestamp '2020-01-01' + random()*(timestamp '2021-01-01' - timestamp '2020-01-01') from generate_series(1, 100000) s(i); -- see the timestamp range for each org select organization_id, min(created_at), max(created_at) from foo group by 1 order by 1; create index on foo (created_at); vacuum analyze; checkpoint; set max_parallel_workers_per_gather = 0; explain analyze select * from foo where organization_id = 1 order by created_at limit 10; explain analyze select * from foo where organization_id = 10 order by created_at limit 10; For me, this produces: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.43..5.62 rows=10 width=16) (actual time=0.072..0.378 rows=10 loops=1) -> Index Scan using foo_created_at_idx on foo (cost=0.43..505826.61 rows=975331 width=16) (actual time=0.071..0.375 rows=10 loops=1) Filter: (organization_id = 1) Rows Removed by Filter: 76 Planning Time: 0.040 ms Execution Time: 0.390 ms (6 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..43.61 rows=10 width=16) (actual time=30316.079..30316.117 rows=10 loops=1) -> Index Scan using foo_created_at_idx on foo (cost=0.43..505826.61 rows=117161 width=16) (actual time=30316.078..30316.114 rows=10 loops=1) Filter: (organization_id = 10) Rows Removed by Filter: 10000000 Planning Time: 0.041 ms Execution Time: 30316.136 ms (6 rows) So perhaps there's something "wrong" with your data, because there's no "Filter" or "Rows Removed by Filter" in your query plans.

zac23or 3y ago

You reproduced it. See the index being used. foo_updated_at_idx, not foo_organization_id_idx. See a FILTER on Organization_id, not a Cond index. If you create an index using Organization_id and updated_at the plan changes to: Limit (cost=0.42..3.26 rows=10 width=24) (actual time=0.013..0.024 rows=10 loops=1) -> Index Scan using foo_organization_id_updated_at_idx on foo (cost=0.42..2011.50 rows=7063 width=24) (actual time=0.012..0.023 rows=10 loops=1) Index Cond: (organization_id = 1) Planning Time: 0.154 ms Execution Time: 0.033 ms Now the plan is using Index Cond, not filter.

dventimi 3y ago
Without adding that index, what's the correct index to use?
- zac23or 3y ago
  foo_organization_id_idx is the correct index, in my case using foo_organization_at_idx is 100x, 1000x faster. 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.
  - dventimi 3y ago
    With only two indexes, one on organization_id and one on updated_at, for the query 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.

pgaddict 3y ago

Now try adding a new organization with ID 11 and timestamps on the tail end (>2020-01-01). And query for that ID.

dventimi 3y ago
Sure. -- 1e7 entries for organization_id = 10 distributed evenly over the 10 -- years after '2020-01-01 00:00:00' as requested insert into foo ( organization_id, created_at, updated_at) select 10, --9 was the previous max organization_id so presumably 10 is OK rather than 11 generate_series('2020-01-01 00:00:00'::timestamp, '2020-01-01 00:00:00'::timestamp + interval '10 years', (interval '10 years')/1e7), generate_series('2020-01-01 00:00:00'::timestamp, '2020-01-01 00:00:00'::timestamp + interval '10 years', (interval '10 years')/1e7); cluster foo using foo_created_at_idx; --should be a no-op but why not vacuum analyze; --same thing create index on foo (updated_at) where organization_id = 10; --stats will be highly-skewed so we had better fix that explain analyze select * from foo where organization_id = 10 order by updated_at limit 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..23.35 rows=10 width=57) (actual time=0.073..0.079 rows=10 loops=1) -> Index Scan using foo_updated_at_idx1 on foo (cost=0.43..23495662.12 rows=10254706 width=57) (actual time=0.071..0.075 rows=10 loops=1) Planning Time: 0.185 ms Execution Time: 0.101 ms BTW, SQLite was no better at solving this riddle. It also failed miserably in its query plan until I created the same partial index.
- pgaddict 3y ago
  Not sure, but I see this ... I simplified the SQL a little bit - I don't think it's necessary to have two timestamps and cluster on one of them. The other timestamp is not correlated, so the access through that index will be random anyway. And I used 10M rows only. create unlogged table foo ( id int primary key generated by default as identity, organization_id int, created_at timestamptz ); insert into foo (id, organization_id, created_at) select i, floor(random()*10), timestamp '2010-01-01' + random()*(timestamp '2020-01-01' - timestamp '2010-01-01') from generate_series(1, 1e7) s(i); insert into foo (id, organization_id, created_at) select 1e7 + i, 10, timestamp '2020-01-01' + random()*(timestamp '2021-01-01' - timestamp '2020-01-01') from generate_series(1, 100000) s(i); -- see the timestamp range for each org select organization_id, min(created_at), max(created_at) from foo group by 1 order by 1; create index on foo (created_at); vacuum analyze; checkpoint; set max_parallel_workers_per_gather = 0; explain analyze select * from foo where organization_id = 1 order by created_at limit 10; explain analyze select * from foo where organization_id = 10 order by created_at limit 10; For me, this produces: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.43..5.62 rows=10 width=16) (actual time=0.072..0.378 rows=10 loops=1) -> Index Scan using foo_created_at_idx on foo (cost=0.43..505826.61 rows=975331 width=16) (actual time=0.071..0.375 rows=10 loops=1) Filter: (organization_id = 1) Rows Removed by Filter: 76 Planning Time: 0.040 ms Execution Time: 0.390 ms (6 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..43.61 rows=10 width=16) (actual time=30316.079..30316.117 rows=10 loops=1) -> Index Scan using foo_created_at_idx on foo (cost=0.43..505826.61 rows=117161 width=16) (actual time=30316.078..30316.114 rows=10 loops=1) Filter: (organization_id = 10) Rows Removed by Filter: 10000000 Planning Time: 0.041 ms Execution Time: 30316.136 ms (6 rows) So perhaps there's something "wrong" with your data, because there's no "Filter" or "Rows Removed by Filter" in your query plans.