PG 10 experience different user execute same sql get different access plan

Zhiyu ZY13 Xu <xuzy13@xxxxxxxxxx> · Wed, 19 May 2021 09:40:52 +0000

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@xxxxxxxxxx

No.6 Shangdi West Road, Haidian District Beijing, China, 100085