Access plan selection logic PG9.2 -> PG14 - Mailing list pgsql-general

Hi all

I am planning to migrate from PostgreSQL9.2 to PostgreSQL14.7.
Running the same SQL with the same statistics as previous versions resulted in different access plans.
I have no knowledge of the PostgreSQL planner and would like to be informed. Please assist.

Behavior:
I am using an environment in which the statistics have been manually updated using pg_dbms_stats. (*)
In PostgreSQL9.2, the primary key was selected when a SELECT was executed with all columns of the primary key specified
asequals in the where clause. 
However, in PostgreSQL14.7, a SELECT with the same condition would select an index that does not contain any columns
specifiedby the where clause. 

(*) The statistics have been updated as follows.
　* pg_class
　　　relpages=200000, reltuples=9.62e+06, relallvisible=0
  * pg_statistic
      stanullfrac, stadistinct, stakindN, staopN=0
      stanumbersN, stavaluesN=NULL
      stawidth=61
  * dbms_stats.relation_stats_locked
      curpages=200000

The OSS versions for each of the above events are as follows:
PostgreSQL14.7 - pg_dbms_stats 1.3.9
PostgreSQL9.2 - pg_dbms_stats 1.5.0

Question:
I am assuming that the version upgrade has changed the behavior of the planner. Is this correct?
I don't know why they choose a plan that seems more expensive than IndexOnlyScan.

Reproduction Method:
Run the attached SQL script against the database you created.
Enable pg_dbms_stats beforehand.

Ex) psql -d test -f test.sql

The contents of test.sql are:
1. Define a table (13 columns) + primary key + user index
2. Insert Test Data
3. Analyze
4. Update pg_class and pg_statistic, and lock statistics
5. SELECT specifies all columns of primary key in WHERE
6. Delete the data used in the test

Result:

PostgreSQL9.2
explain analyze select a from public.tbl where a='1000000000' and b='1000000000' and c='1000000000' and d='1000000000'
ande='1000000000' and h=1; 
                                                                                QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using tbl_pkey on tbl  (cost=0.00..0.54 rows=1 width=61) (actual time=0.010..0.011 rows=1 loops=1)
   Index Cond: ((a = '1000000000'::text) AND (b = '1000000000'::text) AND (c = '1000000000'::text) AND (d =
'1000000000'::text)AND (e = '1000000000'::text) AND (h = 1)) 
   Heap Fetches: 1
 Total runtime: 0.023 ms

PostgreSQL14.7
explain analyze select a from public.tbl where a='1000000000' and b='1000000000' and c='1000000000' and d='1000000000'
ande='1000000000' and h=1; 
                                                                                 QUERY PLAN
                                                     

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl_ix1 on tbl  (cost=0.18..0.42 rows=1 width=61) (actual time=0.007..0.008 rows=1 loops=1)
   Index Cond: (((a)::text = '1000000000'::text) AND ((b)::text = '1000000000'::text) AND ((c)::text =
'1000000000'::text)AND ((d)::text = '1000000000'::text) AND (h = 1)) 
   Filter: ((e)::text = '1000000000'::text)
 Planning Time: 0.337 ms
 Execution Time: 0.023 ms


Regards, Ryo