아래는 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 로 줄었다.
6월17일 추가 사항
우연히 아래의 블로그를 읽게 되었다.
Index Skip Scan 은 '2018 부터 구현하려고 시도하고 있고, PostgreSQL 14 에서도 구현되지 않을 것이라고 한다. 위에 가술한 technique은 적어도 2022년까지는 써먹을 수 있을 것이다.
https://blog.timescale.com/blog/how-we-made-distinct-queries-up-to-8000x-faster-on-postgresql/
6월19일 추가 사항
필자가 위 내용을 쓰면서 스스로 창의적인 방법을 생각해 냈다고 기뻐했었다.
하지만 오늘 googling하다가 적어도 2012년도 부터 이미 널리 사용되고 있는 technique이라는 것을 알게 되었다.
하늘 아래 새 것은 없는가 보다.
http://orasql.org/2012/09/21/distinct-values-by-index-topn/ |
