Search

부정 조건에 취약한 PostgreSQL

— Oracle과 다른 필터 처리 위치가 만드는 I/O 차이
공동 작성 | SweetBoss · GPT-6 Astra
•
문제 제기·SQL·실험·최종 문장 점검: SweetBoss
•
본문 초안·문장 구성·소스 확인: GPT-6 Astra
실습 준비: PostgreSQL 예제를 직접 실습하려면 『SQL TUNER for PostgreSQL 기본원리편』에서 제공하는 실습 데이터베이스를 먼저 구성해야 합니다. Oracle 비교 예제는 대응하는 주문 데이터와 인덱스가 준비된 startdbora 환경에서 실행한 결과입니다.
인덱스로 회원 범위를 조회하면서, 중간 구간을 제외하는 부정 조건을 추가했다.
Oracle은 전체 Buffers가 3,424에서 1,751로 거의 절반 줄었다. PostgreSQL은 3,428로 그대로였다. 같은 범위에서 같은 데이터를 제외했는데, 이 실험에서 PostgreSQL은 Oracle보다 약 두 배의 논리적 블록 접근을 수행한 것이다.
차이는 조건을 검사하는 위치에 있었다. Oracle은 인덱스에서 제외 대상을 걸러낸 뒤 테이블에 접근했지만, PostgreSQL은 인덱스에 존재하는 컬럼인데도 테이블의 행을 가져온 뒤 제외했다.
Oracle에서 인덱스 필터로 처리되던 조건이 PostgreSQL에서도 같은 방식으로 처리될 것이라고 예상하면, 불필요한 테이블 접근을 놓칠 수 있다. SQL과 실행계획으로 확인해 보자.
이 글에서 비교하는 I/O는 Buffers로 확인한 논리적 블록 접근량이다. 물리 디스크 읽기 횟수나 실행시간보다, 각 DB에서 조건 추가 전후에 읽는 양이 어떻게 달라지는지를 살펴본다.

Oracle에서는 테이블 접근이 줄었다

두 환경 모두 회원 ID를 키로 사용하는 tr_ord_big_x31 인덱스를 이용한다. 먼저 Oracle에서 회원 ID가 M00001 이상, M00020 이하인 주문을 조회한다.
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(t1 tr_ord_big_x31) */ t1.*, COUNT(*) OVER() FROM startdbora.tr_ord_big t1 WHERE t1.mbr_id >= 'M00001' AND t1.mbr_id <= 'M00020';
SQL
복사
----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.23 | 3424 | 2986 | | | | | 1 | WINDOW BUFFER | | 1 | 312 | 200 |00:00:00.23 | 3424 | 2986 | 478K| 448K| 424K (0)| | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TR_ORD_BIG | 1 | 312 | 3444 |00:00:00.02 | 3424 | 2986 | | | | |* 3 | INDEX RANGE SCAN | TR_ORD_BIG_X31 | 1 | 312 | 3444 |00:00:00.01 | 11 | 8 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."MBR_ID">='M00001' AND "T1"."MBR_ID"<='M00020')
Plain Text
복사
3번 INDEX RANGE SCAN에서 회원 범위에 해당하는 3,444건의 ROWID를 전달하고, 2번 TABLE ACCESS BY INDEX ROWID BATCHED에서 해당 테이블 행을 가져온다. 인덱스 Buffers는 11, 전체 Buffers는 3,424다.
이제 중간 구간인 M00005부터 M00015까지를 제외해 보자.
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(t1 tr_ord_big_x31) */ t1.*, COUNT(*) OVER() FROM startdbora.tr_ord_big t1 WHERE t1.mbr_id >= 'M00001' AND t1.mbr_id <= 'M00020' AND NOT(t1.mbr_id BETWEEN 'M00005' AND 'M00015');
SQL
복사
-------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.01 | 1751 | | | | | 1 | WINDOW BUFFER | | 1 | 312 | 200 |00:00:00.01 | 1751 | 232K| 232K| 206K (0)| | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TR_ORD_BIG | 1 | 312 | 1762 |00:00:00.01 | 1751 | | | | |* 3 | INDEX RANGE SCAN | TR_ORD_BIG_X31 | 1 | 312 | 1762 |00:00:00.01 | 11 | | | | -------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."MBR_ID">='M00001' AND "T1"."MBR_ID"<='M00020') filter(("T1"."MBR_ID">'M00015' OR "T1"."MBR_ID"<'M00005'))
Plain Text
복사
범위를 정하는 access 조건은 그대로다. 추가한 부정 조건은 MBR_ID > 'M00015' OR MBR_ID < 'M00005'로 나타나며, 3번 인덱스 오퍼레이션의 filter에 붙어 있다.
인덱스에서 범위를 읽으면서 중간 구간의 1,682건을 걸러내고, 통과한 1,762건의 ROWID만 테이블 접근 단계로 전달한다. 실제로 인덱스의 A-Rows가 3,444에서 1,762로 줄었다.
인덱스 Buffers는 11로 동일하다. 읽는 인덱스 범위를 줄인 것이 아니라, 테이블까지 가져갈 행을 인덱스에서 먼저 줄인 것이다. 그 결과 전체 Buffers는 3,424에서 1,751로 감소했다.
즉, Oracle에서는 추가한 NOT 조건을 인덱스 리프에서 먼저 처리한 후 테이블에 접근한 것이다.

PostgreSQL에서는 테이블 접근이 그대로다

PostgreSQL에서도 같은 회원 범위를 조회한다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF) SELECT /*+ IndexScan(t1) */ t1.* FROM startdbpg.tr_ord_big t1 WHERE t1.mbr_id >= 'M00001' AND t1.mbr_id <= 'M00020';
SQL
복사
Index Scan using tr_ord_big_x31 on tr_ord_big t1 (actual time=0.014..3.395 rows=3444 loops=1) Index Cond: (((mbr_id)::text >= 'M00001'::text) AND ((mbr_id)::text <= 'M00020'::text)) Buffers: shared hit=3428 Planning Time: 0.188 ms Execution Time: 3.685 ms
Plain Text
복사
회원 범위 조건이 Index Cond에 표시되어 있다. 테이블에서 3,444건을 가져왔고, Buffers는 3,428이다.
Oracle과 동일하게 중간 구간을 제외하는 조건을 추가한다.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF) SELECT /*+ IndexScan(t1) */ t1.* FROM startdbpg.tr_ord_big t1 WHERE t1.mbr_id >= 'M00001' AND t1.mbr_id <= 'M00020' AND NOT(t1.mbr_id BETWEEN 'M00005' AND 'M00015');
SQL
복사
Index Scan using tr_ord_big_x31 on tr_ord_big t1 (actual time=0.018..3.567 rows=1762 loops=1) Index Cond: (((mbr_id)::text >= 'M00001'::text) AND ((mbr_id)::text <= 'M00020'::text)) Filter: (((mbr_id)::text < 'M00005'::text) OR ((mbr_id)::text > 'M00015'::text)) Rows Removed by Filter: 1682 Buffers: shared hit=3428 Planning Time: 0.278 ms Execution Time: 3.733 ms
Plain Text
복사
반환한 행은 1,762건으로 줄었지만, Buffers는 3,428로 그대로다.
추가한 제외 조건이 Index Cond가 아닌 Filter에 남았기 때문이다. Index Scan에 표시된 Filter는 테이블에서 처리한 조건이다. 즉, 회원 ID가 인덱스에도 존재함에도 불구하고 NOT 조건을 테이블에서 처리한 것이다. 테이블에서 가져와 Filter를 검사한 행 수는 다음과 같다.
•
1,762(actual rows) + 1,682(Rows Removed by Filter) = 3,444건
Oracle은 제외 대상 구간의 1,682건을 테이블 접근 전에 걸러냈다. 반면 PostgreSQL은 테이블에서 가져온 다음 제외했다.

인덱스 컬럼의 조건인데, 왜 테이블까지 읽을까?

제외 조건을 검사하는 데 필요한 값은 회원ID 하나뿐이다. 이미 인덱스에 있는 값이다. 인덱스에서 회원ID를 확인했을 때 제외 대상이라면, 테이블에는 가지 않아도 된다. 실제로 앞의 Oracle 실행계획은 그렇게 처리했다.
그런데 PostgreSQL은 인덱스에서 판정할 수 있는 회원ID 제외 조건을 테이블에서 가져온 행에 적용했다. 이것은 PostgreSQL의 Index Scan 구현 방식 때문이다.
PostgreSQL의 Index Scan은 Index Cond로 전달된 조건을 인덱스 내부에서 검사하고, Filter로 남은 조건은 테이블 행을 가져온 뒤 검사하도록 구현되어 있다. Index Scan의 Filter로 남으면, 인덱스 값만으로 검사할 수 있더라도 테이블 행을 가져온 뒤 검사한다.
그렇다면 어떤 조건들이 주로 Filter로 남을까? PostgreSQL에서 원래 컬럼값을 변형하지 않은 B-tree 인덱스를 사용할 때, 다음과 같은 조건들이 대표적이다.
•
같지 않다 조건: mbr_id <> 'M00065'.
•
제외 조건: NOT(mbr_id BETWEEN 'M00005' AND 'M00015')
•
컬럼에 함수를 적용한 조건: TRIM(mbr_id) = 'M00022'
•
중간·끝 문자열을 찾는 조건: mbr_id LIKE '%0022%', mbr_id LIKE '%0022'
모두 인덱스에 회원ID만 있으면 검사할 수 있는 조건들이다. 하지만 PostgreSQL의 Index Scan에서 이 조건들이 Filter로 남으면, 테이블 행을 가져온 뒤 검사한다. 여기서 설명하는 것은 PostgreSQL의 Index Scan 처리 방식이며, 모든 DBMS가 이러한 조건을 테이블 접근 후에 검사한다는 뜻은 아니다.
PostgreSQL에서는 인덱스 컬럼에 부정 조건을 추가하면 테이블 접근이 줄지 않을 수 있다. Index Scan의 Filter로 남은 조건은 테이블 행을 가져온 뒤 검사한다는 사실을 기억하자.
참고로 이러한 처리 순서는 PostgreSQL 내부 구현에 따른 동작이다. 관련 코드는 테이블 행을 가져오는 처리와 가져온 행에 Filter를 평가하는 처리에서 볼 수 있다.

마무리

이번 실험에서 Oracle과 PostgreSQL은 같은 범위에서 같은 회원 구간을 제외했다. 하지만 Oracle은 인덱스에서 먼저 걸러 테이블 접근을 줄였고, PostgreSQL은 테이블에서 가져온 뒤 버렸다. 그 차이로 부정 조건 추가 후 PostgreSQL의 전체 Buffers는 Oracle의 약 두 배가 되었다.
부정 조건으로 많은 행을 제외하더라도, Index Scan의 Filter로 남으면 그 행을 가져오는 비용까지 없애 주지는 않는다. Oracle에서 효과적으로 작동하던 조건을 PostgreSQL에서도 같은 방식으로 처리할 것이라고 예상하지 말자.

참고