From: Euler Taveira <euler@eulerto.com>
To: Vladislav Malyshkin <mal@gromco.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: postgres 16 index double variable seems to fail. postgres 15 OK
Date: Tue, 10 Dec 2024 11:30:36 -0300
Message-ID: <3fd61e5f-e086-4b50-a59e-81bd09384295@app.fastmail.com> (raw)
In-Reply-To: <ce5c81ea-9620-4cc0-adba-e8aef7ea44e9@gromco.com>
References: <ce5c81ea-9620-4cc0-adba-e8aef7ea44e9@gromco.com>
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/
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: euler@eulerto.com, mal@gromco.com, pgsql-bugs@lists.postgresql.org
Subject: Re: postgres 16 index double variable seems to fail. postgres 15 OK
In-Reply-To: <3fd61e5f-e086-4b50-a59e-81bd09384295@app.fastmail.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