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.
As a reminder, the definition of the zsf table is
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+---------
id | bigint | | not null |
cid | bigint | | not null |
fpi | bigint | | not null |
Indexes:
"zsf_pkey" PRIMARY KEY, btree (id)
"zsf_cid_idx" btree (cid)
"zsf_id_fpi_cid_key" UNIQUE CONSTRAINT, btree (id, fpi, cid)
As an experiment, I added another unique constraint that puts the
two columns referenced by the query first, giving
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_fpi_cid_key" UNIQUE CONSTRAINT, btree (id, fpi, cid)
Unexpectedly (to me), the planner still chose the same index as before
(with the unreferenced fpi in the middle).
Dropping the original unique constraint led the planner to switch to
a sequential scan of zsf:
Indexes:
"zsf_pkey" PRIMARY KEY, btree (id)
"zsf_cid_idx" btree (cid)
"zsf_id_cid_fpi_key" UNIQUE CONSTRAINT, btree (id, cid, fpi)
Seq Scan on zsf sf (cost=1.20..751.52 rows=34211 width=24) (actual time=0.014..2.781 rows=34205 loops=1)
Filter: (cid = ANY ('{...}'))
which I assume means that the planner estimated the seq scan to be cheaper
than using the unique index on (id, cid, fpi).
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.
It seems to me (perhaps naively) that the planner is being inconsistent
about how it is costing the three possible index traversals. Why would
using zsf_id_cid_idx cost so much more than using the original index?
-- todd
