Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux