From: Vladislav Malyshkin <mal@gromco.com>
To: pgsql-bugs@lists.postgresql.org
Subject: postgres 16 index double variable seems to fail. postgres 15 OK
Date: Tue, 10 Dec 2024 08:18:53 -0500
Message-ID: <ce5c81ea-9620-4cc0-adba-e8aef7ea44e9@gromco.com> (raw)
<!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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: mal@gromco.com, pgsql-bugs@lists.postgresql.org
Subject: Re: postgres 16 index double variable seems to fail. postgres 15 OK
In-Reply-To: <ce5c81ea-9620-4cc0-adba-e8aef7ea44e9@gromco.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox