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

On Thu, Jul 31, 2025 at 5:59 PM Todd Cook <cookt@blackduck.com> wrote:
> On 7/31/25, 4:24 PM, "Tom Lane" <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
> > 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.023
rows=1loops=47089) 
> >       Index Cond: ((id = sdo.sfi) AND (cid = ANY ('{...}'::bigint[])))
>
> I've been doing some experimenting with the indexes on the zsf table.

> Dropping the original unique constraint led the planner to switch to
> a sequential scan of zsf:

I noticed that too. The relevant zsf SAOP array has a unique match for
most of the rows in zsf, so it isn't particularly suprising that we
get a sequential scan when the details are tweaked/with a different
set of indexes.

> Adding a plain index on (id, cid) switches back to an index scan:
>
>   Indexes:
>       "zsf_pkey" PRIMARY KEY, btree (id)
>       "zsf_cid_idx" btree (cid)
>       "zsf_id_cid_fpi_key" UNIQUE CONSTRAINT, btree (id, cid, fpi)
>       "zsf_id_cid_idx" btree (id, cid)
>
>   Index Scan using zsf_id_cid_idx on zsf sf  (cost=0.29..1010.86 rows=34211 width=24) (actual time=0.033..4.239
rows=34205loops=1) 
>     Index Cond: (cid = ANY ('{...}'))
>
> Every plan that didn't use the original unique index ran in ~60 to ~80 ms.

Notice that this plan shows "rows=34205 loops=1" for zsf_id_cid_idx --
not "rows=1 loops=47089", as in your original test case. It looks like
this isn't revised plan makes its scan of zsf_id_cid_idx appear
someplace that isn't the inner side of a nested loop join. In other
words, it looks like the basic structure/join order of the plan is
significantly different to that of your original Postgres 15 plan (and
that of your original Postgres 17 plan, which had the same basic join
order as the 15 one).

--
Peter Geoghegan