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


총 게시물 64건, 최근 0 건
   

Index Skip Scan 을 이용한 Tuning

글쓴이 : 모델광 날짜 : 2021-04-04 (일) 00:43 조회 : 41
아래는 Oracle의 Index Skip Scan을 모방한 SQL 튜닝 사례이다.
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 character varying(4) NOT NULL
);
CREATE INDEX sales_x01 ON portal.sales USING btree (product_id, sale_date, eur_value);
- 튜닝 전 SQL
SELECT SALE_DATE, EUR_VALUE, EMPLOYEE_ID
  FROM SALES
 WHERE SALE_DATE = DATE '2021-03-29'
   AND EUR_VALUE > 700;
- 튜닝 전 실행계획 
Seq Scan on sales (actual time=314.511..314.585 rows=212 loops=1)
Filter: ((eur_value > '700'::numeric) AND (sale_date = '2021-03-29'::date))
Rows Removed by Filter: 2206042
Buffers: shared hit=5015 read=17730
Planning Time: 0.134 ms
Execution Time: 314.696 ms
WHERE clause에 PRODUCT_ID 컬럼이 존재하지 않아서 인덱스를 액세스 할 수 없다.
아래의 쿼리로 SALES테이블의 통계정보를 검토해 보았다.
SELECT tablename, attname, n_distinct FROM PG_STATS WHERE TABLENAME='sales';
tablename attname n_disinct
sales sale_id -1.0
sales employee_id 143.0
sales subsidiary_id 1.0
sales sale_date 3129.0
sales eur_value 94470.0
sales product_id 26.0
sales quantity 6.0
sales channel 2.0
위 정보를 보면 PRODUCT_ID의 distinct value가 26 이므로 오라클이었으면 index skip scan이 동작하였을 것이다.
PG에서 index skip scan처럼 실행계획이 나오도록 하기 위해 SQL을 아래와 같이 수정하였다.
WITH clause를 이용해서 PRODUCT_ID의 distinct value를 빨리 추출하는 것이 본 SQL 튜닝의 핵심 원리이다.
간단하게는 (SALE_DATE, EUR_VALUE) 인덱스를 추가하면 되지만, 이럴경우 WRITE 부하증가/인덱스 추가에 따른 옵티마이저 비용증가/다른 sql들의 성능저하 가능성 등의 문제점이 있다.
WITH RECURSIVE W AS (
SELECT MIN(PRODUCT_ID) AS  PRODUCT_ID
  FROM SALES
UNION ALL
SELECT (SELECT MIN(PRODUCT_ID) FROM SALES A WHERE A.PRODUCT_ID > W.PRODUCT_ID)
  FROM W
 WHERE PRODUCT_ID IS NOT NULL
)
SELECT SALE_DATE, EUR_VALUE, EMPLOYEE_ID
  FROM SALES
 WHERE PRODUCT_ID IN (SELECT PRODUCT_ID
                        FROM W)
   AND SALE_DATE = DATE '2021-03-29'
   AND EUR_VALUE > 700;
아래는 위 수정 후 SQL의 실행계획이다.
Nested Loop (actual time=0.361..0.600 rows=212 loops=1)
Buffers: shared hit=357
CTE w
-> Recursive Union (actual time=0.046..0.329 rows=27 loops=1)
Buffers: shared hit=89
-> Result (actual time=0.045..0.046 rows=1 loops=1)
Buffers: shared hit=4
InitPlan 3 (returns $1)
-> Limit (actual time=0.042..0.043 rows=1 loops=1)
Buffers: shared hit=4
-> Index Only Scan using sales_x01 on sales sales_1 (actual time=0.041..0.042 rows=1 loops=1)
Index Cond: (product_id IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=4
-> WorkTable Scan on w w_1 (actual time=0.010..0.010 rows=1 loops=27)
Filter: (product_id IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=85
SubPlan 2
-> Result (actual time=0.009..0.009 rows=1 loops=26)
Buffers: shared hit=85
InitPlan 1 (returns $3)
-> Limit (actual time=0.009..0.009 rows=1 loops=26)
Buffers: shared hit=85
-> Index Only Scan using sales_x01 on sales a (actual time=0.008..0.008 rows=1 loops=26)
Index Cond: ((product_id IS NOT NULL) AND (product_id > w_1.product_id))
Heap Fetches: 6
Buffers: shared hit=85
-> HashAggregate (actual time=0.351..0.358 rows=27 loops=1)
Group Key: w.product_id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=89
-> CTE Scan on w (actual time=0.047..0.338 rows=27 loops=1)
Buffers: shared hit=89
-> Index Scan using sales_x01 on sales (actual time=0.004..0.007 rows=8 loops=27)
Index Cond: ((product_id = w.product_id) AND (sale_date = '2021-03-29'::date) AND (eur_value > '700'::numeric))
Buffers: shared hit=268
Planning Time: 0.263 ms
Execution Time: 0.762 ms
결론적으로 ELAPSED TIME은 314 msec --> 0.76sec,
Block I/O는 4983+17762 에서 357 로 줄었다.

   

postgresdba.com