Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: postgres chooses objectively wrong index
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAHyXU0yAibBKPaJ-A59inJa+iv1Q1bOfMp+VPZUyNjvquBNVSQ@mail.gmail.com>
Date: Tue, 24 Mar 2026 10:21:15 +0100
Cc: Alexey Ermakov <alexius.work@gmail.com>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-performance@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <217AB71B-F368-4DA1-A3B5-ABED94E1BD0A@gmail.com>
References: 
 <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
 <574349.1773786268@sss.pgh.pa.us>
 <CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
 <381e7d47-8021-458d-9a64-0cdb80b004f0@gmail.com>
 <CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com>
 <2a32c870-3f69-4b09-854d-ffea03b1e537@gmail.com>
 <CAHyXU0yAibBKPaJ-A59inJa+iv1Q1bOfMp+VPZUyNjvquBNVSQ@mail.gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/217AB71B-F368-4DA1-A3B5-ABED94E1BD0A%40gmail.com>
Precedence: bulk


> On 23 Mar 2026, at 22:58, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Mar 19, 2026 at 1:09=E2=80=AFAM 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.=20

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