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 <pgsql-bugs-owner+archive@lists.postgresql.org>)
	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 <pgsql-bugs-owner+archive@lists.postgresql.org>)
	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 <mal@gromco.com>)
	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 <mal@gromco.com>)
	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 <pgsql-bugs@lists.postgresql.org>; Tue, 10 Dec 2024 08:18:55 -0500 (EST)
Message-ID: <ce5c81ea-9620-4cc0-adba-e8aef7ea44e9@gromco.com>
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 <mal@gromco.com>
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: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/ce5c81ea-9620-4cc0-adba-e8aef7ea44e9%40gromco.com>
Precedence: bulk

<!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>