Re: postgres chooses objectively wrong index

On Tue, Mar 24, 2026 at 3:21 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:

>
>
> > On 23 Mar 2026, at 22:58, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> > On Thu, Mar 19, 2026 at 1:09 AM Andrei Lepikhov <lepihov(at)gmail(dot)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