Re: [BUGS] BUG #11500: PRIMARY KEY index not being used

On Sat, 4 Oct 2025 at 15:40, Marko Tiikkaja <marko@joh.to> wrote:
>
> On Fri, Oct 3, 2025 at 16:31 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You haven't given us a lot to go on: no reproducible test case,
>
> I've provided two.  Both make the planner look bad.

I expect what might be happening here is that ANALYZE runs when there
are no state = 'WAIT_EVENT' rows and thinks using the index on that
column is a better idea than the primary key index. Both selectivities
will be clamped at 1 row, so the index with fewer pages is likely to
win.  Then the problem happens after lots of state = 'WAIT_EVENT' rows
have been added, but not enough to trigger an auto-analyze. In that
case, the planner would still think there's 1 row, but there could be
any number of rows.

create table t (a bigint primary key, b int not null);
create index on t (b);
insert into t select x, x%10+1 from generate_Series(1,1000000)x;
analyze t;
explain select * from t where a=1 and b=0;
                            QUERY PLAN
------------------------------------------------------------------
 Index Scan using t_b_idx on t  (cost=0.42..8.40 rows=1 width=12)
   Index Cond: (b = 0)
   Filter: (a = 1)
(3 rows)

Just to see the cost comparison between the two, drop the index on "b":

drop index t_b_idx;
explain select * from t where a=1 and b=0;
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using t_pkey on t  (cost=0.42..8.45 rows=1 width=12) <- a
bit more costly.
   Index Cond: (a = 1)
   Filter: (b = 0)
(3 rows)

Basically, the planner isn't very good at taking risk into account
when it comes to this sort of thing.  Our cost model doesn't have a
risk factor. We have added a few things into add_path() over the years
to try to do something better in these scenarios. They often help.
However, there's none for this scenario.  The attached patch adds one.
I'm not proposing we do this, but if you try my example above with it
applied, you'll see the t_pkey index is used. I feel the hack is too
fragile to be a proper fix.

You might be able to work around the issue by adjusting the query.
Ordinarily, you could do something like "WHERE indexed_col + 0 =
<value>" to bypass using an index on that column (assuming the column
is an integer type). I can't off the top of my head think of a way to
do that with an enum column.

Another idea on how you could fix is to adjust the
orders_wait_event_idx index somehow. Maybe make it non-partial and
index the enum column and INCLUDE another column to make the index
wider and more costly to scan. Or maybe move it to another tablespace
with higher page costs.

David