5 ms·

My biggest problem with Postgres is the Planner/Optimizer. The worst example is a super simple query: SELECT * FROM TABLE FOO WHERE ORGANIZATION_ID = 10 ORDER

My biggest problem with Postgres is the Planner/Optimizer. The worst example is a super simple query: SELECT * FROM TABLE FOO WHERE ORGANIZATION_ID = 10 ORDER BY CREATED_AT LIMIT 10 OFFSET 0; Postgres sorts by created_at using an index, but uses the filter on organization_id. This organization has a million rows... without the order, the query runs in ms. In order, in seconds/minutes. Sometimes the plan changes if you change the query offset! For organization 10, the plane is goood, for organization 11, the plane is bad... Change stats, add more stats, fill the void. that doesn't solve anything. The solution is to use a calc on order by , but in the end you need to create an index using Organization_id and created_at to really fix it. I did the same test on SQLServer and SQLite, they executed the query correctly, using the correct index. I've never created an index to fix a bad plan in SQLServer, and I've used SqlServer a lot more than Postgres. And I use a Pass database, it's impossible to install extensions... I'm going to test this extension locally, if it works I'll convince the company to use it (by changing the database provider)

Izkata 3y ago
This sounds like what I described yesterday: https://news.ycombinator.com/item?id=39713239 https://news.ycombinator.com/item?id=39713239 In addition to what I described there, the way you described it, it sounds like you don't have a single index that covers both columns. That may also help if I understood right.
- zac23or 3y ago
  > it sounds like you don't have a single index that covers both columns. Yes. But other databases (SQLlite, etc.) select the correct index, without the need to create an index with both columns.
  - franckpachot 3y ago
    In all databases you will avoid bad plans (and the unpredictable performance related to plan changes) by providing the right index. You have two selective filters: WHERE and LIMIT so the right index have both
  - franckpachot 3y ago
    Maybe it is not the planner. On difference between those other databases and PostgreSQL is that their plan do not depend on how freshly the table was vacuumed. The cost of your "correct index" may becomes worse when the rows are updated until they are vacuumed.
riku_iki 3y ago
> For organization 10, the plane is goood, for organization 11, the plane is bad... that's probably because for 11, there will be more results (say 10k), and optimizer decided that it is faster to scan already sorted index with filter, than sort 10k rows. Logic of optimizer is usually reasonable, my problem with it is that it relies on cost config parameters, which I think by default are inadequate for modern servers, it is not clear how to set it correctly, and actual optimizer rules are not documented, but one need to look at PgSQL code to understand them.
- zac23or 3y ago
  > Logic of optimizer is usually reasonable I agree. My problem is that SQLite, an embedded database, seems to have a better Planner/Optimizer than Postgres. Is the development of Planner/Optimizer abandoned? > my problem with it is that it relies on cost config parameters And I don't have access to change it, as it's a PASS database...
  - pgaddict 3y ago
    Abandoned? Certainly not. But it's a complex part of a mature database product, with many existing deployments/users, which means the improvement is affecting literally everyone. And query planning in general is a hard problem. So it takes time to get new stuff in.
    - zac23or 3y ago
      I agree with everything you said, but it's frustrating that the planner fails on a seemingly trivial query ("seemingly trivial", perhaps it's a very complex problem). > So it takes time to get new stuff in. It's true, I discovered this problem in PG 9, and it's true in PG 16, if I'm not mistaken.
      - pgaddict 3y ago
        I think this very much depends on what exactly is the problem - if it's with how costing for LIMIT works (https://news.ycombinator.com/item?id=39728826 https://news.ycombinator.com/item?id=39728826), then yeah, this did not change since 9.x in a substantial way, so 16 has the same behavior. On a technical level, this is hard because the optimizer has a fairly limited amount of information (optimizer stats) - in particular, it does not know if the columns are correlated. If they are not, then this costing is perfect, and the plan is the correct one. And this assumption of independence is what most databases do by default, so we do that. But if the columns are correlated, it blows up, unfortunately. That is, it's not a very robust plan :-( But I think the really hard problem is the impact the change might have on existing systems. As I said, the problem is we have very little information to inform the decision. We could penalize this "risky" plan somehow (we don't really have any concept of "risk" separately from the cost), but inevitably that will make the query much worse for some of the systems where that plan is correct. I understand the frustration, though. (FWIW I love sqlite, it's an amazing piece of software, but claiming that it has a better optimizer based on a single query may be a bit ... premature.)
matthewbauer 3y ago
That sounds like the pg stats are messed up somehow. Try running explain analyze foo to see if it changes things. Postgres doesn’t automatically create indexes - I bet the others have some implicit index on organization_id, created_at. Otherwise they would be giving bad results in other cases. I don’t see why you can’t just make that index in postgres.
- zac23or 3y ago
  >Pg stats are messed up somehow. Try running explain analyze foo I did > Postgres doesn’t automatically create indexes Both fields have indexes (created manually). Postgres is incorrectly choosing the index in order by column, not the index in the where clause.
AaronFriel 3y ago
Postgres' query planner is likely considering random vs sequential page costs, and preferring an index scan on created_at. What are the current values of `random_page_cost` and `seq_page_cost`? SHOW seq_page_cost; SHOW random_page_cost; The default is typically 4, and in practice with modern disks you should use a lower value closer to 1.
- zac23or 3y ago
  seq_page_cost is 1; random_page_cost is 2; I have many other problems with the planner, this is the most absurd due to the simplicity of the query.
  - zacmps 3y ago
    On an SSD I'd drop random to 1-1.2.
  - franckpachot 3y ago
    If changing random_page_cost from 4 to 2 makes a difference, then probably there are no good indexes. The choice between Seq Scan and Index Scan should be obvious without depending on small adjustments or one day, with slightly different data distribution the plan will flip to a bad one
- zac23or 3y ago
  I changed random_page_cost to 1! The query continue to use the wrong index.
pgaddict 3y ago
It's really hard to say why this is happening without EXPLAIN ANALYZE (and even then it may not be obvious), but it very much seems like the problem with correlated columns we often have with LIMIT queries. The optimizer simply assumes the matching rows are "uniformly" distributed, because limit is costed as linear approximation of the startup/total cost of the input node. For example, consider this made up query plan Limit (cost=0.0..100.0 rows=10) -> Index scan (cost=0.0...10000000.0 rows=1000000) Filter: dept_id=10 The table may have many more rows - say, 100x more. But the optimizer assumes the rows with (dept_id=10) are distributed in the index, so it can scan the first 1/100000 if the index to get the 10 rows the limits needs. So it assumes the cost is 10*(0+10000000)/1000000. But chances are the dept_id=10 rows happen to be at the very end of the index, so the planner actually needs to scan almost the whole index, making the cost wildly incorrect. You can verify this by looking how far the dept_id rows are in "order by created_id" results. If there are many other rows before the 10 rows you need, it's likely this. Sadly, the optimizer is not smart enough to realize there's this risk. I agree it's an annoying robustness issue, but I don't have a good idea how to mitigate it ... I wonder what the other databases do.
- zac23or 3y ago
  > It's really hard to say why this is happening without EXPLAIN ANALYZE I can run EXPLAIN and paste it here, but it's just one of the problems with the planner (in a very basic query), I have many other problems with it. On the other hand, I've never had any other serious problems with Postgres. It's a very good database.
  - justinclift 3y ago
    > I can run EXPLAIN and paste it here ... Might as well, could turn up something useful. :)
    - pgaddict 3y ago
      I'd suggest reporting it to pgsql-performance mailing list [1], and continuing the discussion there. I'd expect more community members to join that discussion there. [1] https://www.postgresql.org/list/pgsql-performance/ https://www.postgresql.org/list/pgsql-performance/
dventimi 3y ago
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?

Izkata 3y ago

This sounds like what I described yesterday: https://news.ycombinator.com/item?id=39713239 https://news.ycombinator.com/item?id=39713239 In addition to what I described there, the way you described it, it sounds like you don't have a single index that covers both columns. That may also help if I understood right.

zac23or 3y ago
> it sounds like you don't have a single index that covers both columns. Yes. But other databases (SQLlite, etc.) select the correct index, without the need to create an index with both columns.
- franckpachot 3y ago
  In all databases you will avoid bad plans (and the unpredictable performance related to plan changes) by providing the right index. You have two selective filters: WHERE and LIMIT so the right index have both
- franckpachot 3y ago
  Maybe it is not the planner. On difference between those other databases and PostgreSQL is that their plan do not depend on how freshly the table was vacuumed. The cost of your "correct index" may becomes worse when the rows are updated until they are vacuumed.

riku_iki 3y ago

> For organization 10, the plane is goood, for organization 11, the plane is bad... that's probably because for 11, there will be more results (say 10k), and optimizer decided that it is faster to scan already sorted index with filter, than sort 10k rows. Logic of optimizer is usually reasonable, my problem with it is that it relies on cost config parameters, which I think by default are inadequate for modern servers, it is not clear how to set it correctly, and actual optimizer rules are not documented, but one need to look at PgSQL code to understand them.

zac23or 3y ago
> Logic of optimizer is usually reasonable I agree. My problem is that SQLite, an embedded database, seems to have a better Planner/Optimizer than Postgres. Is the development of Planner/Optimizer abandoned? > my problem with it is that it relies on cost config parameters And I don't have access to change it, as it's a PASS database...
- pgaddict 3y ago
  Abandoned? Certainly not. But it's a complex part of a mature database product, with many existing deployments/users, which means the improvement is affecting literally everyone. And query planning in general is a hard problem. So it takes time to get new stuff in.
  - zac23or 3y ago
    I agree with everything you said, but it's frustrating that the planner fails on a seemingly trivial query ("seemingly trivial", perhaps it's a very complex problem). > So it takes time to get new stuff in. It's true, I discovered this problem in PG 9, and it's true in PG 16, if I'm not mistaken.
    - pgaddict 3y ago
      I think this very much depends on what exactly is the problem - if it's with how costing for LIMIT works (https://news.ycombinator.com/item?id=39728826 https://news.ycombinator.com/item?id=39728826), then yeah, this did not change since 9.x in a substantial way, so 16 has the same behavior. On a technical level, this is hard because the optimizer has a fairly limited amount of information (optimizer stats) - in particular, it does not know if the columns are correlated. If they are not, then this costing is perfect, and the plan is the correct one. And this assumption of independence is what most databases do by default, so we do that. But if the columns are correlated, it blows up, unfortunately. That is, it's not a very robust plan :-( But I think the really hard problem is the impact the change might have on existing systems. As I said, the problem is we have very little information to inform the decision. We could penalize this "risky" plan somehow (we don't really have any concept of "risk" separately from the cost), but inevitably that will make the query much worse for some of the systems where that plan is correct. I understand the frustration, though. (FWIW I love sqlite, it's an amazing piece of software, but claiming that it has a better optimizer based on a single query may be a bit ... premature.)

matthewbauer 3y ago

That sounds like the pg stats are messed up somehow. Try running explain analyze foo to see if it changes things. Postgres doesn’t automatically create indexes - I bet the others have some implicit index on organization_id, created_at. Otherwise they would be giving bad results in other cases. I don’t see why you can’t just make that index in postgres.

zac23or 3y ago
>Pg stats are messed up somehow. Try running explain analyze foo I did > Postgres doesn’t automatically create indexes Both fields have indexes (created manually). Postgres is incorrectly choosing the index in order by column, not the index in the where clause.

AaronFriel 3y ago

Postgres' query planner is likely considering random vs sequential page costs, and preferring an index scan on created_at. What are the current values of `random_page_cost` and `seq_page_cost`? SHOW seq_page_cost; SHOW random_page_cost; The default is typically 4, and in practice with modern disks you should use a lower value closer to 1.

zac23or 3y ago
seq_page_cost is 1; random_page_cost is 2; I have many other problems with the planner, this is the most absurd due to the simplicity of the query.
- zacmps 3y ago
  On an SSD I'd drop random to 1-1.2.
- franckpachot 3y ago
  If changing random_page_cost from 4 to 2 makes a difference, then probably there are no good indexes. The choice between Seq Scan and Index Scan should be obvious without depending on small adjustments or one day, with slightly different data distribution the plan will flip to a bad one
zac23or 3y ago
I changed random_page_cost to 1! The query continue to use the wrong index.

pgaddict 3y ago

It's really hard to say why this is happening without EXPLAIN ANALYZE (and even then it may not be obvious), but it very much seems like the problem with correlated columns we often have with LIMIT queries. The optimizer simply assumes the matching rows are "uniformly" distributed, because limit is costed as linear approximation of the startup/total cost of the input node. For example, consider this made up query plan Limit (cost=0.0..100.0 rows=10) -> Index scan (cost=0.0...10000000.0 rows=1000000) Filter: dept_id=10 The table may have many more rows - say, 100x more. But the optimizer assumes the rows with (dept_id=10) are distributed in the index, so it can scan the first 1/100000 if the index to get the 10 rows the limits needs. So it assumes the cost is 10*(0+10000000)/1000000. But chances are the dept_id=10 rows happen to be at the very end of the index, so the planner actually needs to scan almost the whole index, making the cost wildly incorrect. You can verify this by looking how far the dept_id rows are in "order by created_id" results. If there are many other rows before the 10 rows you need, it's likely this. Sadly, the optimizer is not smart enough to realize there's this risk. I agree it's an annoying robustness issue, but I don't have a good idea how to mitigate it ... I wonder what the other databases do.

zac23or 3y ago
> It's really hard to say why this is happening without EXPLAIN ANALYZE I can run EXPLAIN and paste it here, but it's just one of the problems with the planner (in a very basic query), I have many other problems with it. On the other hand, I've never had any other serious problems with Postgres. It's a very good database.
- justinclift 3y ago
  > I can run EXPLAIN and paste it here ... Might as well, could turn up something useful. :)
  - pgaddict 3y ago
    I'd suggest reporting it to pgsql-performance mailing list [1], and continuing the discussion there. I'd expect more community members to join that discussion there. [1] https://www.postgresql.org/list/pgsql-performance/ https://www.postgresql.org/list/pgsql-performance/

dventimi 3y ago

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?