public inbox for [email protected]
help / color / mirror / Atom feedpostgres chooses objectively wrong index
12+ messages / 4 participants
[nested] [flat]
* postgres chooses objectively wrong index
@ 2026-03-17 21:01 Merlin Moncure <[email protected]>
2026-03-17 22:16 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Merlin Moncure @ 2026-03-17 21:01 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
@ 2026-03-17 22:16 ` Alexey Ermakov <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Alexey Ermakov @ 2026-03-17 22:16 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
@ 2026-03-17 22:24 ` Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2026-03-17 22:24 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; +Cc: [email protected]
Merlin Moncure <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
@ 2026-03-17 22:52 ` Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Merlin Moncure @ 2026-03-17 22:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Alexey Ermakov <[email protected]>; +Cc: [email protected]
On Tue, Mar 17, 2026 at 4:16 PM Alexey Ermakov <[email protected]>
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 <[email protected]> wrote:
> Merlin Moncure <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
@ 2026-03-18 05:27 ` Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Alexey Ermakov @ 2026-03-18 05:27 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; Tom Lane <[email protected]>; +Cc: [email protected]
On 2026-03-18 04:52, Merlin Moncure wrote:
> On Tue, Mar 17, 2026 at 4:16 PM Alexey Ermakov
> <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
@ 2026-03-18 18:38 ` Merlin Moncure <[email protected]>
2026-03-18 20:12 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-19 07:09 ` Re: postgres chooses objectively wrong index Andrei Lepikhov <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Merlin Moncure @ 2026-03-18 18:38 UTC (permalink / raw)
To: Alexey Ermakov <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <[email protected]>
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 <[email protected]> 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 <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
@ 2026-03-18 20:12 ` Alexey Ermakov <[email protected]>
2026-03-18 22:42 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Alexey Ermakov @ 2026-03-18 20:12 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
On 2026-03-19 00:38, Merlin Moncure wrote:
> On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov
> <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 20:12 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
@ 2026-03-18 22:42 ` Merlin Moncure <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Merlin Moncure @ 2026-03-18 22:42 UTC (permalink / raw)
To: Alexey Ermakov <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
On Wed, Mar 18, 2026 at 2:12 PM Alexey Ermakov <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
@ 2026-03-19 07:09 ` Andrei Lepikhov <[email protected]>
2026-03-23 21:58 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Andrei Lepikhov @ 2026-03-19 07:09 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; Alexey Ermakov <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]
On 18/3/26 19:38, Merlin Moncure wrote:
> On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <[email protected]
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-19 07:09 ` Re: postgres chooses objectively wrong index Andrei Lepikhov <[email protected]>
@ 2026-03-23 21:58 ` Merlin Moncure <[email protected]>
2026-03-24 09:21 ` Re: postgres chooses objectively wrong index Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Merlin Moncure @ 2026-03-23 21:58 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: Alexey Ermakov <[email protected]>; Tom Lane <[email protected]>; [email protected]
On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <[email protected]> wrote:
> On 18/3/26 19:38, Merlin Moncure wrote:
> > On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov <[email protected]
> > 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-19 07:09 ` Re: postgres chooses objectively wrong index Andrei Lepikhov <[email protected]>
2026-03-23 21:58 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
@ 2026-03-24 09:21 ` Andrei Lepikhov <[email protected]>
2026-03-25 16:53 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Andrei Lepikhov @ 2026-03-24 09:21 UTC (permalink / raw)
To: Merlin Moncure <[email protected]>; +Cc: Alexey Ermakov <[email protected]>; Tom Lane <[email protected]>; [email protected]
> On 23 Mar 2026, at 22:58, Merlin Moncure <[email protected]> wrote:
> On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres chooses objectively wrong index
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:24 ` Re: postgres chooses objectively wrong index Tom Lane <[email protected]>
2026-03-17 22:52 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Re: postgres chooses objectively wrong index Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-19 07:09 ` Re: postgres chooses objectively wrong index Andrei Lepikhov <[email protected]>
2026-03-23 21:58 ` Re: postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-24 09:21 ` Re: postgres chooses objectively wrong index Andrei Lepikhov <[email protected]>
@ 2026-03-25 16:53 ` Merlin Moncure <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Merlin Moncure @ 2026-03-25 16:53 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: Alexey Ermakov <[email protected]>; Tom Lane <[email protected]>; [email protected]
On Tue, Mar 24, 2026 at 3:21 AM Andrei Lepikhov <[email protected]> wrote:
>
>
> > On 23 Mar 2026, at 22:58, Merlin Moncure <[email protected]> wrote:
> > On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <[email protected]>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-03-25 16:53 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-17 21:01 postgres chooses objectively wrong index Merlin Moncure <[email protected]>
2026-03-17 22:16 ` Alexey Ermakov <[email protected]>
2026-03-17 22:24 ` Tom Lane <[email protected]>
2026-03-17 22:52 ` Merlin Moncure <[email protected]>
2026-03-18 05:27 ` Alexey Ermakov <[email protected]>
2026-03-18 18:38 ` Merlin Moncure <[email protected]>
2026-03-18 20:12 ` Alexey Ermakov <[email protected]>
2026-03-18 22:42 ` Merlin Moncure <[email protected]>
2026-03-19 07:09 ` Andrei Lepikhov <[email protected]>
2026-03-23 21:58 ` Merlin Moncure <[email protected]>
2026-03-24 09:21 ` Andrei Lepikhov <[email protected]>
2026-03-25 16:53 ` Merlin Moncure <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox