Hacker News Reader

Comment by zac23or | Hacker News Reader

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)

View on HN

This sounds like what I described yesterday: 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.

> 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.

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

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.

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

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.

Without adding that index, what's the correct index to use?

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.

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.

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.

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”.

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;

I 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?

> 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

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

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

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.

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.

Now add a partial index and re-run your queries.

  create index on foo (created_at) where organization_id = 10;

Yeah. But the whole discussion here was about the dilemma the optimizer faces if it only has the two indexes on (created_at) and (organization_id), and why the assumption of independence/uniformity does not work for the skewed case.

Of course, adding a composite or partial index may help, but people are confused why the optimizer is not smart enough to just switch to the other index, which on my machine does this:

                                                             QUERY PLAN                                                         
    ----------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=182766.62..182766.65 rows=10 width=16) (actual time=441.647..441.650 rows=10 loops=1)
       ->  Sort  (cost=182766.62..182988.83 rows=88881 width=16) (actual time=441.642..441.644 rows=10 loops=1)
             Sort Key: created_at
             Sort Method: top-N heapsort  Memory: 25kB
             ->  Seq Scan on foo  (cost=0.00..180845.94 rows=88881 width=16) (actual time=210.901..437.292 rows=100000 loops=1)
                   Filter: (organization_id = 10)
                   Rows Removed by Filter: 10000000
     Planning Time: 0.327 ms
     Execution Time: 441.696 ms
    (9 rows)

That's ~2 orders of magnitude faster, but the optimizer has no way to predict this.

Sure, the composite/partial indexes will do much better, but my intent was to explain why LIMIT queries have this problem.

> Yeah. But the whole discussion here was about the dilemma the optimizer faces if it only has the two indexes on (created_at) and (organization_id), and why the assumption of independence/uniformity does not work for the skewed case.

Was it? The whole discussion started when zac23or said at the top of the thread that their biggest problem with PostgreSQL is its optimizer, that this is their worst example, that it needs an index on (organization_id, created_id) for it to be solved, and that SQLite and MS SQL Server do not need that index on (organization_id, created_at). They didn't initially say how their data are distributed other than that there are millions of rows with organization_id=10 and they didn't initially say that their data are skewed. Later, they said that the data are "distributed equally" and added that organization_id is random, which would be consistent, though they did say that their data are clustered not by created_at but by organization_id. Consequently, the skewed distribution seems to be a special case that you added in this sub-thread. That's fine, but that's a different albeit related problem from the one zac23or posed. If the original problem is, "Arrange for PostgreSQL to perform well on this query on this data model for equally-distributed data without adding an index on (organization_id, created_id)." that problem is solved. It can be done. If your additional problem is "Arrange for PostgreSQL to perform well on this query on this data model for unequally-distributed data without adding an index on (organization_id, created_at)." that problem is also solved with a partial index on just (created_at).

> Of course, adding a composite or partial index may help, but people are confused why the optimizer is not smart enough to just switch to the other index, which on my machine does this:

I'm curious how you forced the optimizer to switch to that query plan if it's not smart enough to do it on its own. I don't know, but I suspect you ordered by an expression, with something like

  explain analyze select * from foo where organization_id = 10 order by created_at + interval '0 day' limit 10;

That produces nearly identical results on my machine, which increases my suspicion.

                                                           QUERY PLAN                                                          
  -----------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=183393.70..183393.73 rows=10 width=24) (actual time=310.827..310.828 rows=10 loops=1)
     ->  Sort  (cost=183393.70..183657.98 rows=105713 width=24) (actual time=307.195..307.196 rows=10 loops=1)
           Sort Key: ((created_at + '00:00:00'::interval))
           Sort Method: top-N heapsort  Memory: 26kB
           ->  Seq Scan on foo  (cost=0.00..181109.28 rows=105713 width=24) (actual time=294.054..300.537 rows=100000 loops=1)
                 Filter: (organization_id = 10)
                 Rows Removed by Filter: 10000000
   Planning Time: 0.116 ms
   JIT:
     Functions: 5
     Options: Inlining false, Optimization false, Expressions true, Deforming true
     Timing: Generation 0.594 ms, Inlining 0.000 ms, Optimization 0.347 ms, Emission 3.296 ms, Total 4.237 ms
   Execution Time: 311.490 ms

This plan is better for organization_id=10 than the one the optimizer chose, without resorting to a partial index, but it comes at a cost: this plan is worse for any of the other values of organization_id, whose data are not skewed.

I think a third question, which is implicit in your comments is, "Well then why doesn't the optimizer use one plan for organization_id=10 and the other plan for the other values of organization_id?" That's a good question. Is this possible? Does any other database do this? I don't know. I'll try to find out but if somebody already has the answer, I'd love to hear it.

A fourth question is, "How do other databases perform on this and similar queries?" In my experiments, the picture is still cloudy. With my original data, SQLite chose the same plan and performed just as badly in the skewed case, until I also gave it a partial index. With your data, SQLite actually outperforms PostgreSQL in the skewed case even without the partial index. Why is that? I don't know. These are just experiments for different cases that are highly context-dependent. So far, I don't see enough evidence to support the original claim, that PostgreSQL's optimizer has a problem and that this is an example.

>> Yeah. But the whole discussion here was about the dilemma the optimizer faces if it only has the two indexes on (created_at) and (organization_id), and why the assumption of independence/uniformity does not work for the skewed case. > > Was it? The whole discussion started when zac23or said at the top of the thread that their biggest problem with PostgreSQL is its optimizer, that this is their worst example, that it needs an index on (organization_id, created_id) for it to be solved, and that SQLite and MS SQL Server do not need that index on (organization_id, created_at). ...

I don't know, but that's how I understood the discussion - I tried to explain why the Postgres optimizer makes this choice, and why planning this type of queries (LIMIT + WHERE) is a hard problem in principle.

>> Of course, adding a composite or partial index may help, but people are confused why the optimizer is not smart enough to just switch to the other index, which on my machine does this: > > I'm curious how you forced the optimizer to switch to that query plan if it's not smart enough to do it on its own.

I simply dropped the index on created_at, forcing the planner to use the other index.

> I think a third question, which is implicit in your comments is, "Well then why doesn't the optimizer use one plan for organization_id=10 and the other plan for the other values of organization_id?" That's a good question. Is this possible? Does any other database do this? I don't know. I'll try to find out but if somebody already has the answer, I'd love to hear it.

The answer is that this does not happen because the optimizer does not have any concept of dependence between the two columns, or (perhaps more importantly) between values in these two columns. So it can't say that for some departments it's not correlated but for ID 10 it is (and should use a different plan).

Maybe it would be possible to implement some new multi-column statistics. I don't think any of the existing optional extended statistics could provide this, but maybe some form of multi-dimensional histogram could? Or something like that? But ultimately, this can never be a complete fix - there'll always be some type of cross-column dependency the statistics can't detect.

> A fourth question is, "How do other databases perform on this and similar queries?"

I'm not sure what all the other databases do, but I think handling this better will require some measure of "risk" or "plan robustness". In other words, some ability to say - how sensitive is the plan to estimates not being perfectly accurate, or maybe some other assumptions being violated. But that's gonna be "better worst case, worse average" solution.

> I don't know, but that's how I understood the discussion - I tried to explain why the Postgres optimizer makes this choice, and why planning this type of queries (LIMIT + WHERE) is a hard problem in principle.

That may be a hard problem. If it is, then a harder problem still is limit + order by. That's why I almost always tell people that limit is not what you want and probably never should be used outside of ad-hoc data exploration.

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.

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.

On an SSD I'd drop random to 1-1.2.

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

I changed random_page_cost to 1! The query continue to use the wrong index.

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.

> 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.

> I can run EXPLAIN and paste it here ...

Might as well, could turn up something useful. :)

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/

See https://news.ycombinator.com/item?id=39730206

One thing that I've run across is when doing a select on a large table with serial or indexed timestamps, ordered by that column is that the plans are horrible when the criteria you're using are unexpectedly rare, even if there's an index on that column. e.g. If col foo has low cardinality, with a bunch of common but a few rare entries, the planner will prefer using the ordering index to the column index even for the very low frequency ones.

Partial indexes can be really useful there, as well as order by (id+0). But it's a total hack.

> 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.

> 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...

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.

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.

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), 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.)

So, why PG devs are resistant against introducing hints, and allowing users optimize base on their knowledge about data nature and have predictable results?..

I don't think there's a uniform agreement to not have hints, and even for devs opposing the idea of hints is there is not a single universal reason and it's more like every single dev has his own reason(s) to not like them. For example:

1) The dev may be working on something else entirely, not caring about hints at all. I'd bet most devs are actually in this group.

2) PG does the right decisions for the use cases the dev needs/supports, so there's not much motivation to make this complex, or feeling hints are needed. Everyone scratches their own itch in the first place.

3) There's a feeling we shouldn't be fixing planning issues by hints but by fixing the optimizer. I agree it's rather idealistic and perhaps not very practical (how does it help that in 2 years there might be a fix, when you have the problem on PROD now?) or supported by history (there's a bunch of cases that we know are a problem for years, not much progress was made).

4) Often bad past experience with applications overusing hints to force plans that the "smart" developer thought are great, but then it grew to 100x the size over years, and now the plans are insane. But also the hints are part of the application code and that can't be changed and it's obviously he fault of the DBA to "make it work". If I had a $1 for every such application I had to deal with ...

5) There issues are fairly rare (depending on the application/schema/,...) are there are other ways to force the database to use a different plan. You can do CTEs, set different cost parameters, disable some plan nodes, ... Not as explicit as hints, but good enough for no one to spend enough time on hints.

6) Belief this can/should be solved out of core. The fact that pg_hint_plan exists suggests maybe it's not a bad idea.

7) Concerns about complexity - the optimizer is quite complicated piece of code already, hints would make it even more complex (both the code and testing). If the community is not ready to accept this extra complexity and maintain it forever, it won't be added.

8) There are proprietary/commercial forks that actually do support hints - for example EDB Postgres Advanced Server supports this. (disclosure: I work for EDB, although not on EPAS.)

9) I don't think there ever was a formal patch adding this capability. And without a patch it's all just a theoretical discussion.

10) ... probably many more reasons ...

I'm not saying any of this is a good reason to (not) support hints, it's merely my observation of what devs say when asked about hints.

imho, lack of hints makes PG just dangerous to use in prod, since DB can stuck in poorly optimized query any moment, and shutdown whole site/system. It would be priority #1 if I would be product manager of this project, and all your bullet points would be naturally resolved.

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.

>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.

Reply on news.ycombinator.com