pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres 16 index double variable seems to fail. postgres 15 OK
2+ messages / 2 participants
[nested] [flat]
* postgres 16 index double variable seems to fail. postgres 15 OK
@ 2024-12-10 13:18 Vladislav Malyshkin <mal@gromco.com>
0 siblings, 1 reply; 2+ messages in thread
From: Vladislav Malyshkin @ 2024-12-10 13:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
<!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>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: postgres 16 index double variable seems to fail. postgres 15 OK
@ 2024-12-10 14:30 Euler Taveira <euler@eulerto.com>
parent: Vladislav Malyshkin <mal@gromco.com>
0 siblings, 0 replies; 2+ messages in thread
From: Euler Taveira @ 2024-12-10 14:30 UTC (permalink / raw)
To: Vladislav Malyshkin <mal@gromco.com>; pgsql-bugs@lists.postgresql.org
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-12-10 14:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-10 13:18 postgres 16 index double variable seems to fail. postgres 15 OK Vladislav Malyshkin <mal@gromco.com>
2024-12-10 14:30 ` Euler Taveira <euler@eulerto.com>
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