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

Поиск
Список
Период
Сортировка
От Zhiyu ZY13 Xu
Тема PG 10 experience different user execute same sql get different access plan
Дата
Msg-id HK2PR03MB4610CF12CCE777C1C6D87091A82B9@HK2PR03MB4610.apcprd03.prod.outlook.com
обсуждение исходный текст
Ответы Re: PG 10 experience different user execute same sql get different access plan  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: MEERA
Дата:
Сообщение: Plan for exclusive backup method
Следующее
От: Derek van den Nieuwenhuijzen
Дата:
Сообщение: PgAdmin4 - 'NoneType' object has no attribute 'value'