Re: Postgres: Queries are too slow after upgrading to PG17 from PG15

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