4 ms·

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.
  - dventimi 3y ago
    I dumped the data from postgres and loaded into a similar (same name, same columns, in the same order, same columns indexed) it works in much the same way. 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.
    - zac23or 3y ago
      In my old tests, with data clustered by organization_id, the SQLite and SQLServer run the query very fast. > 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”.
  - 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;

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.
- dventimi 3y ago
  I dumped the data from postgres and loaded into a similar (same name, same columns, in the same order, same columns indexed) it works in much the same way. 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.
  - zac23or 3y ago
    In my old tests, with data clustered by organization_id, the SQLite and SQLServer run the query very fast. > 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”.
- 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;