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.
