Request a Demo
Home   >   mailing lists

Re: postgres chooses objectively wrong index - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: postgres chooses objectively wrong index
Date
Msg-id CAHyXU0yVw+urG729bYbu29PixK1=oddZMPn6hZFu1AbtX6fHFw@mail.gmail.com
Whole thread
In response to Re: postgres chooses objectively wrong index  (Alexey Ermakov <alexius.work@gmail.com>)
List pgsql-performance
Tree view
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
 

pgsql-performance by date:

Previous
From: Alexey Ermakov
Date:
Subject: Re: postgres chooses objectively wrong index
Next
From: Andrei Lepikhov
Date:
Subject: Re: postgres chooses objectively wrong index