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 wi
回复: [External] Re: PG 10 experience different user execute same sql get different access plan
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_ebrThis table is partition table. And have trigger define on the table. dcg.brick_shipmentThis 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:xuz...@lenovo.com No.6 Shangdi West Road, Haidian District Beijing, China, 100085 -邮件原件- 发件人: Tom Lane 发送时间: 2021年5月19日 23:00 收件人: Zhiyu ZY13 Xu 抄送: pgsql-general@lists.postgresql.org 主题: [External] Re: PG 10 experience different user execute same sql get different access plan Zhiyu ZY13 Xu 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 ccfdb=# \dS+ dcg.brick_base_ebr Table "dcg.brick_base_ebr" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --++---+--+-+--+--+- bill_num | character(10) | | not null | | extended | | bill_item| numeric(6,0) | | not null | | main | | comp_code| character(4) | | not null | | extended | | ze2e_flg | character(1) | | not null | | extended | | fiscvarnt| character(2) | | not null | | extended | | zsourceid| character(10) | | not null | | extended | | fiscyear | numeric(4,0) | | not null | | main | | sales_off| character(4) | | | | extended | | profit_ctr | character(10) | | | | extended | | doc_type | character(4) | | | | extended | | doc_number | character(10) | | not null | | extended | | so number s_ord_item | numeric(6,0) | | | | main | | billtoprty | character(10) | | | | extended | | ship_to | character(10) | | | | extended | | sold_to | character(10) | | | | extended | | zsalescat| character(10) | | | | extended | | plant| character(4) | | | | extended | | doc_num | character(10) | | | | extended | | doc_item | numeric(6,0) | | | | main | | material | character(18) | | | | extended | | prod_hier| character(18) | | | | extended | | zprdhier1| character(21) | | | | extended | | zprdhier2| character(23) | | | | extended | | zprdhier3| character(23) | | | | extended | | zprdhier4| character(24) | | | | extended | | zprdhier5| character(24) | | | | extended | | currency | character(5) | | | | extended | | zprdhier6| character(23) | | | | extended | | bill_date| character(10) | | | | extended | | zfiscper | numeric(6,0) | | | | main | | fisc
