Обсуждение: PG 10 experience different user execute same sql get different access plan
Hi Admin support
I experience a PG optimizer problem. Different user query with same SQL at same time. But get different access plan.
On the test env:
PG 10.3 env.
User: ccfuser. This user is DB owner. It will get result within 3 second.
The totally cost is : 99672
User: a_app. This is normal user . It will get result after take 15 minutes.
The totally cost is : 52784
The table has been analyze and vacuum.
dcg.brick_base_ebr
dcg.brick_shipment
I don’t know which factor impact the access plan. This problem could reproduced.
If I grant superuser to a_app. a_app execute sql will using same access plan with ccfuser.
If I revoke superuser from a_app . It will back to original access plan.
Would you like to give me any advice about this issue ? Thanks for your help.
The SQL is :
SELECT A .* FROM ( select * from dcg.brick_base_ebr t1 where 1=1 and t1.ze2e_flg !='J' and t1.fiscper >='2020007' and '2020007' >=t1.fiscper and t1.fiscyear = '2020'
) A LEFT JOIN ( SELECT sd_vbeln, sd_posnr, wrbtr, netpr, matnr FROM ( SELECT ROW_NUMBER () OVER ( PARTITION BY sd_vbeln, sd_posnr ORDER BY A.belnr DESC, A.buzei DESC ) num,* FROM dcg.brick_shipment AS A ) AS A WHERE num = 1 ) bs ON A.doc_number = bs.sd_vbeln AND A.s_ord_item = bs.sd_posnr LEFT JOIN dcg.brick_billing t2 ON A.bill_num=t2.vbeln AND A.bill_item=t2.posnr
|
The ccfuser access plan:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Gather (cost=99672.75..161786.66 rows=79187 width=1107) Workers Planned: 3 -> Hash Left Join (cost=98672.75..152867.96 rows=25544 width=1107) Hash Cond: ((t1.bill_num = (t2.vbeln)::bpchar) AND (t1.bill_item = t2.posnr)) -> Hash Left Join (cost=60696.63..93204.44 rows=25544 width=1107) Hash Cond: ((t1.doc_number = (a.sd_vbeln)::bpchar) AND (t1.s_ord_item = a.sd_posnr)) -> Append (cost=0.00..23310.24 rows=25545 width=1107) -> Parallel Seq Scan on brick_base_ebr t1 (cost=0.00..0.00 rows=1 width=4742) Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AND (fiscyear = '2020'::numeric)) -> Parallel Seq Scan on brick_base_ebr_2020007 t1_1 (cost=0.00..23310.24 rows=25544 width=1107) Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AND (fiscyear = '2020'::numeric)) -> Hash (cost=60680.82..60680.82 rows=1054 width=15) -> Subquery Scan on a (cost=52776.00..60680.82 rows=1054 width=15) Filter: (a.num = 1) -> WindowAgg (cost=52776.00..58045.88 rows=210795 width=3565) -> Sort (cost=52776.00..53302.99 rows=210795 width=31) Sort Key: a_1.sd_vbeln, a_1.sd_posnr, a_1.belnr DESC, a_1.buzei DESC -> Seq Scan on brick_shipment a_1 (cost=0.00..34135.95 rows=210795 width=31) -> Hash (cost=29742.85..29742.85 rows=414085 width=16) -> Seq Scan on brick_billing t2 (cost=0.00..29742.85 rows=414085 width=16) (20 rows) |
The a_app access plan:
Nested Loop Left Join (cost=52784.48..94612.52 rows=2 width=2926) Join Filter: ((t1.doc_number = (a.sd_vbeln)::bpchar) AND (t1.s_ord_item = a.sd_posnr)) -> Hash Right Join (cost=8.47..33892.18 rows=2 width=2926) Hash Cond: (((t2.vbeln)::bpchar = t1.bill_num) AND (t2.posnr = t1.bill_item)) -> Seq Scan on brick_billing t2 (cost=0.00..29742.85 rows=414085 width=16) -> Hash (cost=8.44..8.44 rows=2 width=2924) -> Append (cost=0.00..8.44 rows=2 width=2924) -> Seq Scan on brick_base_ebr t1 (cost=0.00..0.00 rows=1 width=4742) Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AND (fiscyear = '2020'::numeric)) -> Index Scan using brick_base_ebr_fiscper_2020007 on brick_base_ebr_2020007 t1_1 (cost=0.42..8.44 rows=1 width=1107) Index Cond: ((fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper)) Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscyear = '2020'::numeric)) -> Materialize (cost=52776.00..60686.09 rows=1054 width=15) -> Subquery Scan on a (cost=52776.00..60680.82 rows=1054 width=15) Filter: (a.num = 1) -> WindowAgg (cost=52776.00..58045.88 rows=210795 width=3565) -> Sort (cost=52776.00..53302.99 rows=210795 width=31) Sort Key: a_1.sd_vbeln, a_1.sd_posnr, a_1.belnr DESC, a_1.buzei DESC -> Seq Scan on brick_shipment a_1 (cost=0.00..34135.95 rows=210795 width=31) (19 rows) |
徐志宇（Jack）
Database Engineer
DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085
Zhiyu ZY13 Xu <xuzy13@lenovo.com> writes:
> I experience a PG optimizer problem. Different user query with same SQL at same time. But get different access
plan.
If you've got row-level security turned on for that table, it could
explain results like this. RLS limits the planner's ability to
see statistics, which can easily result in a worse plan.
regards, tom lane
Hi Tom
Thanks for your quick response. I check the table ddl. There is no row-level security turn on.
There are 2 tables.
dcg.brick_base_ebr This table is partition table. And have trigger define on the table.
dcg.brick_shipment This is very simple and normal table.
I attach the table ddl for your reference.
I also attach the output about different user get different access plan for your reference.
徐志宇（Jack）
Database Engineer
DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085
-----邮件原件-----
发件人: Tom Lane <tgl@sss.pgh.pa.us>
发送时间: 2021年5月19日 23:00
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgsql-general@lists.postgresql.org
主题: [External] Re: PG 10 experience different user execute same sql get different access plan
Zhiyu ZY13 Xu <xuzy13@lenovo.com> writes:
> I experience a PG optimizer problem. Different user query with same SQL at same time. But get different access
plan.
If you've got row-level security turned on for that table, it could explain results like this. RLS limits the
planner'sability to see statistics, which can easily result in a worse plan.
regards, tom lane