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

On Tue, Dec 10, 2024, at 10:18 AM, Vladislav Malyshkin wrote:
> 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
> );
>
This is not a valid definition. Do you mean "bigint" instead of "int::8"?

> with multi-field index
> CREATE INDEX table1_indx_textsearch2_Words ON table (inode,fieldNum,wordIndex);
>
The table name is wrong. Are you sure you are creating the index in the right
table?

> 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.
>
You didn't provide the query plan.

EXPLAIN (VERBOSE, SETTINGS) DELETE FROM table1 WHERE inode = 133218;

>
> There were no such problem in postgres 15.

You didn't provide the query plan.

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

It is hard to say something if you didn't provide a reproducible test case.

--
Euler Taveira
EDB https://www.enterprisedb.com/