Thread

1. postgres chooses objectively wrong index

   Merlin Moncure <mmoncure@gmail.com> — 2026-03-17T21:01:09Z

   I've been maintaining an airflow style orchestrator in pl/pgsql, and it's
   revealed a performance issue I just can't solve.  There is a table, task,
   which may normally contain billions of rows, but only a tiny portion is
   interesting for specific reasons—a common pattern in task-type systems.
   
   CREATE TABLE async.task
   (
     task_id BIGSERIAL PRIMARY KEY,
     target TEXT REFERENCES async.target ON UPDATE CASCADE ON DELETE CASCADE,
     priority INT DEFAULT 0,
     entered TIMESTAMPTZ DEFAULT clock_timestamp(),
     consumed TIMESTAMPTZ,
     processed TIMESTAMPTZ,
     yielded TIMESTAMPTZ,
     times_up TIMESTAMPTZ,
     concurrency_pool TEXT
   );
   
   CREATE OR REPLACE FUNCTION async.task_execution_state(t async.task)
     RETURNS async.task_execution_state_t AS
   $$
     SELECT
       CASE
         WHEN t.processed IS NOT NULL THEN 'FINISHED'
         WHEN t.consumed IS NULL AND t.yielded IS NULL THEN 'READY'
         WHEN t.yielded IS NOT NULL THEN 'YIELDED'
         WHEN t.consumed IS NOT NULL AND t.yielded IS NULL THEN 'RUNNING'
       END::async.task_execution_state_t;
   $$ LANGUAGE SQL IMMUTABLE;
   
   "processed NOT NULL" defines the 'needle', let's say typically <0.01%.   Of
   those cases, a few patterns need defense from a performance standpoint.
   Naturally, partial indexes are used because we don't want to index the
   entire table.
   
   /* supports fetching eligible tasks */
   CREATE INDEX ON async.task(concurrency_pool, priority, entered)
   WHERE async.task_execution_state(task) = 'READY';
   
   /* look up expired tasks.  Times up qual is to prevent index being used for
    * any other purpose.
    */
   CREATE INDEX ON async.task(times_up)
     WHERE
       async.task_execution_state(task) IN('READY', 'RUNNING', 'YIELDED')
       AND times_up IS NOT NULL;
   
   /* supports cleaning up dead tasks on startup and other needs for
    * processing unfinished tasks.
    */
   CREATE INDEX ON async.task(task_id)
     WHERE async.task_execution_state(task) IN('READY', 'RUNNING', 'YIELDED');
   
   These indexes support queries called in a tight loop, for example:
   
   SELECT *
   FRROM async.task
   WHERE
     async.task_execution_state(task.*) =
   'READY'::async.task_execution_state_t
     AND concurrency_pool = 'xyz'
   ORDER BY priority, entered
   LIMIT 10;
   
   Usually, we get a plan that looks like this:
   
    Limit  (cost=0.38..39.74 rows=10 width=563) (actual time=0.054..0.054
   rows=0 loops=1)
      ->  Index Scan using task_concurrency_pool_priority_entered_idx on task
    (cost=0.38..705.08 rows=179 width=563) (actual time=0.053..0.053 rows=0
   loops=1)
            Index Cond: (concurrency_pool = 'xyz'::text)
    Planning Time: 0.234 ms
    Execution Time: 0.072 ms
   
   Let's note that the partial index predicate exactly matches the where
   clause, and that the index from left to right matches in terms of equality
   and ordering.  No sorting is required, and the results are excellent.  The
   final costing here is IMNSHO very high: 39.74, and I believe that is the
   fundamental issue.
   
   Sometimes, based on a certain data distribution, we get results like this:
   
   Limit  (cost=25.75..25.78 rows=10 width=563) (actual time=8.909..8.911
   rows=0 loops=1)
     ->  Sort  (cost=25.75..26.20 rows=179 width=563) (actual
   time=8.908..8.909 rows=0 loops=1)
           Sort Key: priority, entered
           Sort Method: quicksort  Memory: 25kB
           ->  Bitmap Heap Scan on task  (cost=9.10..21.89 rows=179 width=563)
   (actual time=8.902..8.903 rows=0 loops=1)
                 Recheck Cond: ((async.task_execution_state(task.*) = ANY
   ('{READY,RUNNING,YIELDED}'::async.task_execution_state_t[])) AND
   (concurrency_pool = 'xyz'::text) AND (async.task_execution_state(task.*) =
   'READY'::async.task_execution_state_t))
                 ->  BitmapAnd  (cost=9.10..9.10 rows=3 width=0) (actual
   time=8.883..8.883 rows=0 loops=1)
                       ->  Bitmap Index Scan on task_task_id_idx
    (cost=0.00..4.38 rows=575191 width=0) (actual time=8.828..8.828 rows=16
   loops=1)
                       ->  Bitmap Index Scan on
   task_concurrency_pool_priority_entered_idx  (cost=0.00..4.38 rows=179
   width=0) (actual time=0.053..0.053 rows=0 loops=1)
                             Index Cond: (concurrency_pool = 'xyz'::text)
   Planning Time: 0.262 ms
   Execution Time: 8.946 ms
   
   In this case, we get an explicit sort and other unnecessary work for a 100x
   degradation in runtime.  My basic issue is that I do not believe any data
   distribution that allows plan #2 to beat plan #1, given the more specific
   predicate and index order matching result order.  I suspect this is a very
   long standing issue concerning insufficient weight given to partial
   indexes, predicate matching, and possibly index-supported sorting.  I've
   dealt with some variant of this problem for many years.
   
   Sometimes, there can be even worse plans, running into 10-20 seconds, for a
   ~ 10 order of magnitude miss.  I can manage this at the query level by:
   * turning off various planner directives, heap scan, etc
   * adding faux columns to the table to support forcing index selection in
   particular cases (CREATE INDEX ON foo WHERE this_case IS NULL....SELECT *
   FROM foo WHERE this_case IS NULL...)
   
   I'm wondering if there are other tricks that might apply here, for example,
   multi column index statistics...curious if anyone has thoughts on that.
   
   Any suggestions?
   
   merlin
   

2. Re: postgres chooses objectively wrong index

   Alexey Ermakov <alexius.work@gmail.com> — 2026-03-17T22:16:30Z

   On 2026-03-18 03:01, Merlin Moncure wrote:
   > I've been maintaining an airflow style orchestrator in pl/pgsql, and 
   > it's revealed a performance issue I just can't solve.  There is a 
   > table, task, which may normally contain billions of rows, but only a 
   > tiny portion is interesting for specific reasons—a common pattern in 
   > task-type systems.
   >
   > ...
   >
   > I'm wondering if there are other tricks that might apply here, for 
   > example, multi column index statistics...curious if anyone has 
   > thoughts on that.
   >
   > Any suggestions?
   >
   > merlin
   >
   Hello. I think planner doesn't have information about distribution of 
   *async.task_execution_state(task)* unless it's part of any full index. I 
   would try to give that with extended statistics (postgresql 14+):
   
   create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
   analyze async.task;
   
   If that won't help - please show distribution from pg_stats_ext view for 
   extended statistic above.
   
   
   --
   Alexey Ermakov
   

3. Re: postgres chooses objectively wrong index

   Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-17T22:24:28Z

   Merlin Moncure <mmoncure@gmail.com> writes:
   > I've been maintaining an airflow style orchestrator in pl/pgsql, and it's
   > revealed a performance issue I just can't solve.  There is a table, task,
   > which may normally contain billions of rows, but only a tiny portion is
   > interesting for specific reasons—a common pattern in task-type systems.
   > ...
   
   > Usually, we get a plan that looks like this:
   
   >  Limit  (cost=0.38..39.74 rows=10 width=563) (actual time=0.054..0.054 rows=0 loops=1)
   >    ->  Index Scan using task_concurrency_pool_priority_entered_idx on task  (cost=0.38..705.08 rows=179 width=563) (actual time=0.053..0.053 rows=0 loops=1)
   
   > Sometimes, based on a certain data distribution, we get results like this:
   
   > Limit  (cost=25.75..25.78 rows=10 width=563) (actual time=8.909..8.911 rows=0 loops=1)
   >   ->  Sort  (cost=25.75..26.20 rows=179 width=563) (actual time=8.908..8.909 rows=0 loops=1)
   
   I think the fundamental problem here is that the planner is estimating
   179 matching rows when the true count is 0.  Getting that estimate
   down by, say, an order of magnitude would probably fix your issue.
   However, if the selectivity is already epsilon (are there really
   billions of rows?) it may be hard to get it down to a smaller epsilon.
   What statistics target are you using?
   
   How often do tasks change state?  Could it be reasonable to partition
   the task table on state, rather than rely on an index?
   
   			regards, tom lane
   
   
   
   

4. Re: postgres chooses objectively wrong index

   Merlin Moncure <mmoncure@gmail.com> — 2026-03-17T22:52:42Z

   On Tue, Mar 17, 2026 at 4:16 PM Alexey Ermakov <alexius.work@gmail.com>
   wrote:
   
   > On 2026-03-18 03:01, Merlin Moncure wrote:
   >
   > I've been maintaining an airflow style orchestrator in pl/pgsql, and it's
   > revealed a performance issue I just can't solve.  There is a table, task,
   > which may normally contain billions of rows, but only a tiny portion is
   > interesting for specific reasons—a common pattern in task-type systems.
   >
   > ...
   >
   > I'm wondering if there are other tricks that might apply here, for
   > example, multi column index statistics...curious if anyone has thoughts on
   > that.
   >
   > Any suggestions?
   >
   > merlin
   >
   > Hello. I think planner doesn't have information about distribution of
   > *async.task_execution_state(task)* unless it's part of any full index. I
   > would try to give that with extended statistics (postgresql 14+):
   >
   > create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
   > analyze async.task;
   >
   > If that won't help - please show distribution from pg_stats_ext view for
   > extended statistic above.
   >
   
   This unfortunately fails, probably because the table type includes system
   columns (despite not using them).
   
   orchestrator_service_user@orchestrator=> create statistics  task_stats
   (mcv) on (async.task_execution_state(task)) from async.task;
   ERROR:  statistics creation on system columns is not supported
   
   This would require some refactoring to fix.
   
   On Tue, Mar 17, 2026 at 4:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   > Merlin Moncure <mmoncure@gmail.com> writes:
   > > I've been maintaining an airflow style orchestrator in pl/pgsql, and it's
   > > revealed a performance issue I just can't solve.  There is a table, task,
   > > which may normally contain billions of rows, but only a tiny portion is
   > > interesting for specific reasons—a common pattern in task-type systems.
   > > ...
   >
   > > Usually, we get a plan that looks like this:
   >
   > >  Limit  (cost=0.38..39.74 rows=10 width=563) (actual time=0.054..0.054
   > rows=0 loops=1)
   > >    ->  Index Scan using task_concurrency_pool_priority_entered_idx on
   > task  (cost=0.38..705.08 rows=179 width=563) (actual time=0.053..0.053
   > rows=0 loops=1)
   >
   > > Sometimes, based on a certain data distribution, we get results like
   > this:
   >
   > > Limit  (cost=25.75..25.78 rows=10 width=563) (actual time=8.909..8.911
   > rows=0 loops=1)
   > >   ->  Sort  (cost=25.75..26.20 rows=179 width=563) (actual
   > time=8.908..8.909 rows=0 loops=1)
   >
   > I think the fundamental problem here is that the planner is estimating
   > 179 matching rows when the true count is 0.  Getting that estimate
   > down by, say, an order of magnitude would probably fix your issue.
   > However, if the selectivity is already epsilon (are there really
   > billions of rows?) it may be hard to get it down to a smaller epsilon.
   > What statistics target are you using?
   >
   
   Potentially yes.  Maybe 40m in this particular database.
   
   It's set to default, so it isn't very precise.  Is my earlier point
   correct, though? No distribution of data should prefer that plan (barring
   some low row count seqscan stuff)?  Let's say the row count was 179 rows,
   it would make no difference in the disparity (in fact, it'd probably be
   worse).
   
   Simplified, the query is:
   SELECT * FROM foo WHERE a=? AND b=K ORDER BY c, d LIMIT N;
   CREATE INDEX ON foo(a,b,c) WHERE b=K;
   
   why choose any other index? I was guessing mcv stats problem, but this can
   be proved out without stats IMO.
   
   
   > How often do tasks change state?
   >
   
   This is typical FIFO task processing system, pgmq, etc, with a huge number
   of processed rows. and a small number of "processing" rows that get staged
   and then complete.  Loads are highly transient; unprocessed rows may surge
   up to millions before trending to zero.   This naturally puts a lot of
   stress on statistics.   Tasks often resolve in seconds or minutes,
   depending on depth of queue.
   
   Could it be reasonable to partition the task table on state, rather than
   > rely on an index?
   
   
   I've thought about this; the basic issue is that the flow module extends
   async.task with a BEFORE trigger.  This can be worked around but not
   easily.   This is my drop back and punt option, but I'm curious if there is
   an underlying solve here.
   
   merlin
   

5. Re: postgres chooses objectively wrong index

   Alexey Ermakov <alexius.work@gmail.com> — 2026-03-18T05:27:26Z

   On 2026-03-18 04:52, Merlin Moncure wrote:
   > On Tue, Mar 17, 2026 at 4:16 PM Alexey Ermakov 
   > <alexius.work@gmail.com> wrote:
   >
   >     On 2026-03-18 03:01, Merlin Moncure wrote:
   >>     I've been maintaining an airflow style orchestrator in pl/pgsql,
   >>     and it's revealed a performance issue I just can't solve.  There
   >>     is a table, task, which may normally contain billions of rows,
   >>     but only a tiny portion is interesting for specific reasons—a
   >>     common pattern in task-type systems.
   >>
   >>     ...
   >>
   >>     I'm wondering if there are other tricks that might apply here,
   >>     for example, multi column index statistics...curious if anyone
   >>     has thoughts on that.
   >>
   >>     Any suggestions?
   >>
   >>     merlin
   >>
   >     Hello. I think planner doesn't have information about distribution
   >     of *async.task_execution_state(task)* unless it's part of any full
   >     index. I would try to give that with extended statistics
   >     (postgresql 14+):
   >
   >     create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
   >     analyze async.task;
   >
   >     If that won't help - please show distribution from pg_stats_ext
   >     view for extended statistic above.
   >
   >
   > This unfortunately fails, probably because the table type includes 
   > system columns (despite not using them).
   > orchestrator_service_user@orchestrator=> create statistics  task_stats 
   > (mcv) on (async.task_execution_state(task)) from async.task;
   > ERROR:  statistics creation on system columns is not supported
   >
   > This would require some refactoring to fix.
   
   Interesting... In that case functional index should help (as it also 
   makes statistic for the planner):
   
   create index concurrently on task_task_execution_state_idx async.task using btree ((async.task_execution_state(task)));
   
   analyze async.task;
   
   Perhaps multicolumn index will also help for queries but hard to say 
   without knowing distributions. We could check state distribution info 
   after index creation and analyze with query like this:
   
   select * from pg_stats where tablename = 'task_task_execution_state_idx' \gx
   
   
   --
   
   Alexey Ermakov
   

6. Re: postgres chooses objectively wrong index

   Merlin Moncure <mmoncure@gmail.com> — 2026-03-18T18:38:07Z

   On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <alexius.work@gmail.com>
   wrote:
   
   > On 2026-03-18 04:52, Merlin Moncure wrote:
   >
   > Hello. I think planner doesn't have information about distribution of
   >> *async.task_execution_state(task)* unless it's part of any full index. I
   >> would try to give that with extended statistics (postgresql 14+):
   >>
   >> create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
   >> analyze async.task;
   >>
   >> If that won't help - please show distribution from pg_stats_ext view for
   >> extended statistic above.
   >>
   >
   > This unfortunately fails, probably because the table type includes system
   > columns (despite not using them).
   >
   > orchestrator_service_user@orchestrator=> create statistics  task_stats
   > (mcv) on (async.task_execution_state(task)) from async.task;
   > ERROR:  statistics creation on system columns is not supported
   >
   > This would require some refactoring to fix.
   >
   > Interesting... In that case functional index should help (as it also makes
   > statistic for the planner):
   >
   > create index concurrently on task_task_execution_state_idx async.task using btree ((async.task_execution_state(task)));
   >
   > analyze async.task;
   >
   >
   This can't help performance, as the index...
   CREATE INDEX ON async.task(concurrency_pool, priority, entered)
   WHERE async.task_execution_state(task) = 'READY';
   
   ...is very precisely configured to provide exactly what's needed; I need
   tasks for that exact pool in that exact order if and only if ready.  The
   partial predicate is designed to keep the index nice and small since only a
   small portion of tasks would be eligible at any specific time.
   
   @Tom Lane <tgl@sss.pgh.pa.us> I'm pretty sure you were following me, but my
   abstraction earlier was a bit off;
   
   Simplified, the query is:
   > SELECT * FROM foo WHERE a=? AND b=K ORDER BY c, d LIMIT N;
   > CREATE INDEX ON foo(a,b,c) WHERE b=K;
   
   
   Should have been:
   SELECT * FROM foo WHERE a=? AND d=K ORDER BY b, c LIMIT N;
   CREATE INDEX ON foo(a,b,c) WHERE d=K;
   
   Point being, the index match in on (=, order, order). If a contains any
   less than 100% of the total records, and N is small relative to table size,
   this ought to be the ideal index for just about any case, the exact match
   on partial qual is just gravy.
   
   I think the planner is not giving enough bonus for an exact match versus an
   inexact match on partial index mathcing,  (A=A should be better than A
   IN(A,B,C)), and it's unclear why the planner things bitmap heap + sort is
   outperforming a raw read off the index base on marginal estimated row
   counts.  Lowering random_page_cost definitely biases the plan I like,
   but it skews both estimates.
   
   
   @Alexey Ermakov <alexius.work@gmail.com>
   If you're interested in more context, see:
   
   pgasync <https://github.com/merlinm/pgasync>
   pgflow <https://github.com/merlinm/pgflow>
   graph example <https://imgur.com/a/LZNpTC1>
   
   merlin
   

7. Re: postgres chooses objectively wrong index

   Alexey Ermakov <alexius.work@gmail.com> — 2026-03-18T20:12:13Z

   On 2026-03-19 00:38, Merlin Moncure wrote:
   > On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov 
   > <alexius.work@gmail.com> wrote:
   >
   >     On 2026-03-18 04:52, Merlin Moncure wrote:
   >>
   >>         Hello. I think planner doesn't have information about
   >>         distribution of *async.task_execution_state(task)* unless
   >>         it's part of any full index. I would try to give that with
   >>         extended statistics (postgresql 14+):
   >>
   >>         create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
   >>         analyze async.task;
   >>
   >>         If that won't help - please show distribution from
   >>         pg_stats_ext view for extended statistic above.
   >>
   >>
   >>     This unfortunately fails, probably because the table type
   >>     includes system columns (despite not using them).
   >>     orchestrator_service_user@orchestrator=> create statistics
   >>      task_stats (mcv) on (async.task_execution_state(task)) from
   >>     async.task;
   >>     ERROR:  statistics creation on system columns is not supported
   >>
   >>     This would require some refactoring to fix.
   >
   >     Interesting... In that case functional index should help (as it
   >     also makes statistic for the planner):
   >
   >     create index concurrently on task_task_execution_state_idx async.task using btree ((async.task_execution_state(task)));
   >
   >     analyze async.task;
   >
   >
   > This can't help performance, as the index...
   > CREATE INDEX ON async.task(concurrency_pool, priority, entered)
   > WHERE async.task_execution_state(task) = 'READY';
   >
   > ...is very precisely configured to provide exactly what's needed; I 
   > need tasks for that exact pool in that exact order if and only if 
   > ready.  The partial predicate is designed to keep the index nice and 
   > small since only a small portion of tasks would be eligible at any 
   > specific time.
   
   The index I suggested was not intended to be used by such queries, it's 
   only a way to provide statistics for the planner as `create statistics` 
   on expression is not working here.
   
   It might not be enough and require additional columns (in that case it 
   will be replacement for your index) or perhaps elevated statistics 
   target. It could even make things worse but I'm sure you have ways to 
   test that before putting it on an important database.
   
   It should help if planner underestimate number of rows but even then 
   total estimation won't be perfect when we have 2 conditions on state 
   that obviously statistically dependent. Combining both conditions on 
   application side would work much better if that is possible...
   
   What would also might help there - output of `explain (analyze, 
   buffers)` of a query that really had a bad plan and executes in seconds 
   with sizes of indexes. And same output but with `set enable_sort = off` 
   to see plan that supposed to be better. And just in case number of 
   live/dead tuples in that table from pg_stat_user_tables.
   
   --
   
   Alexey Ermakov
   
   

8. Re: postgres chooses objectively wrong index

   Merlin Moncure <mmoncure@gmail.com> — 2026-03-18T22:42:29Z

   On Wed, Mar 18, 2026 at 2:12 PM Alexey Ermakov <alexius.work@gmail.com>
   wrote:
   
   > On 2026-03-19 00:38, Merlin Moncure wrote:
   >
   > Interesting... In that case functional index should help (as it also makes
   >> statistic for the planner):
   >>
   >> create index concurrently on task_task_execution_state_idx async.task using btree ((async.task_execution_state(task)));
   >>
   >> analyze async.task;
   >>
   >>
   > This can't help performance, as the index...
   > CREATE INDEX ON async.task(concurrency_pool, priority, entered)
   > WHERE async.task_execution_state(task) = 'READY';
   >
   > ...is very precisely configured to provide exactly what's needed; I need
   > tasks for that exact pool in that exact order if and only if ready.  The
   > partial predicate is designed to keep the index nice and small since only a
   > small portion of tasks would be eligible at any specific time.
   >
   > The index I suggested was not intended to be used by such queries, it's
   > only a way to provide statistics for the planner as `create statistics` on
   > expression is not working here.
   >
   > It might not be enough and require additional columns (in that case it
   > will be replacement for your index) or perhaps elevated statistics target.
   > It could even make things worse but I'm sure you have ways to test that
   > before putting it on an important database.
   >
   > It should help if planner underestimate number of rows but even then total
   > estimation won't be perfect when we have 2 conditions on state that
   > obviously statistically dependent. Combining both conditions on application
   > side would work much better if that is possible...
   >
   > What would also might help there - output of `explain (analyze, buffers)`
   > of a query that really had a bad plan and executes in seconds with sizes of
   > indexes. And same output but with `set enable_sort = off` to see plan that
   > supposed to be better. And just in case number of live/dead tuples in that
   > table from pg_stat_user_tables.
   >
   
   Roger.  Turns out, I don't have access to pg_statistic (working on that).
   Here are the plans with buffers:
   
   orchestrator_service_user@orchestrator=> explain (analyze, buffers) select
   > * from async.task where async.task_execution_state(task.*) =
   > 'READY'::async.task_execution_state_t  and concurrency_pool =
   > '065.laqjjj_live' order by priority, entered limit 10;
   >
   >                                                      QUERY PLAN
   >
   > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   >  Limit  (cost=25.88..25.90 rows=10 width=563) (actual time=35.024..35.026
   > rows=0 loops=1)
   >    Buffers: shared hit=12542
   >    ->  Sort  (cost=25.88..26.33 rows=179 width=563) (actual
   > time=35.023..35.024 rows=0 loops=1)
   >          Sort Key: priority, entered
   >          Sort Method: quicksort  Memory: 25kB
   >          Buffers: shared hit=12542
   >          ->  Bitmap Heap Scan on task  (cost=9.23..22.01 rows=179
   > width=563) (actual time=34.989..34.990 rows=0 loops=1)
   >                Recheck Cond: ((async.task_execution_state(task.*) = ANY
   > ('{READY,RUNNING,YIELDED}'::async.task_execution_state_t[])) AND
   > (concurrency_pool = '065.laqjjj_live'::text) AND
   > (async.task_execution_state(task.*
   >                Buffers: shared hit=12536
   >                ->  BitmapAnd  (cost=9.23..9.23 rows=3 width=0) (actual
   > time=34.979..34.980 rows=0 loops=1)
   >                      Buffers: shared hit=12536
   >                      ->  Bitmap Index Scan on task_task_id_idx
   >  (cost=0.00..4.38 rows=575191 width=0) (actual time=34.882..34.883 rows=97
   > loops=1)
   >                            Buffers: shared hit=12502
   >                      ->  Bitmap Index Scan on
   > task_concurrency_pool_priority_entered_idx  (cost=0.00..4.51 rows=179
   > width=0) (actual time=0.092..0.093 rows=0 loops=1)
   >                            Index Cond: (concurrency_pool =
   > '065.laqjjj_live'::text)
   >                            Buffers: shared hit=34
   >  Planning:
   >    Buffers: shared hit=350
   >  Planning Time: 1.571 ms
   >  Execution Time: 35.091 ms
   > (20 rows)
   
   
   
   orchestrator_service_user@orchestrator=> set enable_sort to false;
   > SET
   >
   > orchestrator_service_user@orchestrator=> explain (analyze, buffers)
   > select * from async.task where async.task_execution_state(task.*) =
   > 'READY'::async.task_execution_state_t  and concurrency_pool =
   > '065.laqjjj_live' order by priority, entered limit 10;
   >
   >  QUERY PLAN
   >
   > -------------------------------------------------------------------------------------------------------------------------------------------------------------
   >  Limit  (cost=0.50..39.87 rows=10 width=563) (actual time=0.091..0.092
   > rows=0 loops=1)
   >    Buffers: shared hit=34
   >    ->  Index Scan using task_concurrency_pool_priority_entered_idx on task
   >  (cost=0.50..705.21 rows=179 width=563) (actual time=0.090..0.091 rows=0
   > loops=1)
   >          Index Cond: (concurrency_pool = '065.laqjjj_live'::text)
   >          Buffers: shared hit=34
   >  Planning Time: 0.251 ms
   >  Execution Time: 0.110 ms
   > (7 rows)
   
   
   What I'm driving at here is that while statistics contribute to the issue,
   it's somewhat baffling that postgres estimates the 'good' plan slower than
   the 'bad' plan.  Note that it incorrectly estimated 500k rows in the heap
   scan  to avoid 179 heap fetches, since scanning the index itself is a wash,
   with a non-trivial recheck.  So I see this as a planning problem because,
   even with the estimated stats the plan makes no sense.
   
   What seems to have suppressed the bad plan is:
   REINDEX INDEX async.task_task_id_idx;
   
   yielding this plan:
   orchestrator_service_user@orchestrator=> explain (analyze, buffers) select
   * from async.task where async.task_execution_state(task.*) =
   'READY'::async.task_execution_state_t  and concurrency_pool =
   '065.laqjjj_live' order by priority, entered limit 10;
   
    QUERY PLAN
   -------------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=0.50..39.88 rows=10 width=563) (actual time=0.111..0.112
   rows=0 loops=1)
      Buffers: shared hit=34
      ->  Index Scan using task_concurrency_pool_priority_entered_idx on task
    (cost=0.50..685.64 rows=174 width=563) (actual time=0.110..0.110 rows=0
   loops=1)
            Index Cond: (concurrency_pool = '065.laqjjj_live'::text)
            Buffers: shared hit=34
    Planning:
      Buffers: shared hit=311 read=41
      I/O Timings: shared read=23.052
    Planning Time: 24.972 ms
    Execution Time: 0.152 ms
   
   ...with no planner tweaks.
   
   forcing the bad plan,
   
   orchestrator_service_user@orchestrator=> set enable_indexscan to false;
   SET
   Time: 98.377 ms
   orchestrator_service_user@orchestrator=> explain (analyze, buffers) select
   * from async.task where async.task_execution_state(task.*) =
   'READY'::async.task_execution_state_t  and concurrency_pool =
   '065.laqjjj_live' order by priority, entered limit 10;
   
    QUERY PLAN
   -----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=745.70..745.73 rows=10 width=563) (actual time=0.103..0.103
   rows=0 loops=1)
      Buffers: shared hit=40
      ->  Sort  (cost=745.70..746.14 rows=174 width=563) (actual
   time=0.102..0.102 rows=0 loops=1)
            Sort Key: priority, entered
            Sort Method: quicksort  Memory: 25kB
            Buffers: shared hit=40
            ->  Bitmap Heap Scan on task  (cost=4.55..741.94 rows=174
   width=563) (actual time=0.068..0.069 rows=0 loops=1)
                  Recheck Cond: ((concurrency_pool = '065.laqjjj_live'::text)
   AND (async.task_execution_state(task.*) =
   'READY'::async.task_execution_state_t))
                  Buffers: shared hit=34
                  ->  Bitmap Index Scan on
   task_concurrency_pool_priority_entered_idx  (cost=0.00..4.51 rows=174
   width=0) (actual time=0.064..0.064 rows=0 loops=1)
                        Index Cond: (concurrency_pool =
   '065.laqjjj_live'::text)
                        Buffers: shared hit=34
    Planning:
      Buffers: shared hit=1
    Planning Time: 0.258 ms
    Execution Time: 0.136 ms
   
   Point being, bloat is definitely a factor here. However, the database seems
   to be doing the opposite of what's expected, in the presence of bloat, it's
   veering toward a more bloat sensitive plan.
   
   merlin
   

9. Re: postgres chooses objectively wrong index

   Andrei Lepikhov <lepihov@gmail.com> — 2026-03-19T07:09:33Z

   On 18/3/26 19:38, Merlin Moncure wrote:
   > On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <alexius.work@gmail.com 
   > I think the planner is not giving enough bonus for an exact match versus 
   > an inexact match on partial index mathcing,  (A=A should be better than 
   > A IN(A,B,C)), and it's unclear why the planner things bitmap heap + sort 
   > is outperforming a raw read off the index base on marginal estimated row 
   > counts.  Lowering random_page_cost definitely biases the plan I like, 
   > but it skews both estimates.
   
   One ongoing shortcoming is that cardinality estimation takes place early 
   in the optimisation process and uses all filter conditions. This can be 
   frustrating because a partial index covers just part of the table and 
   could give the optimiser better statistics. If we ignored the index 
   condition, we might get a more accurate estimate.
   
   I haven’t tried to redesign this part myself. If it were simple, someone 
   likely would have fixed it by now. Maybe Tom has some ideas about it.
   
   -- 
   regards, Andrei Lepikhov,
   pgEdge
   
   
   
   

10. Re: postgres chooses objectively wrong index

    Merlin Moncure <mmoncure@gmail.com> — 2026-03-23T21:58:40Z

    On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
    
    > On 18/3/26 19:38, Merlin Moncure wrote:
    > > On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <alexius.work@gmail.com
    > > I think the planner is not giving enough bonus for an exact match versus
    > > an inexact match on partial index mathcing,  (A=A should be better than
    > > A IN(A,B,C)), and it's unclear why the planner things bitmap heap + sort
    > > is outperforming a raw read off the index base on marginal estimated row
    > > counts.  Lowering random_page_cost definitely biases the plan I like,
    > > but it skews both estimates.
    >
    > One ongoing shortcoming is that cardinality estimation takes place early
    > in the optimisation process and uses all filter conditions. This can be
    > frustrating because a partial index covers just part of the table and
    > could give the optimiser better statistics. If we ignored the index
    > condition, we might get a more accurate estimate.
    >
    
    Thanks.  I understand the challenge with estimation around partial
    indexes.  Something deeper seems to be at play here.
    
    Poking around more, I see that the bad plans are related to bloat.   A
    simple REINDEX of one of the indexes made the problem disappear; however,
    what's odd is that the estimates didn't really change although the net plan
    cost certainly did.  It's also worth noting ANALYZE doesn't help, only
    REINDEX does.
    
    I keep coming back to this: the bitmap scan noted above makes no sense. I'm
    trying to figure out what is steering the planner in that direction and
    eliminate it.
    
    This problem reliably reproduces about once a month (taking down
    production). I'll wait for it to recur and look at it with fresh eyes.
    
    merlin
    

11. Re: postgres chooses objectively wrong index

    Andrei Lepikhov <lepihov@gmail.com> — 2026-03-24T09:21:15Z

    
    > On 23 Mar 2026, at 22:58, Merlin Moncure <mmoncure@gmail.com> wrote:
    > On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
    > Poking around more, I see that the bad plans are related to bloat.   A simple REINDEX of one of the indexes made the problem disappear; however, what's odd is that the estimates didn't really change although the net plan cost certainly did.  It's also worth noting ANALYZE doesn't help, only REINDEX does. 
    
    We already have plan-freezing and plan-hinting extensions. If I understand you correctly, it makes sense to invent a statistics-freezing module right now. I think such a module will be quite simple - is it a good crutch for you?
    Also, we have some stuff already to work out your case someday:
    1. Postgres already scans indexes during planning to improve estimations of inequality clauses (get_actual_variable_range). Here may be a way to estimate the bloat effect. Not sure how to do it, but allowing index AM to read the page number of the returned tuple, you might, in principle, detect anomalies in the index.
    2. We are quite close to vacuum statistics and detailed index statistics. This is also a way to estimate issues of stale statistics/bloated indexes and decide on the scan type.
    
    So, keep the community posted and provide more real-life examples to build up a proper solution.
    
    Andrei,
    pgEdge
    
    
    
    
    
    
    

12. Re: postgres chooses objectively wrong index

    Merlin Moncure <mmoncure@gmail.com> — 2026-03-25T16:53:42Z

    On Tue, Mar 24, 2026 at 3:21 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
    
    >
    >
    > > On 23 Mar 2026, at 22:58, Merlin Moncure <mmoncure@gmail.com> wrote:
    > > On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <lepihov@gmail.com>
    > wrote:
    > > Poking around more, I see that the bad plans are related to bloat.   A
    > simple REINDEX of one of the indexes made the problem disappear; however,
    > what's odd is that the estimates didn't really change although the net plan
    > cost certainly did.  It's also worth noting ANALYZE doesn't help, only
    > REINDEX does.
    >
    > We already have plan-freezing and plan-hinting extensions.
    
    
    A couple of thoughts here. Re: plan-hinting, I'm a multi-decade disciple of
    the philosophy: "You are not smarter than the planner," "We need
    performance feedback on plans to improve planning," and "Do not exclude
    yourself from potential new types of plans," as offered many years ago by a
    significant poster on this thread.  So, if I were to use it, it would be
    more for exploration and debugging. As things stand, I get by mostly by
    manipulating queries and the very occasional GUC (read: disable nestloop).
    
    Freezing the plan, however, seems to be essential functionality.  My loose
    observation is that postgres plannner stability has generally declined over
    time, causing many production outages.  Many reasons exist for this,
    including my personal tendency to design around optimal outcomes; this
    thread is a pretty good example of that.
    
    The various rules for preparing and managing plans are generally good but
    require more precise control in specific situations. Plan management ought
    to be in core, perhaps even at the syntax level.  I say this because
    extensions like these are generally written in C and not offered by cloud
    providers, which highly limits their audience.  This is why pgasync
    <https://github.com/merlinm/pgasync/tree/main> was written; it's a souped
    up pgbackground / pgmq written entirely at the SQL level, requiring only
    dblink which is now generally offered.
    
    
    > If I understand you correctly, it makes sense to invent a
    > statistics-freezing module right now. I think such a module will be quite
    > simple - is it a good crutch for you?
    > Also, we have some stuff already to work out your case someday:
    >
    
    Interesting question.  I suppose a simple plan lock should be enough, but
    I'm not sure about that.   We might be slightly off here though, my point
    is that the chosen plan seems indefensible even with the supplied
    statistics?  Specifically, pre-REINDEX, postgres thinks that this plan:
    
            ->  Bitmap Heap Scan on task  (cost=9.10..21.89 rows=179 width=563)
    (actual time=8.902..8.903 rows=0 loops=1)
                  Recheck Cond: ((async.task_execution_state(task.*) = ANY
    ('{READY,RUNNING,YIELDED}'::async.task_execution_state_t[])) AND
    (concurrency_pool = 'xyz'::text) AND (async.task_execution_state(task.*) =
    'READY'::async.task_execution_state_t))
                  ->  BitmapAnd  (cost=9.10..9.10 rows=3 width=0) (actual
    time=8.883..8.883 rows=0 loops=1)
                        ->  Bitmap Index Scan on task_task_id_idx
     (cost=0.00..4.38 rows=575191 width=0) (actual time=8.828..8.828 rows=16
    loops=1)
                        ->  Bitmap Index Scan on
    task_concurrency_pool_priority_entered_idx  (cost=0.00..4.38 rows=179
    width=0) (actual time=0.053..0.053 rows=0 loops=1)
                              Index Cond: (concurrency_pool = 'xyz'::text)
    
    is better than this plan:
    
     Limit  (cost=0.38..39.74 rows=10 width=563) (actual time=0.054..0.054
    rows=0 loops=1)
       ->  Index Scan using task_concurrency_pool_priority_entered_idx on task
     (cost=0.38..705.08 rows=179 width=563) (actual time=0.053..0.053 rows=0
    loops=1)
             Index Cond: (concurrency_pool = 'xyz'::text)
    
    which is something I just don't understand.  After reindexing
    task_task_id_idx, things cleared up, and both plans ran well, which I also
    don't understand.  ISTM a plan lock ought to keep things buttoned up though.
    
    
    > 1. Postgres already scans indexes during planning to improve estimations
    > of inequality clauses (get_actual_variable_range). Here may be a way to
    > estimate the bloat effect. Not sure how to do it, but allowing index AM to
    > read the page number of the returned tuple, you might, in principle, detect
    > anomalies in the index.
    > 2. We are quite close to vacuum statistics and detailed index statistics.
    > This is also a way to estimate issues of stale statistics/bloated indexes
    > and decide on the scan type.
    >
    > So, keep the community posted and provide more real-life examples to build
    > up a proper solution.
    >
    
    very much appreciate your insight.
    
    merlin