On Fri, 15 Sept 2023 at 01:36, Ryo Yamaji (Fujitsu) <yamaji....@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
