PostgreSQL Partition Table Full scan bug

PostgreSQL

 

오늘 이야기는 PosrgreSQL의 파티션 검색 버그에 대한 내용이다.

 

PostgreSQL에 Table이 Partition이 되어 있을 경우, 그리고 RANGE 파티션일 경우에 해당된다.

대상 DB는 PostgreSQL 10.x 에 해당하는 것이며 11 부터는 해결되었다고 함

 

 

예를들어 날짜 기반의 RANGE 파티션 테이블을 하나 만들어 보자

CREATE TABLE eventhistory(
 a VARCHAR(10),
 b VARCHAR(10),
 ......
 	AddTime TIMESTAMP
) PARTITION BY RANGE (Addtime);


CREATE TABLE eventhistory_20220210 PARTITION OF eventhistory FOR VALUES FROM ('20220210') TO ('20220211');
CREATE TABLE eventhistory_20220211 PARTITION OF eventhistory FOR VALUES FROM ('20220211') TO ('20220212');
CREATE TABLE eventhistory_20220212 PARTITION OF eventhistory FOR VALUES FROM ('20220212') TO ('20220213');
CREATE TABLE eventhistory_20220213 PARTITION OF eventhistory FOR VALUES FROM ('20220213') TO ('20220214');
CREATE TABLE eventhistory_20220214 PARTITION OF eventhistory FOR VALUES FROM ('20220214') TO ('20220215');
CREATE TABLE eventhistory_20220215 PARTITION OF eventhistory FOR VALUES FROM ('20220215') TO ('20220216');
CREATE TABLE eventhistory_20220216 PARTITION OF eventhistory FOR VALUES FROM ('20220216') TO ('20220217');
CREATE TABLE eventhistory_20220217 PARTITION OF eventhistory FOR VALUES FROM ('20220217') TO ('20220218');
CREATE TABLE eventhistory_20220218 PARTITION OF eventhistory FOR VALUES FROM ('20220218') TO ('20220219');
CREATE TABLE eventhistory_20220219 PARTITION OF eventhistory FOR VALUES FROM ('20220219') TO ('20220220');
CREATE TABLE eventhistory_20220220 PARTITION OF eventhistory FOR VALUES FROM ('20220220') TO ('20220221');
CREATE TABLE eventhistory_20220221 PARTITION OF eventhistory FOR VALUES FROM ('20220221') TO ('20220222');
CREATE TABLE eventhistory_20220222 PARTITION OF eventhistory FOR VALUES FROM ('20220222') TO ('20220223');

그리고 데이터를 15일치를 밀어 넣는다.

 

 

날짜를 조회조건에 넣어서 Plan을 확인해 보면 해당 Partition의 데이터만 잘 나온다

EXPLAIN
SELECT * 
FROM eventhistory
where addtime = '2022-02-24'::timestamp;

결과
Append  (cost=0.00..3600.40 rows=13 width=64)
  ->  Seq Scan on eventhistory_20220224  (cost=0.00..3600.40 rows=13 width=64)
        Filter: (addtime = '2022-02-24 00:00:00'::timestamp without time zone)

 

날짜를 now()로 해서 Plan을 확인해보면

EXPLAIN
SELECT * 
FROM eventhistory
where addtime = now()::date

결과
Append  (cost=0.00..111153.13 rows=214 width=69)
  ->  Seq Scan on eventhistory_20220210  (cost=0.00..6247.58 rows=12 width=63)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220211  (cost=0.00..9022.58 rows=17 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220212  (cost=0.00..7474.54 rows=14 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220213  (cost=0.00..8267.13 rows=15 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220214  (cost=0.00..7150.77 rows=14 width=63)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220215  (cost=0.00..7369.98 rows=14 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220216  (cost=0.00..6885.36 rows=12 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220217  (cost=0.00..7257.49 rows=14 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220218  (cost=0.00..7360.02 rows=14 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220219  (cost=0.00..8230.42 rows=15 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220220  (cost=0.00..7875.87 rows=15 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220221  (cost=0.00..8160.68 rows=15 width=63)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220222  (cost=0.00..7655.93 rows=14 width=63)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220223  (cost=0.00..7783.25 rows=14 width=64)
        Filter: (addtime = (now())::date)
  ->  Seq Scan on eventhistory_20220224  (cost=0.00..4393.57 rows=13 width=64)
        Filter: (addtime = (now())::date)
  ->  Index Scan using idx_eventhistory_20220225_1 on eventhistory_20220225  (cost=0.15..8.98 rows=1 width=676)
        Index Cond: (addtime = (now())::date)
  ->  Index Scan using idx_eventhistory_20220226_1 on eventhistory_20220226  (cost=0.15..8.98 rows=1 width=676)
        Index Cond: (addtime = (now())::date)

 

 

왜?? now() 를 조건절에 넣었을때 Full Scan을 하는가 ???

버그 같다..

즉, 이전 쿼리에서 addtime = '2022-02-24'::timestamp로 했을때는 변수가 static이지만 now() 를 사용하게 되면 이거는 가변 변수라서 Partition Table을 타지 않고 Full Scan을 하는 것 같다.

 

Query를 열심히 다 짜놓고, Application에서 구동을 하니 이상하게 시간이 지날수록 느려진다. Full Scan을 하는 것이다.

Partition Table인데 Full Scan 을 하다니...

 

해결방법

• 무조건 Query의 조건절에 bind를 사용해서 수행해야만한다. 
• 또는 현재 시간에 해당하는 함수를 만들어 사용할 수도 있다.

* 조건절에 static 으로 bind 되도록 Application 에서 수행하도록 한다.

SELECT * 
FROM eventhistory
where addtime = ?

 

* 또는 함수를 만들어서 호출하면 된다.

CREATE OR REPLACE FUNCTION now_immediate()
	RETURNS timestamp AS
$$
	SELECT now() AT TIME ZONE current_setting('TimeZone')
$$
LANGUAGE sql IMMUTABLE;



EXPLAIN
SELECT * 
FROM eventhistory
where addtime = now_immediate()::date



결과
Append  (cost=0.00..3688.84 rows=13 width=64)
  ->  Seq Scan on eventhistory_20220224  (cost=0.00..3688.84 rows=13 width=64)
        Filter: (addtime = '2022-02-24 15:04:00.494184'::timestamp without time zone)

 

 

의견

PostgreSQL를 22년전부터 개인적으로 사용을 했었고, 매우 안정적이라 생각이 든다.

그리고 라이선스가 MIT라서 제약조건도 없고 Update도 꾸준해서 좋다.

다만 약간의 버그가 있긴한데, 이런거는 좀 피해서 코딩을 하면 아주 훌륭한 DB라고 생각된다.

가끔 바큠 도는것만 빼면 ,,,

 

 

참고자료

https://nullpark.tistory.com/54

PostgreSQL Partition Table