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.
Vladislav