설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 85건, 최근 0 건
   

WindowAgg optimization

글쓴이 : 모델광 날짜 : 2021-10-23 (토) 19:44 조회 : 100

When I optimize a query, there are some optimizer operations I hate to see. Some of them are Sort, GroupAgg, WindowAgg operations and then some. In this note, I will offer you a tuning technique with which you can get rid of the WindowAgg operation.


Here is a script to generate the data set I will use for a demonstration.


create table ord as

select i as ord_no

     , 'cust_'||(mod(i,100)+1)::text as cust_id

     , random()::text as contents

     , to_char(current_date - mod(i,5),'yyyymmdd') as ord_date

  from generate_series(1,1000000) a(i);

ALTER TABLE ord ADD CONSTRAINT pk_ord

  PRIMARY KEY (ord_no);


drop table if exitsts ord_item;

create table ord_item as

select mod(i,1000000)+1 as ord_no

      ,'prod_'||mod(i,196)::text as prod_no

      , trunc(random()*1000) as prod_per_price

      , mod(i,10) as ord_quantity

      , 'D'||mod(i,9)::text as discount_code

  from generate_series(1,3000000) a(i);

ALTER TABLE ord_item ADD CONSTRAINT pk_ord_item

  PRIMARY KEY (ord_no, prod_no);

CREATE INDEX ORD_ITEM_X01 ON ORD_ITEM(ORD_NO, ORD_QUANTITY);


Here is an SQL statement that we should optimize.


SELECT A.ORD_NO, B.PROD_NO

  FROM ORD A

  JOIN LATERAL (SELECT ORD_NO, 

                       FIRST_VALUE(PROD_NO) OVER (PARTITION BY ORD_NO ORDER BY ORD_QUANTITY DESC) PROD_NO

                  FROM ORD_ITEM B

                 WHERE DISCOUNT_CODE='D2'

                   AND A.ORD_NO = B.ORD_NO

                ) B ON TRUE

 WHERE A.CUST_ID = 'cust_2';


 Nested Loop (actual time=33.267..993.630 rows=3334 loops=1)

   Buffers: shared hit=47729 read=30981 written=1

   ->  Gather (actual time=33.147..81.172 rows=10000 loops=1)

         Workers Planned: 2

         Workers Launched: 2

         Buffers: shared hit=2 read=8649

         ->  Parallel Seq Scan on ord a (actual time=4.902..137.208 rows=3333 loops=3)

               Filter: (cust_id = 'cust_2'::text)

               Rows Removed by Filter: 330000

               Buffers: shared hit=2 read=8649

   ->  WindowAgg (actual time=0.089..0.089 rows=0 loops=10000)

         Buffers: shared hit=47727 read=22332 written=1

         ->  Index Scan Backward using ord_item_x01 on ord_item b (actual time=0.080..0.085 rows=0 loops=10000)

               Index Cond: (ord_no = a.ord_no)

               Filter: (discount_code = 'D2'::text)

               Rows Removed by Filter: 3

               Buffers: shared hit=47727 read=22332 written=1

 Planning Time: 0.295 ms

 Execution Time: 995.217 ms

(19 rows)


I am using the first_value() analytic function over the ORD_ITEM data set in order to get the first PROD_NO value sorted by the ORD_QANTITY value. With the descending index range scan, the optimizer didn't have to do the sort operation, which seems quite efficient. In the execution plan, however, you can spot the WindowAgg operation. Note that while accessing the ord_item_x01 index, the number of block I/Os is 47727 + 22332 = 70059.


The following is the code I rewrote in order to eliminate the WindowAgg operation. This query retrieves the same data set.


SELECT A.ORD_NO, B.PROD_NO

  FROM ORD A

  JOIN LATERAL (SELECT ORD_NO, PROD_NO

                 FROM ORD_ITEM B

                 WHERE DISCOUNT_CODE='D2'

                   AND A.ORD_NO = B.ORD_NO

                 ORDER BY B.ORD_QUANTITY DESC

                FETCH NEXT 1 ROWS ONLY

                ) B ON TRUE

 WHERE A.CUST_ID = 'cust_2';


The execution plan is presented below.


 Nested Loop (actual time=0.879..581.447 rows=3334 loops=1)

   Buffers: shared hit=42232 read=29790

   ->  Gather (actual time=0.823..17.377 rows=10000 loops=1)

         Workers Planned: 2

         Workers Launched: 2

         Buffers: shared hit=1 read=8650

         ->  Parallel Seq Scan on ord a (actual time=0.093..114.961 rows=3333 loops=3)

               Filter: (cust_id = 'cust_2'::text)

               Rows Removed by Filter: 330000

               Buffers: shared hit=1 read=8650

   ->  Limit (actual time=0.055..0.055 rows=0 loops=10000)

         Buffers: shared hit=42231 read=21140

         ->  Index Scan Backward using ord_item_x01 on ord_item b (actual time=0.053..0.053 rows=0 loops=10000)

               Index Cond: (ord_no = a.ord_no)

               Filter: (discount_code = 'D2'::text)

               Rows Removed by Filter: 2

               Buffers: shared hit=42231 read=21140

 Planning Time: 0.357 ms

 Execution Time: 583.044 ms


Notice that while accessing the ord_item_x01 index, the number of block I/Os is 42231 + 21140 = 63371. By getting rid of the analytic function, the number of block I/Os dropped from 70059 to 63371.


Conclusion

If you have an analytic function that has an "order by" clause in a query block and the planner decides that it can use the WindowAgg operation, you should try to eliminate the WindowAgg operation. One of the methods of doing it is to take advantage of a pagination tuning technology of using the FETCH NEXT 1 ROWS ONLY clause.



   

postgresdba.com