From: | "Ryo Yamaji (Fujitsu)" <yamaji(dot)ryo(at)fujitsu(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Access plan selection logic PG9.2 -> PG14 |
Date: | 2023-09-14 07:15:38 |
Message-ID: | TYAPR01MB607360C7295A12C3978632C78AF7A@TYAPR01MB6073.jpnprd01.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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 | Content-Type | Size |
---|---|---|
test.zip | application/x-zip-compressed | 772 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | SMITH Matt | 2023-09-14 10:50:35 | Help with PostgreSQL Upgrade |
Previous Message | Dirschel, Steve | 2023-09-13 17:58:00 | RE: [EXT] Re: Query performance going from Oracle to Postgres |