From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marko Tiikkaja <marko@joh.to>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
Date: Fri, 03 Oct 2025 10:31:12 -0400
Message-ID: <1314567.1759501872@sss.pgh.pa.us> (raw)
In-Reply-To: <CAL9smLB1szUHLMfpN19FKiCHRCs4WvfjqXbxKaCUjmDzEtT=ng@mail.gmail.com>
References: <20140926080211.7639.68358@wrigleys.postgresql.org>
<8043.1411753479@sss.pgh.pa.us>
<CAL9smLB1szUHLMfpN19FKiCHRCs4WvfjqXbxKaCUjmDzEtT=ng@mail.gmail.com>
Marko Tiikkaja <marko@joh.to> writes:
> ... we have a frequent query like this:
> SELECT ..
> FROM orders
> WHERE
> order_id = $1 AND
> state = 'WAIT_EVENT';
> which almost always uses the primary key. But sometimes, perhaps
> after an autovacuum or something, something changes and postgres
> decides to start serving that query through the orders_wait_event_idx
> index.
You haven't given us a lot to go on: no reproducible test case,
no clear description of what triggers the issue, not even the complete
EXPLAIN output for the problem query. But it's hard to believe that
the planner would estimate a probe on a unique index as costing more
than 94812.85 units, which is what this fragment seems to suggest.
If you look at eqsel() you will observe that the presence of a
unique index overrides any information from statistics, so there
is no "casino" behavior here IMO: it should realize that
"order_id = $1" selects a single row no matter what.
I'm wondering if it didn't use that index because it *couldn't* use
that index, for who-knows-what reason. There are operations that
will leave an index marked as invalid and hence unsafe to query on.
Anyway, -EINSUFFICIENTINFORMATION.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, marko@joh.to
Subject: Re: [BUGS] BUG #11500: PRIMARY KEY index not being used
In-Reply-To: <1314567.1759501872@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox