설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은
총 게시물 162건, 최근 0 건
   

SELECT DISTINCT ON 성능 검토

글쓴이 : 모델광 날짜 : 2021-04-01 (목) 14:10 조회 : 3850
distinct on 절은 표준SQL은 아니고, PostgreSQL에서만 사용할 수 있는 문법이다.
SQL 튜닝 중에 distinct on 절을 사용해 보았으나, 속도가 더 느려져서 분석 내용을 공유한다.
결론부터 얘기하자면, distinct on 절 대신에 표준 SQL 문법을 사용하라.

SALES 테이블 구조
CREATE TABLE sales (
  sale_id       NUMERIC NOT NULL,
  employee_id   NUMERIC NOT NULL,
  subsidiary_id NUMERIC NOT NULL,
  sale_date     DATE    NOT NULL,
  eur_value     NUMERIC(17,2) NOT NULL,
  product_id    BIGINT  NOT NULL,
  quantity      INTEGER NOT NULL,
  CHANNEL          VARCHAR(4) NOT NULL,
  CONSTRAINT sales_pk     
     PRIMARY KEY (sale_id),
  CONSTRAINT sales_emp_fk 
     FOREIGN KEY          (subsidiary_id, employee_id)
      REFERENCES employees(subsidiary_id, employee_id)
);

검토 대상 SQL
아래 SQL은 판매일자별로 EUR_VALUE 값이 가장 큰 row를 하나씩 출력한다.
SELECT DISTINCT ON (SALE_DATE) SALE_DATE, SALE_ID
      , EMPLOYEE_ID, EUR_VALUE
  FROM SALES
ORDER BY SALE_DATE, EUR_VALUE DESC;
아래 실행계획을 보면, SALES 테이블을 full scan 후 Sorting 작업을 수행했음을 알 수 있다.
대부분의 elapsed time도 Sort 동작이 차지하고 있다.

| Unique  (cost=367802.71..378835.02 rows=3128 width=21) (actual time=3018.508..3361.263 rows=3128 loops=1)                    |
|   Buffers: shared hit=3516 read=19236, temp read=18122 written=18178                                                         |
|   ->  Sort  (cost=367802.71..373318.87 rows=2206461 width=21) (actual time=3018.507..3232.424 rows=2206461 loops=1)          |
|         Sort Key: sale_date, eur_value DESC                                                                                  |
|         Sort Method: external merge  Disk: 71264kB                                                                           |
|         Buffers: shared hit=3516 read=19236, temp read=18122 written=18178                                                   |
|         ->  Seq Scan on sales  (cost=0.00..44812.61 rows=2206461 width=21) (actual time=0.045..276.905 rows=2206461 loops=1) |
|               Buffers: shared hit=3512 read=19236                                                                            |
| Planning:                                                                                                                    |
|   Buffers: shared hit=4                                                                                                      |
| Planning Time: 0.068 ms                                                                                                      |
| Execution Time: 3374.092 ms

일반적인 SQL 작성 방식으로 위 SQL을 아래와 같이 수정했다.

SELECT B.SALE_DATE, B.SALE_ID, B.EMPLOYEE_ID, B.EUR_VALUE
  FROM
     (SELECT SALE_DATE, (MAX(ARRAY[EUR_VALUE,SALE_ID]))[2] AS SALE_ID
        FROM SALES
    GROUP BY SALE_DATE) A
    JOIN SALES B
      ON A.SALE_ID = B.SALE_ID
ORDER BY SALE_DATE;

 Sort (actual time=889.261..889.566 rows=3128 loops=1)                                                                                                             
   Output: b.sale_date, b.sale_id, b.employee_id, b.eur_value                                                                                                         |
   Sort Key: b.sale_date                                                                                                                                              |
   Sort Method: quicksort  Memory: 341kB                                                                                                                              |
   Buffers: shared hit=16184 read=19076                                                                                                                               |
   ->  Nested Loop (actual time=872.391..888.349 rows=3128 loops=1)                                                                                       
         Output: b.sale_date, b.sale_id, b.employee_id, b.eur_value                                                                                                   |
         Inner Unique: true                                                                                                                                           |
         Buffers: shared hit=16184 read=19076                                                                                                                         |
         ->  HashAggregate (actual time=872.374..873.590 rows=3128 loops=1)                                                                             
               Output: sales.sale_date, (max(ARRAY[sales.eur_value, sales.sale_id]))[2]                                                                          
               Group Key: sales.sale_date                                                                                                                             |
               Batches: 1  Memory Usage: 625kB                                                                                                                        |
               Buffers: shared hit=3672 read=19076                                                                                                                    |
               ->  Seq Scan on portal.sales (actual time=0.077..197.715 rows=2206461 loops=1)                                                       
                     Output: sales.sale_id, sales.employee_id, sales.subsidiary_id, sales.sale_date, sales.eur_value, sales.product_id, sales.quantity, sales.channel |
                     Buffers: shared hit=3672 read=19076                                                                                                              |
         ->  Index Scan using sales_pk on portal.sales b (actual time=0.004..0.004 rows=1 loops=3128)                                         
               Output: b.sale_id, b.employee_id, b.subsidiary_id, b.sale_date, b.eur_value, b.product_id, b.quantity, b.channel           
               Index Cond: (b.sale_id = ((max(ARRAY[sales.eur_value, sales.sale_id]))[2]))                                                                      
               Buffers: shared hit=12512                                                                                                                              |
 Planning Time: 0.113 ms                                                                                                                                              |
 Execution Time: 889.726 ms

수정 후 SQL은 SALES 테이블을 2회 액세스 했음에도 불구하고 ELAPSED TIME은 3.3 SEC --> 0.9 SEC로 향상되었다.
실행계획을 보면 DISTINCT ON 사용 시에는 SALES 전체에 대해서 SORTING이 발생했고, 수정 후는 SORING작업 대신에 HashAggregate 동작하면서 elapsed time이 많이 줄어들었다. 수정 후 SQL은 인라인 뷰 내부에 ORDER BY가 없기 때문에 HashAggregate만으로 MAX 값을 추출할 수 있는 것이다. 수정 후 SQL에서도 최종적으로는 Sort가 발생했으나 이것은 SALES 테이블에 대해서 동작한 것이 아니라, HashAggregate로 줄어든 3128 row에 대해서만 sort 한 것이기 때문에 성능에 영향이 미미하다. 
PG에서는 GroupAggregate 또는 Sort operation이 있으면, HashAggregate operation으로 동작하도록 할 수 있는 방안을 강구해야 한다.  DISTINCT ON 절이 readability도 좋고, 작성하기 편하지만, 성능이 중요한 시스템이라면 사용을 지양해야 한다.

Added on JULY 22, 2023
I am afraid the conclusion I made here is incorrect. Please refer to the following note.
https://www.postgresdba.com/bbs/board.php?bo_table=C05&wr_id=248

   

postgresdba.com
| 개인정보취급방침 | 책임의한계와 법적고지 | 이메일무단수집거부
Copyright ⓒ PostgresDBA.COM All rights reserved.
E-MAIL: root@postgresdba.com