postgres 16 index double variable seems to fail. postgres 15 OK
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
I am not completely sure, but there seems to be a regression in
postgres 16.<br>
Let there be a table with millions of records<br>
<br>
CREATE TABLE table1(<br>
inode int::8 not null,<br>
fieldNum int not null,<br>
wordFromText text not null,<br>
wordIndex int<br>
);<br>
with multi-field index<br>
CREATE INDEX table1_indx_textsearch2_Words ON table
(inode,fieldNum,wordIndex);<br>
<br>
The problem now seems to be that the queries on a single variable
inode such as <br>
DELETE FROM table1 WHERE inode=133218<br>
and others, especially when used in some JOIN, it seems does not use
the index and run slow. <br>
There were no such problem in postgres 15.<br>
When I create one more index, now a single-field one.<br>
CREATE INDEX table1_indx2 ON table (inode);<br>
postgers 16 becomes fast again. <br>
Postgres 15 was happy with thee fields index
(inode,fieldNum,wordIndex), and did not need a single variable
index.<br>
<br>
Vladislav<br>
<br>
</body>
</html>
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 recordsCREATE 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/