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 as equals in the where clause.
However, in PostgreSQL14.7, a SELECT with the same condition would select an index that does not contain any columns specified by 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' and e='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' and e='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
<<attachment: test.zip>>
