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


총 게시물 166건, 최근 1 건
   

WindowAgg Optimization 2

글쓴이 : 모델광 날짜 : 2024-01-20 (토) 09:04 조회 : 616
During an Oracle to PostgreSQL migration, I was tasked with optimizing a query that ran twice as slow in PostgreSQL. The migrated query in PostgreSQL appeared somewhat convoluted.
I'll postpone the script to create the test data to the end of this note and start with the query that required optimizations in PostgreSQL 13.2:

SELECT ORD_NO
             , CUST_NO
             , COUNT(*) FILTER (WHERE RN1=1) OVER () AS C_CNT
             , COUNT(*) FILTER (WHERE RN2=1) OVER () AS O_CNT
 FROM (
            SELECT ORD_NO
                         , CUST_NO
                         , ROW_NUMBER() OVER (PARTITION BY CUST_NO) AS RN1
                         , ROW_NUMBER() OVER (PARTITION BY ORD_STATUS_CD) AS RN2
              FROM ONLINE_ORDER
          ) FOO;


And here is the resuting execution plan obtained when I ran the query in PostgreSQL 13.2.

WindowAgg (actual time=1810.866..1931.919 rows=1000000 loops=1)
  Buffers: shared hit=32 read=35447, temp read=24703 written=19860
  ->  Subquery Scan on foo (actual time=1138.963..1587.717 rows=1000000 loops=1)
        Buffers: shared hit=32 read=35447, temp read=14692 written=14732
        ->  WindowAgg (actual time=1138.961..1513.119 rows=1000000 loops=1)
              Buffers: shared hit=32 read=35447, temp read=14692 written=14732
              ->  Sort (actual time=1138.954..1258.692 rows=1000000 loops=1)
                    Sort Key: online_order.cust_no
                    Sort Method: external merge  Disk: 33352kB
                    Buffers: shared hit=32 read=35447, temp read=14692 written=14732
                    ->  WindowAgg (actual time=404.638..729.175 rows=1000000 loops=1)
                          Buffers: shared hit=32 read=35447, temp read=6363 written=6382
                          ->  Sort (actual time=404.631..504.186 rows=1000000 loops=1)
                                Sort Key: online_order.ord_status_cd
                                Sort Method: external merge  Disk: 25472kB
                                Buffers: shared hit=32 read=35447, temp read=6363 written=6382
                                ->  Seq Scan on online_order (actual time=0.088..149.742 rows=1000000 loops=1)
                                      Buffers: shared hit=32 read=35447
Planning Time: 0.074 ms
Execution Time: 1967.840 ms


In the migrated query, we are calculating the count of distinct CUST_NO values and ORD_STATUS_CD values across the entire set of rows. We know that the FILTER clause followed by COUNT(*) is specific to PostgreSQL. This led me to wonder how the original query in the Oracle database was structured. And the query in Oracle was of the following format:

 
SELECT ORD_NO
              , CUST_NO
              , COUNT(DISTINCT CUST_NO) OVER ()        AS C_CNT
              , COUNT(DISTINCT ORD_STATUS_CD) OVER ()  AS O_CNT
    FROM ONLINE_ORDER;


For your reference, here is the execution plan pulled from memory on Oracle 12.2.

Plan hash value: 2114875192
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |   1000K|00:00:00.94 |   19014 |  19005 |       |       |          |

|   1 |  WINDOW SORT        |              |      1 |   1000K|   1000K|00:00:00.94 |   19014 |  19005 |    39M|  2230K|   35M (0)|

|   2 |   WINDOW SORT       |              |      1 |   1000K|   1000K|00:00:00.48 |   19014 |  19005 |    35M|  2127K|   31M (0)|

|   3 |    TABLE ACCESS FULL| ONLINE_ORDER |      1 |   1000K|   1000K|00:00:00.07 |   19014 |  19005 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------


We can observe that the query  is twice as fast in Oracle (1.96 seconds vs. 0.94 seconds).
When I asked the developer who converted the query why he did change the query in PostgreSQL, he said that the query with COUNT(DISTINCT col_name) OVER () did not work in PostgreSQL. When I ran the Oracle query in PostgreSQL, I got this error message:

ERROR: DISTINCT is not implemented for window functions

So the query in PostgreSQL, converted by the develper, appeared to be a reasonable strategy in extracting the count of disticnt values of two columns.
However, the problem was that the query was slower than in the Oracle database. When we take a closer look at the execution plan in PostgreSQL, we can observe that we have two Sort operations and three WindowAgg operations in PostgreSQL. Both the Sort and WindowAgg operations are quite expensive.

One of the principles in optimizing a query is to reduce the Sort, WindowAgg,  GroupAgg operations.
Considering the principle, we can reconstruct the query as follows:

WITH counts AS (
    SELECT
        COUNT(DISTINCT CUST_NO) AS C_CNT,
        COUNT(DISTINCT ORD_STATUS_CD) AS O_CNT
    FROM ONLINE_ORDER
)
SELECT
    ORD_NO,
    CUST_NO,
    (SELECT C_CNT FROM counts) AS C_CNT,
    (SELECT O_CNT FROM counts) AS O_CNT
FROM ONLINE_ORDER;


And here is the execution plan:

Seq Scan on online_order (actual time=585.542..683.183 rows=1000000 loops=1)
  Buffers: shared hit=32209 read=8609, temp read=2094 written=2121
  CTE counts
    ->  Aggregate (actual time=585.493..585.494 rows=1 loops=1)
          Buffers: shared hit=16089 read=4320, temp read=2094 written=2121
          ->  Seq Scan on online_order online_order_1 (actual time=0.001..69.950 rows=1000000 loops=1)
                Buffers: shared hit=16089 read=4320
  InitPlan 2 (returns $1)
    ->  CTE Scan on counts (actual time=585.497..585.498 rows=1 loops=1)
          Buffers: shared hit=16089 read=4320, temp read=2094 written=2121
  InitPlan 3 (returns $2)
    ->  CTE Scan on counts counts_1 (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.054 ms
Execution Time: 712.138 ms


Here is the break-down of the execution plan:
1) Main Query (Seq Scan on online_order node)
    It is a sequential scan of the table online_order, which returned 1,000,000 rows.
2) CTE counts node
   - This node is based on an Aggregate operation, which suggests that it is calculating some aggregated values.
   - The CTE scans the same online_order table (aliased as online_order_1) that the main query scan.
3) InitPlan2 and 3
   - There are two "InitPlan" operations, denoted as $1 and $2.
   - They access the results of CTE "count" to retrieve values from it.

Note that there is only one Aggregate node. Even though we are accessing the online_order table two times, the elapsed time dropped from 1931 to 712 ms.

In PostgreSQL, the "InitPlan" node is executed only once. When we look at the actual time part of each node, we can observe that the "CTE counts" node was executed before other nodes were executed.

For entertainment, I executed the revised query in Oracle 12c.
When I executed the revised query in Oracle, the query became faster as well.
Here is the plan of the restructured query in Oracle 12c:

Plan hash value: 447979334

-----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

-----------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |              |      1 |        |   1000K|00:00:00.59 |   47839 |  38010 |       |       |          |

|   1 |  NESTED LOOPS        |              |      1 |   1000K|   1000K|00:00:00.59 |   47839 |  38010 |       |       |          |

|   2 |   VIEW               |              |      1 |      1 |      1 |00:00:00.21 |   19014 |  19005 |       |       |          |

|   3 |    HASH GROUP BY     |              |      1 |      1 |      1 |00:00:00.21 |   19014 |  19005 |    24M|  5036K|   25M (0)|

|   4 |     TABLE ACCESS FULL| ONLINE_ORDER |      1 |   1000K|   1000K|00:00:00.06 |   19014 |  19005 |       |       |          |

|   5 |   TABLE ACCESS FULL  | ONLINE_ORDER |      1 |   1000K|   1000K|00:00:00.31 |   28825 |  19005 |       |       |          |

-----------------------------------------------------------------------------------------------------------------------------------


In Oracle, the database accessed the ONLINE_ORDER table twice. However, the two WINDOW SORT operations were replaced by a single HASH GROUP BY operation. It is worth noting that the elapsed time descreased from 0.94 to 0.59, even though the block I/O increased from 19014 to 47839.

Conclusion

Whenever you have the need to use analytic functions, it is worth just pausing for a second, and making sure that there is no other alternative to construct the query.

The main point of this note is to point out that it's a good idea to remove SORT operations when we start playing around with analytic functions. At times the strategy of accessing a table twice is a viable option to avoid sort operations. In particular, be cautious of using analytical functions.

Sometimes, you might reduce block I/O by leveraging window functions but that implementation comes at the cost of longer elapsed time. When we eliminate analytic functions, we have a better chance of achieving improved performance.

Footnote
The developer who ported the Oracle query to PostgreSQL, was, of course, very smart to use PostgreSQL specific syntax. Query changes in a migration project may introduce significant side effects. What we have to do is find big wins using simple strategies that avoid complicated code changes - we need to be sure that we haven't introduced side-effects that mean we have moved the problem rather than solving it.

Test Code
If you want to experiment further, here is the SQL to create the test data in PostgreSQL 13.2:

drop table online_order;
create table online_order (
ord_no    bigint      not null,
cust_no   int          not null,
ord_date  timestamp    not null,
ord_dt    varchar(8)        not null,
ord_status_cd varchar(1) not null,
comment   varchar(100)
);

​INSERT into online_order
select i, mod(i,1000000) as cust_no
      ,current_date - mod(i,1000) as ord_date
      ,to_char((current_date - mod(i,1000)),'yyyymmdd') as ord_dt
      ,(mod(i,4) + 1) as ord_status_cd
      ,lpad('x',100,'x')
  from generate_series(1,2000000,2) a(i);

   

postgresdba.com