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:xuz...@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085
