From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <pg@bowt.ie>
cc: Sajith Prabhakar Shetty <ssajith@blackduck.com>,
        Andrei Lepikhov <lepihov@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>,
        Todd Cook <cookt@blackduck.com>
Subject: Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
In-reply-to: 
 <CAH2-Wz=O3C=9EWu1azGuHT-3iz1jOon21vG53xFfwJhiaG2CFg@mail.gmail.com>
References: 
 <PH7PR19MB6481278D3A55C8BD04B9B9EDB590A@PH7PR19MB6481.namprd19.prod.outlook.com>
 <2a8243eb-a8e8-4c2d-9c46-7152782d84f8@gmail.com>
 <DM4PR19MB64869049D2AD4882D114BB90B551A@DM4PR19MB6486.namprd19.prod.outlook.com>
 <CAH2-WzmBXPLn=j+nqwGtzoQiMzjY=otTt1irzb39+NkfnHMsnw@mail.gmail.com>
 <DM4PR19MB6486B947E5F0D800127897F5B551A@DM4PR19MB6486.namprd19.prod.outlook.com>
 <CAH2-Wzkduf6pevzTfGpXr29SoXb9jH6AxAkbJM-cJo1EU0M57Q@mail.gmail.com>
 <DM4PR19MB648605FF2806007FC55C0AA5B55AA@DM4PR19MB6486.namprd19.prod.outlook.com>
 <CAH2-WznCkB0V86y+OO0_pp2NfS14-gM2FbMr9uXx9yjdQkpbxQ@mail.gmail.com>
 <DM4PR19MB6486E73FF04BADFD120A0568B525A@DM4PR19MB6486.namprd19.prod.outlook.com>
 <CAH2-WzmNhY2HWNkPku0ugesSp=AxWyy+9tCaq4YqNMSRV8QDqw@mail.gmail.com>
 <2121299.1753993453@sss.pgh.pa.us>
 <CAH2-Wz=O3C=9EWu1azGuHT-3iz1jOon21vG53xFfwJhiaG2CFg@mail.gmail.com>
Comments: In-reply-to Peter Geoghegan <pg@bowt.ie>
	message dated "Thu, 31 Jul 2025 17:04:13 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2128450.1753997128.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Thu, 31 Jul 2025 17:25:28 -0400
Message-ID: <2128451.1753997128@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2128451.1753997128%40sss.pgh.pa.us>
Precedence: bulk

Peter Geoghegan <pg@bowt.ie> writes:
> On Thu, Jul 31, 2025 at 4:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I do think we should do something about this, though.  My suggestion
>> is that we should always presort in the planner if the SAOP argument
>> is a Const array, and then skip the run-time sort if the executor
>> sees the argument is a Const.

> I agree.

Cool, will you do the legwork?

> Is there a convenient choke point for this in the planner?

I'd be inclined to do it as late as possible, in create_plan
(so that we don't expend the effort if we don't choose that
index path).  So in or near fix_indexqual_references is
probably a good spot.

> I suspect that making this change will have a side-effect: it'll make
> EXPLAIN show the array as sorted and deduplicated. That seems like a
> minor positive to me, but it's something to consider.

Indeed.  We can make use of that in test cases, perhaps.

>> An alternative thought is that maybe the run-time sort is expensive
>> enough that the planner ought to account for it in its estimates.

> I tend to doubt that this will ever make much sense.

As you say, getting the cost estimates accurate enough is daunting,
which is why I called it a research project.

			regards, tom lane