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

On Thu, Jul 31, 2025 at 4:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, maybe it was a mistake to no longer consider such plans, but
> this example doesn't prove it.

To be clear, I strongly suspect that it wasn't a mistake. I was just
laying out options.

> Quoting the submitted readme file,
> we selected this plan in v15:
>
>       ->  Index Scan using zsf_pkey on zsf sf  (cost=1.49..1.51 rows=1 width=24) (actual time=0.001..0.001 rows=1
loops=47089)
>             Index Cond: (id = sdo.sfi)
>             Filter: (cid = ANY ('{...}'::bigint[]))
>
> versus this in v17:
>
>       ->  Index Only Scan using zsf_id_fpi_cid_key on zsf sf  (cost=0.29..0.31 rows=1 width=24) (actual
time=0.023..0.023rows=1 loops=47089) 
>             Index Cond: ((id = sdo.sfi) AND (cid = ANY ('{...}'::bigint[])))
>
> IIUC you're saying the planner no longer even considers the first
> case

Yes, exactly.

> but if it did, it'd surely still pick the second one,
> because the estimated cost is a lot less.  So undoing that choice
> would not help the blackduck folks.

Right.

Theoretically, we could go back to generating these filter qual index
paths if we also revised the costing to account for every little
nuance. But I have little faith in our ability to come up with a cost
model that could ever be accurate enough to reliably pick between two
very similar alternatives like this.

There are huge disadvantages to using filter quals when index quals
can be used instead. Weighing that against the cost of the sort seems
like it would be very brittle.

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

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

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.

> Yes, there will be cases where the
> plan-time sort is wasted effort, but not too darn many.

It's also only something that is noticeable with very large arrays,
which are themselves kind of rare.

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

--
Peter Geoghegan