PG 10 experience different user execute same sql get different access plan - Mailing list pgsql-general
| From | Zhiyu ZY13 Xu |
|---|---|
| Subject | PG 10 experience different user execute same sql get different access plan |
| Date | |
| Msg-id | HK2PR03MB4610CF12CCE777C1C6D87091A82B9@HK2PR03MB4610.apcprd03.prod.outlook.com Whole thread Raw |
| Responses |
Re: PG 10 experience different user execute same sql get different access plan
|
| List | pgsql-general |
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
pgsql-general by date: