On Fri, 15 Sept 2023 at 01:36, Ryo Yamaji (Fujitsu)
<yamaji.ryo@fujitsu.com> wrote:
> Question:
> I am assuming that the version upgrade has changed the behavior of the planner. Is this correct?
It's possible. 9.2 was a long time ago. It would be quite a bit of
work to determine if this is the case. You could perhaps test on 9.2
without pg_dbms_stats and see what happens.
It's likely add_path() has changed quite a bit since 9.2. That could
be having an effect.
> I don't know why they choose a plan that seems more expensive than IndexOnlyScan.
This likely is due to the query planner not giving any preference to
the index that allows more quals to go into the index condition. Once
the selectivity estimate gets as low as 1 row then the costs between
each index don't vary very much. It's possible the PK index didn't
come out any cheaper, or that add_path() saw the costs as (fuzzily)
the same.
I do think the planner should take the number of matched index quals
into account. I'm just not exactly sure how best to cost that in.
Does v14 run faster if you force the tbl_pkey to be used? (perhaps you
could do that just by dropping the other index if you're using a test
instance that's not needed by anyone else).
David
