From mal@gromco.com Tue Dec 10 13:18:53 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tL0cc-00C5oC-Sq for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Dec 2024 13:49:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tL0ca-004iU1-5w for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Dec 2024 13:49:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tL08h-004Jhq-76 for pgsql-bugs@lists.postgresql.org; Tue, 10 Dec 2024 13:19:00 +0000 Received: from ns101513.ip-147-135-7.us ([147.135.7.79] helo=smtp.gromco.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tL08f-0024ms-Oc for pgsql-bugs@lists.postgresql.org; Tue, 10 Dec 2024 13:18:59 +0000 Received: from [10.10.0.6] (unknown [10.10.0.6]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: mal) by smtp.gromco.com (Postfix) with ESMTPSA id 8C4F6340751 for ; Tue, 10 Dec 2024 08:18:55 -0500 (EST) Message-ID: Date: Tue, 10 Dec 2024 08:18:53 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US To: pgsql-bugs@lists.postgresql.org From: Vladislav Malyshkin Subject: postgres 16 index double variable seems to fail. postgres 15 OK Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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
);
with multi-field index
CREATE INDEX table1_indx_textsearch2_Words ON table (inode,fieldNum,wordIndex);

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.
There were no such problem in postgres 15.
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.

Vladislav

From euler@eulerto.com Tue Dec 10 14:30:36 2024 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tL1Gt-00CBAu-1K for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Dec 2024 14:31:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tL1Gp-005UDH-7g for pgsql-bugs@arkaria.postgresql.org; Tue, 10 Dec 2024 14:31:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tL1Go-005UD8-GP for pgsql-bugs@lists.postgresql.org; Tue, 10 Dec 2024 14:31:28 +0000 Received: from fout-a7-smtp.messagingengine.com ([103.168.172.150]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tL1Gi-0025X1-3P for pgsql-bugs@lists.postgresql.org; Tue, 10 Dec 2024 14:31:27 +0000 Received: from phl-compute-01.internal (phl-compute-01.phl.internal [10.202.2.41]) by mailfout.phl.internal (Postfix) with ESMTP id 0BEDC13814BF; Tue, 10 Dec 2024 09:31:18 -0500 (EST) Received: from phl-imap-09 ([10.202.2.99]) by phl-compute-01.internal (MEProxy); Tue, 10 Dec 2024 09:31:18 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1733841078; x=1733927478; bh=tk233ItCrN M9H75+GLnNvEFHhM36bSWXrTq1j80/zSs=; b=XbIupDJHedg87YRvqaLmajTasL cEr3rvFqydQOr17VFH2ac2dUFA2eDLk9ZMGu4JeEee4fdLn/RBOrH9D+39gGOMbY pWOOoCgBnveohoZLBTvJVCiiZt/8wXS4ozTOcYFNCXsQdIt8DI1oojf62KBN3uSo XzdlVU3MyxzkMHDwyH+4SriXPL1HtOyC4ZXFz/0HoObLLfD9DwM/43VZRyLVqnvv bpBBCxAVUGGL/vHBI4lW+bV2HxDT0eU4JQ7HieVOd/BlsoZsaKP4gG3aLGgm8bVV Dch2X4tt1FhHN/1rbomkti3s41hVm0k3Ldqz4+ENaRc/P8sqZQHUL3gBtbHA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1733841078; x=1733927478; bh=tk233ItCrNM9H75+GLnNvEFHhM36bSWXrTq 1j80/zSs=; b=PrSqctF1RsSkWrdmX2IWOn1fr7RWqjfPQ3NePsrIGFmWKI5nZ4J NVVAxG6Urk08L8R9M/yAOhL/l5/4zo5RZB9+4QYo5zcyz7evH6sYTJUQwUfAyZsO 4Z3alCouIBufBYLBq1STsJQdIerPPhe6n/GkNSd6+bw7uNS6SKqn4sdI3mwCGmzp 1ZoQjaHEopYNUEnm3ukvXLDij8RyF5kfRnVX2uRglKgHjc0YJMoobmV6UCxmXkV1 TeGJfyqzgLpT2+YPOe7ijSRFSRWhejFZr/JBJZZHRmCjgyyvjUsYojCiVhG4GiDb jYKdHhY5B5LxsqgN6nGFkMiuyiDkzi001mA== X-ME-Sender:

X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefuddrjeekgdeigecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpggftfghnshhusghstghrihgsvgdpuffr tefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecunecujfgurhepofggfffhvf fkjghfufgtsegrtderreertddtnecuhfhrohhmpedfgfhulhgvrhcuvfgrvhgvihhrrgdf uceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecuggftrfgrthhtvghrnhephfdtud elueefleetuddtudekkeefueegvdfgjeetgefgffetieffudfggfeuffetnecuffhomhgr ihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhushhtvghrufhiiigvpedtne curfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhlvghrthhordgtohhmpdhn sggprhgtphhtthhopedvpdhmohguvgepshhmthhpohhuthdprhgtphhtthhopehmrghlse hgrhhomhgtohdrtghomhdprhgtphhtthhopehpghhsqhhlqdgsuhhgsheslhhishhtshdr phhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy:

Feedback-ID: i0c21471d:Fastmail Received: by mailuser.phl.internal (Postfix, from userid 501) id 66539780068; Tue, 10 Dec 2024 09:31:16 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface MIME-Version: 1.0 Date: Tue, 10 Dec 2024 11:30:36 -0300 From: "Euler Taveira" To: "Vladislav Malyshkin" , pgsql-bugs@lists.postgresql.org Message-Id: <3fd61e5f-e086-4b50-a59e-81bd09384295@app.fastmail.com> In-Reply-To: References: Subject: Re: postgres 16 index double variable seems to fail. postgres 15 OK Content-Type: multipart/alternative; boundary=5a419191456d4cb7b385f4790a235236 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --5a419191456d4cb7b385f4790a235236 Content-Type: text/plain Content-Transfer-Encoding: 7bit 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/ --5a419191456d4cb7b385f4790a235236 Content-Type: text/html Content-Transfer-Encoding: quoted-printable

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=0A postgres 16.
Let there = be a table with millions of records

= ; CREATE TABLE table1(
    inode int::8 no= t null,
    fieldNum int not null,
    wordFromText text not null,
&n= bsp;   wordIndex int
);
<= br>

This is not a valid definition. Do you mean "= bigint" instead of "int::8"?

with multi-field index
CREAT= E INDEX table1_indx_textsearch2_Words ON table=0A (inode,fieldNum,wor= dIndex);

The table name is wr= ong. Are you sure you are creating the index in the right

= table?

The problem now seems to be that the queries on a single var= iable=0A inode such as
DELETE FROM table1 WHERE inode= =3D133218
and others, especially when used in some JOIN, = it seems does not use=0A the index and run slow.

You didn't provide the query plan.

<= br>

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

There were no such problem in postgres= 15.

You didn't provide the qu= ery plan.

When I create one more index, now a single-field one.
=
CREATE INDEX table1_indx2 ON table (inode);
postger= s 16 becomes fast again.
Postgres 15 was happy with thee= fields index=0A (inode,fieldNum,wordIndex), and did not need a singl= e variable=0A index.

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

Eu= ler Taveira

EDB https://www.enterprisedb.com/

--5a419191456d4cb7b385f4790a235236--