"If there is a unique way of speaking or grammar used only by the 300 people working in Yeoido, it would be more like Yeoido dialect than Yeoido grammer. I will speak language used by 50 million people."
I would like to adapt Mr.Han's comments to the context of writing queries in PostgreSQL(with apologies to Mr. Han):
"If there is a unique SQL expression or grammar used exclusively in the PostgreSQL world, even if it is not the standard SQL used by millions, I will employ it."
It is worth noting that you don't have to stick to standard SQL when considering performance. This note demonstrates that standard SQL may harm your database performance. If you want to achieve maximum performance, you need to have thorough knowlege of your database's dialect.
In a migration project from Oracle to PostgreSQL, I encountered an inefficient SQL statement while monitoring the Postgres log. The log file displayed a series of repetitive and inefficient queries with the following structure: I have placed the code to generate the test data at the end of this note. If you wish to conduct the experiment, refer to the Test Code section of this note.
SELECT RX.* FROM (SELECT COUNT(*) OVER() AS TOTAL_COUNT , T1.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY D.ORD_NO, D.PROD_ID) AS DISPLAY_ORDER , D.ORD_LINE_NO, D.ORD_NO, D.PROD_ID, O.COMMENT FROM ORDERS_DETAIL D, ORDERS O WHERE O.ORD_NO BETWEEN 1000 AND 5000 AND D.ORD_NO = O.ORD_NO ) T1 ) RX WHERE DISPLAY_ORDER BETWEEN 11 AND 20;
In this query, the innermost subquery(T1) retrieves data from the ORDERS_DETAIL and ORDERS tables with certain conditions. It assigns a sequence number(DISPLAY_ORDER) to each row based on the ORDER BY clause. The middle subquery(RX) adds an additional column, TOTAL_COUNT, using the COUNT(*) OVER () window function. This counts the total number of rows generated by the T1 subqery and assigns this count to all rows as TOTAL_COUNT. The WHERE clause in the outermost query restricts the result set to rows where the sequence number(DISPLAY_ORDER) falls within the range of 11 to 20.
The reason I consider the query suboptimal is that the query requires two Sort operations. Let's take a look at the execution plan of the query when run under PostgreSQL 13.2:
Subquery Scan on rx (actual time=18.071..20.750 rows=10 loops=1) Filter: ((rx.display_order >= 11) AND (rx.display_order <= 20)) Rows Removed by Filter: 19995 Buffers: shared hit=32056 -> WindowAgg (actual time=18.067..19.903 rows=20005 loops=1) Buffers: shared hit=32056 -> WindowAgg (actual time=0.072..15.073 rows=20005 loops=1) Buffers: shared hit=32056 -> Incremental Sort (actual time=0.069..12.027 rows=20005 loops=1) Sort Key: d.ord_no, d.prod_id Presorted Key: d.ord_no Full-sort Groups: 572 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB Buffers: shared hit=32056 -> Nested Loop (actual time=0.025..9.429 rows=20005 loops=1) Buffers: shared hit=32056 -> Index Scan using pk_orders on orders o (actual time=0.013..0.438 rows=4001 loops=1) Index Cond: ((ord_no >= 1000) AND (ord_no <= 5000)) Buffers: shared hit=48 -> Index Scan using orders_detail_x01 on orders_detail d (actual time=0.001..0.002 rows=5 loops=4001) Index Cond: (ord_no = o.ord_no) Buffers: shared hit=32008 Planning: Buffers: shared hit=24 Planning Time: 0.338 ms
Execution Time: 20.925 ms
You will notice two WindowAgg operations about the execution plan. One is for ROW_NUMBER() and the other is for COUNT(*) OVER(). These WindowAgg operations involve sorting and scanning, contributing to the query's slower performance. To improve performance, it is essential to reduce the number of WindowAgg operations. Back in 202I, I wrote an article on this topic, explaining how to optimize queries that use window functions.
To optimize the query, we should merge the innermost subquery T1 with the subquery RX and make sure the frame_clause is identical for both window functions, allowing them to share WindowAgg operations.
Here is my reenginnered version of the query, along with its execution plan:
SELECT COUNT(*) OVER(ORDER BY D.ORD_NO, D.PROD_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS T_CNT
, ROW_NUMBER() OVER (ORDER BY D.ORD_NO, D.PROD_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS display_order , D.ORD_LINE_NO, D.ORD_NO, D.PROD_ID, O.COMMENT FROM ORDERS_DETAIL D, ORDERS O WHERE O.ORD_NO BETWEEN 1000 AND 5000 AND D.ORD_NO = O.ORD_NO OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Limit (actual time=13.316..13.319 rows=10 loops=1) Buffers: shared hit=32056 -> WindowAgg (actual time=13.312..13.316 rows=20 loops=1) Buffers: shared hit=32056 -> Incremental Sort (actual time=0.044..11.473 rows=20005 loops=1) Sort Key: d.ord_no, d.prod_id Presorted Key: d.ord_no Full-sort Groups: 572 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB Buffers: shared hit=32056 -> Nested Loop (actual time=0.012..9.010 rows=20005 loops=1) Buffers: shared hit=32056 -> Index Scan using pk_orders on orders o (actual time=0.008..0.439 rows=4001 loops=1) Index Cond: ((ord_no >= 1000) AND (ord_no <= 5000)) Buffers: shared hit=48 -> Index Scan using orders_detail_x01 on orders_detail d (actual time=0.001..0.002 rows=5 loops=4001) Index Cond: (ord_no = o.ord_no) Buffers: shared hit=32008 Planning: Buffers: shared hit=24 Planning Time: 0.189 ms
Execution Time: 13.469 ms
As expected, we now have only one WindowAgg operation, In this case, the identical frame_clause for both window funtions could make one of the cirtical operations (WindowAgg) disappear copletely. Even though the block I/O is the same, the elapsed time dropped from 20 ms to 13 ms. Take my word for tit; the COUNT(*) and ROW_NUMBER() functions were merged into one WindowAgg node. It is a pity that the execution plan does not clearly reveal it. We need the capability to recognize the invisible from the execution plan.
After tuning the query, I asked the developer to ship my rewritten version of the query to the TO-BE system. However, the developer rejected my request, stating that he has to comply with the SQL guideline established by an Oracle consultant a few years ago. His comment struck a bit of a nerve. When I asked for the guideline, he provided me with the following sample SQL from the guideline.
Faulty SQL Guideline
When I reviewed the guideline, I suspected that the Oracle consultant might not have come from an Oracle background but rather from SQL Server. In SQL Server, the above query is indeed the optimal choice. However, unlike other databases, Oracle has its specific mechanism to efficiently handle this requirement, such as the ROWNUM pseudocolumn.
The original query was the one that adhered to this misleading guideline. Here is the resuling execution plan I obtained when I ran the query on Oracle 12c:
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.04 | 20690 | | | | |* 1 | VIEW | | 1 | 4034 | 10 |00:00:00.04 | 20690 | | | | | 2 | WINDOW BUFFER | | 1 | 4034 | 20005 |00:00:00.04 | 20690 | 1612K| 624K| 1432K (0)| | 3 | VIEW | | 1 | 4034 | 20005 |00:00:00.03 | 20690 | | | | | 4 | WINDOW SORT | | 1 | 4034 | 20005 |00:00:00.03 | 20690 | 1895K| 658K| 1684K (0)| | 5 | NESTED LOOPS | | 1 | 4034 | 20005 |00:00:00.03 | 20690 | | | | | 6 | NESTED LOOPS | | 1 | 4034 | 20005 |00:00:00.01 | 685 | | | | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS | 1 | 4002 | 4001 |00:00:00.01 | 36 | | | | |* 8 | INDEX RANGE SCAN | PK_ORDERS | 1 | 4002 | 4001 |00:00:00.01 | 11 | | | | |* 9 | INDEX RANGE SCAN | ORDERS_DETAIL_X01 | 4001 | 1 | 20005 |00:00:00.01 | 649 | | | | | 10 | TABLE ACCESS BY INDEX ROWID | ORDERS_DETAIL | 20005 | 1 | 20005 |00:00:00.01 | 20005 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------- In Oracle the WINDOW BUFFER is utilized in SQL statements with analytic functions whose OVER() clause does not have a PARTITION BY clause or an ORDER BY clause. The WINDOW BUFFER operation necessitates the Sort operation within the window range. IF the OVER() clause has either or both of these clauses, Oracle would employ the WINDOW SORT operation. We see a WINDOW SORT operation in the execution plan for the ROW_NUMBER() clause, which is followed by the ORDER BY option. And We see a less costly WINDOW BUFFER operation for the COUNT(*) OVER () clause.
For more in-depth information on WINDOW BUFFER and WINDOW SORT, please refer to these insightful blog posts. Science of Database :: 분석함수의 실행계획 - 2부 (tistory.com) Science of Database :: 분석함수의 실행계획 - 1부 (tistory.com) The above plan in Oracle is not optimal because it involves two Sort operations within WINDOW BUFFER and WINDOW SORT. In other databases such as SQL Server, there is no straightforward way to improve the query. However, Oracle offers a specific mechanism to handle the aforementioned SQL statement more efficiently. We can enhance the query by utilizing the ROWNUM pseudocolumn in Oracle. Here is the SQL statement using the ROWNUM column, followed by its execution plan:
SELECT * FROM ( SELECT TOTAL_CNT, ROWNUM AS DISPLAY_ORDER, ORD_LINE_NO, ORD_NO, PROD_ID, COMENT FROM ( SELECT COUNT(*) OVER () AS TOTAL_CNT , D.ORD_LINE_NO, D.ORD_NO, D.PROD_ID, O.COMENT FROM ORDERS_DETAIL D, ORDERS O WHERE O.ORD_NO BETWEEN 1000 AND 5000 AND D.ORD_NO = O.ORD_NO ORDER BY D.ORD_NO, D.PROD_ID ) T1 WHERE ROWNUM <= 20 ) WHERE DISPLAY_ORDER >= 11;
Plan hash value: 1246100083 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.03 | 20690 | | | | |* 1 | VIEW | | 1 | 20 | 10 |00:00:00.03 | 20690 | | | | |* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.03 | 20690 | | | | | 3 | VIEW | | 1 | 23 | 20 |00:00:00.03 | 20690 | | | | | 4 | WINDOW SORT | | 1 | 23 | 20 |00:00:00.03 | 20690 | 1895K| 658K| 1684K (0)| | 5 | NESTED LOOPS | | 1 | 23 | 20005 |00:00:00.03 | 20690 | | | | | 6 | NESTED LOOPS | | 1 | 23 | 20005 |00:00:00.01 | 685 | | | | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS | 1 | 4002 | 4001 |00:00:00.01 | 36 | | | | |* 8 | INDEX RANGE SCAN | PK_ORDERS | 1 | 4002 | 4001 |00:00:00.01 | 11 | | | | |* 9 | INDEX RANGE SCAN | ORDERS_DETAIL_X01 | 4001 | 1 | 20005 |00:00:00.01 | 649 | | | | | 10 | TABLE ACCESS BY INDEX ROWID | ORDERS_DETAIL | 20005 | 1 | 20005 |00:00:00.01 | 20005 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------- Note that the WINDOW BUFFER operation has been eliminated, signifying that the query no longer utilizes the 1432 Kbytes of PGA. Instead, in the plan, we can observe the COUNT STOPKEY operation, which means Oracle can stop processing once it retrieves the required number of rows. In this case, it only fetches the first 20 rows.
In contrast, the previous execution plan processes all the rows in the result set (20005 rows) with additional operations like WINDOW BUFFER, which is likely contributing to longer execution time and increased resource consumption. Even though the total block I/O remains the same, the previous query must have consumed more CPU and Memory(PGA). This is why I consider the guideline to be misleading.
Comparison of Sorting Mechanisms in Oracle and PostgreSQL By the way, you might be wondering why Oracle consumes 1684 Kbytes(look at the Used-Mem column) for sorting, while PostgreSQL only uses 27 Kbytes for soring. PostgreSQL consumed significantly less work_mem memory due to its incremental sort mechanism. While Oracle 12c allows you to use "fetch next N rows only" instead of "where rownum <= N" there is a hidden threat to using this feature because "fetch next N rows only" turns into a hidden row_number() over() analytic function. In an Oracle project, I typically discourage developers from using "fetch next N rows only". However in this particluar scenario, it performs as efficiently as the ROWNUM pseudocolumn mechanism. Here is the Oracle query employing the FETCH NEXT n ROWS ONLY clause, followed by its execution plan:
SELECT * FROM ( SELECT COUNT(*) OVER() AS T_CNT , ROW_NUMBER() OVER (ORDER BY D.ORD_NO, D.PROD_ID) AS display_order , D.ORD_LINE_NO, D.ORD_NO, D.PROD_ID, O.COMENT FROM ORDERS_DETAIL D, ORDERS O WHERE O.ORD_NO BETWEEN 1000 AND 5000 AND D.ORD_NO = O.ORD_NO ORDER BY D.ORD_NO, D.PROD_ID ) OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY ; Plan hash value: 2285971878 -------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.03 | 20690 | | | | |* 1 | VIEW | | 1 | 20 | 10 |00:00:00.03 | 20690 | | | | |* 2 | WINDOW NOSORT STOPKEY | | 1 | 4034 | 20 |00:00:00.03 | 20690 | 491K| 448K| | | 3 | VIEW | | 1 | 4034 | 20 |00:00:00.03 | 20690 | | | | | 4 | WINDOW SORT | | 1 | 4034 | 20 |00:00:00.03 | 20690 | 1895K| 658K| 1684K (0)| | 5 | NESTED LOOPS | | 1 | 4034 | 20005 |00:00:00.03 | 20690 | | | | | 6 | NESTED LOOPS | | 1 | 4034 | 20005 |00:00:00.01 | 685 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 4002 | 4001 |00:00:00.01 | 36 | | | | |* 8 | INDEX RANGE SCAN | PK_ORDERS | 1 | 4002 | 4001 |00:00:00.01 | 11 | | | | |* 9 | INDEX RANGE SCAN | ORDERS_DETAIL_X01 | 4001 | 1 | 20005 |00:00:00.01 | 649 | | | | | 10 | TABLE ACCESS BY INDEX ROWID | ORDERS_DETAIL | 20005 | 1 | 20005 |00:00:00.01 | 20005 | | | | -------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): 1 - filter((\"from$_subquery$_004\".\"rowlimit_$$_rownumber\"<=20 AND \"from$_subquery$_004\".\"rowlimit_$$_rownumber\">10)) 2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=20) 8 - access(\"O\".\"ORD_NO\">=1000 AND \"O\".\"ORD_NO\"<=5000) 9 - access(\"D\".\"ORD_NO\"=\"O\".\"ORD_NO\")
filter((\"D\".\"ORD_NO\"<=5000 AND \"D\".\"ORD_NO\">=1000))
Take note of the Predicate Information section. Operation id 2 shows us that "fetch next 10 rows only" has indeed been translated into the row_number() over (order by null) that we expected; but to our great joy, the WINDOW NOSORT STOPKEY operation makes the processing stop very early because Oracle recognises that the data is arriving in the right order (the ordering has been done in operation 4), so it isn't necessary to fetch all of it and sort it. WINDOW NOSORT STOPKEY, as the name implies, omits the sorting operation and aborts the execution when the desired number of rows has been fetched. The A-Rows column confirms this interpretation of what has happened.
You might notice, by the way that the optimizer has costed the query as if it were fetching all the rows (E-Rows = 4034), even though it "knows" that it is going to fetch only the first 20 rows(E-Rows=20).
Regarding the migration from Oracle to Postgres, what I want to emphasize here is that if you run this efficient Oracle query in PostgreSQL, you will experience poor performance because the internal implementations are different. I will not include the executioin plan in PostgreSQL here; confirming it is left as an exercise for you readers.
Don't use FETCH clause in Oracle Even though, in the above example, query performance using the FETCH NEXT n ROWS ONLY clause is equivalent to the rownum strategy, it is advisable to avoid it for the sake of safety. If you mistakenly omit the inline view and write the following query, even though the result set is correct, you'll experience degraded performance.
SELECT COUNT(*) OVER() AS T_CNT , ROW_NUMBER() OVER (ORDER BY D.ORD_NO, D.PROD_ID) AS display_order , D.ORD_LINE_NO, D.ORD_NO, D.PROD_ID, O.COMENT FROM ORDERS_DETAIL D, ORDERS O WHERE O.ORD_NO BETWEEN 1000 AND 5000 AND D.ORD_NO = O.ORD_NO OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
You might think that this plan is optimal because there is only one WINDOW SORT. However, take a closer look at the A-Rows column of the WINDOW SORT operation. It is now sorting 20005 rows, whereas the previously optimized query sorted only 20 rows. Without knowledge of the internal operations of analytical functions, we may be able to use analytical functions to arrive at the answer, but won't be able to improve performance. We need to read the invisible or conquer the internals of analytical functions to make tuning possible.
Conclusion When your query involves more than two window functions in PostgreSQL, it is beneficial to seek methods to make those frame clauses identical. Migrating the FETCH clause directly from Oracle to PostgreSQL without modification can cause some dramas in your PostgresQL database. Therefore, as a general standard, when you migrate the FETCH clause, you should refine the query. Even though "fetch next N rows only" is an ANSI compliant clause, the internal implementations of the clause differ between Oracle and PostgreSQL. Fortunately, PostgreSQL outperforms Oracle in terms of the performance of the clause.
FOOTNOTE Idle curiosity then prompted me to examine how query performance would be affected in this specific example when I disable the incremental sort operation. So I have adjusted the enable_incremental_sort parameter to false and reran the tuned query.
set enable_incremental_sort to off;
SELECT COUNT(*) OVER(ORDER BY D.ORD_NO, D.PROD_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS T_CNT
, ROW_NUMBER() OVER (ORDER BY D.ORD_NO, D.PROD_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS display_order , D.ORD_LINE_NO, D.ORD_NO, D.PROD_ID, O.COMMENT FROM ORDERS_DETAIL D, ORDERS O WHERE O.ORD_NO BETWEEN 1000 AND 5000 AND D.ORD_NO = O.ORD_NO OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Limit (actual time=10.742..10.744 rows=10 loops=1) Buffers: shared hit=25038 read=54 -> WindowAgg (actual time=10.739..10.742 rows=20 loops=1) Buffers: shared hit=25038 read=54 -> Merge Join (actual time=1.389..8.089 rows=20005 loops=1) Merge Cond: (d.ord_no = o.ord_no) Buffers: shared hit=25038 read=54 -> Index Scan using orders_detail_x01 on orders_detail d (actual time=0.007..4.984 rows=25001 loops=1) Buffers: shared hit=24990 read=54 -> Index Scan using pk_orders on orders o (actual time=0.007..0.398 rows=4001 loops=1) Index Cond: ((ord_no >= 1000) AND (ord_no <= 5000)) Buffers: shared hit=48 Planning: Buffers: shared hit=19 read=5 Planning Time: 0.309 ms
Execution Time: 10.909 ms
When we disable incremental sort, the plan transforms into a merge join, leading to reduced block I/O( from 32056 to 25092) and decreased elapsed time(from 13.4 ms to 10.9 ms). When the enable_incremental_sort parameter was introduced in PostgreSQL 13, I wondered why they allowed database administrators to set the value to "off" becuase I thought incremental sort was always advantageous. In this case, when we disabled incremental_sort, the access path changed. And it became apparent that incremental sort was detrimental to performance.
TEST CODE Here is a very simple model to demonstrate the usage of "fetch next N rows only" on PostgreSQL 13.2.
CREATE TABLE ORDERS_DETAIL( ORD_LINE_NO BIGINT NOT NULL ,ORD_NO BIGINT NOT NULL ,PROD_ID VARCHAR(10) NOT NULL ,COMMENT VARCHAR(100)
,ORD_AMT BIGINT
);
ALTER TABLE ORDERS_DETAIL ADD CONSTRAINT PK_ORDERS_DETAIL PRIMARY KEY(ORD_LINE_NO); CREATE INDEX ORDERS_DETAIL_X01 ON ORDERS_DETAIL(ORD_NO, PROD_ID);
INSERT INTO ORDERS_DETAIL SELECT i as ORD_LINE_NO , mod(i,1000000)+1 AS ORD_NO , 'PP'||MOD(i,10) AS PROD_ID , lpad('X',20,'Y') as comment , case when i < 1000 then i*100 else i end as prod_amt FROM generate_series(1,5000000) a(i); select min(ord_no), max(ord_no) from orders_detail limit 10;
ALTER TABLE ORDERS ADD CONSTRAINT PK_ORDERS PRIMARY KEY(ORD_NO);
insert into ORDERS select i as ord_no , 'C'||mod(i,2000) as cust_id , lpad('X',20,'Y') as comment , '20191001'::date+mod(i,60) as ord_date from generate_series(1,1000000) a(i);
