Thread: Re: postgres 16 index double variable seems to fail. postgres 15 OK

I am not completely sure, but there seems to be a regression in postgres 16.

Let there be a table with millions of records

  CREATE TABLE table1(

    inode int::8 not null,

    fieldNum int not null,

    wordFromText text not null,

    wordIndex int

  );

with multi-field index

CREATE INDEX table1_indx_textsearch2_Words ON table (inode,fieldNum,wordIndex);

The problem now seems to be that the queries on a single variable inode such as

DELETE FROM table1 WHERE inode=133218

and others, especially when used in some JOIN, it seems does not use the index and run slow.

There were no such problem in postgres 15.

When I create one more index, now a single-field one.

CREATE INDEX table1_indx2 ON table (inode);

postgers 16 becomes fast again.

Postgres 15 was happy with thee fields index (inode,fieldNum,wordIndex), and did not need a single variable index.