PostgreSQL의 테이블 스캔에는 크게 네종류가 있으며 MySQL과는 다르게 동작하는 scan방식이 있습니다.

Sequential Scan
Index Scan
Index Only Scan
Bitmap Scan

이 중 MySQL의 full scan, index range scan과 동일한 Sequential Scan,Index Scan은 건너뛰고
Index Only Scan에 대해서 알아보겠습니다.
Bitmap Scan은 간단히 언급만 하자면, index scan하거나 full scan하기엔 많은 데이터를 bitmap이라는 자료구조를 통해 스캔하는 방법입니다.

Index Only Scan

Index Only Scan은 흔히 말하는 covering index와 동일한 개념입니다.
SELECT 절과 WHERE절에서 사용하는 컬럼과 조건이 모두 index를 사용할 수 있어서 테이블(heap)에 접근할 필요없이 index에서 필요한 모든 데이터를 가져올 수 있을 때
이 인덱스는 이 쿼리의 covering index 라고 합니다.
covering index는 테이블에 접근하지 않기 때문에 성능이 굉장히 좋은 것이 일반적이지만
PostgreSQL에서는 한가지 더 고려해야하는 점이 있는데 그것은 PostgreSQL의 MVCC 구현방식에 따른 visibility map 입니다.
아래에서 테스트와 데이터를 통해 자세히 살펴보겠습니다.

테스트 환경 세팅

testdb=> create extension pg_visibility;
CREATE EXTENSION

testdb=> create table tb_test (id serial, a varchar(100), b int);
create index on tb_test(a,b);
CREATE TABLE
CREATE INDEX
testdb=> insert into tb_test(a,b) select 'test' || generate_series(1,1000000),
(random()*100)::int % 100;
INSERT 0 1000000

testdb=> vacuum tb_test;
VACUUM
testdb=> vacuum analyze tb_test;
VACUUM

testdb=> set enable_seqscan = off;
SET
testdb=> set enable_bitmapscan = off;
SET

실행계획 확인

testdb=> explain analyze select a from tb_test where b between 1 and  10;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using tb_test_a_b_idx on tb_test  (cost=0.42..37421.76 rows=98933 width=10) (actual time=29.327..294.907 rows=99957 loops=1)
   Index Cond: ((b >= 1) AND (b <= 10))
   Heap Fetches: 0
 Planning Time: 0.081 ms
 Execution Time: 301.603 ms
(5 rows)

위에서 실행계획을 봤을 땐 Index Only Scan plan을 제대로 타고 있고 별 문제 없어보입니다.
다만 Heap Fetches: 0 이라는 라인을 기억해두었다가 다시 한번 쿼리를 수행해보겠습니다.
아래와 같이 업데이트가 발생하고 어떤 무언가의 이슈로 vacuum이 돌지 않았다고 가정해보겠습니다.

dead tuple 발생 후 실행계획 확인

testdb=> alter table tb_test set (autovacuum_enabled = off);
ALTER TABLE

testdb=> update tb_test set b = b + 1;
UPDATE 1000000
testdb=> explain analyze select a from tb_test where b between 1 and  10;
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using tb_test_a_b_idx on tb_test  (cost=0.43..67404.75 rows=197851 width=10) (actual time=0.095..336.188 rows=99921 loops=1)
   Index Cond: ((b >= 1) AND (b <= 10))
   Heap Fetches: 199878
 Planning Time: 0.073 ms
 Execution Time: 340.998 ms
(5 rows)

동일한 Index Only Scan의 플랜이지만
무언가 위는 Heap Fetches: 0인데 아래는 Heap Fetches 양이 엄청 늘었습니다.
여기서 Heap 은 PostgreSQL에서 테이블의 데이터 저장구조라고 보시면 됩니다.
즉 테이블을 의미하므로 Heap Fetches가 늘어났다는 것은 covering index를 이용했음에도 테이블에 접근, 곧 I/O가 발생했다는 의미입니다.

Visibility Map

이 현상을 이해하기 위해서는 Visibility Map을 이해해야하는데 간단히 말하면,
각 테이블의 데이터를 갖고 있고 테이블을 구성하는 page에 변경사항이 있는지 없는지를 체크해둔 메타데이터라고 생각하면 됩니다.

이것은 vacuum 의 성능을 개선하기 위해 나온 개념인데, Visibilit Map이 없었던 시절에는 vacuum을 할 때 모든 테이블을 풀스캔을 했습니다.
근데 Visibility Map이 나오면서, 데이터가 변경된 페이지, 변경되지 않은 페이지를 구분할 수 있게 되고, vacuum은 데이터가 변경된 페이지만 골라다 스캔을 할 수 있게 된 것입니다.

Visibility Map은 2bit로 구성됩니다.

VISIBILITYMAP_ALL_VISIBLE : 페이지에 저장된 tuple들이 모든 트랜잭션에게 보이는지 안보이는지에 대한 여부를 의미합니다.
- 1 : 해당 페이지의 모든 tuple들이 모든 트랜잭션에게 보이는 상태, dead tuple이 없음
- 0 : 해당 페이지에 dead tuple이 있거나, vacuum이 수행되지 않아 아직 모르는 상태임
VISIBILITYMAP_ALL_FROZEN : 페이지에 저장된 tuple들이 freeze 되었는지에 대한 여부를 의미합니다.
- 1 : 해당 페이지의 모든 tuple들이 freeze되어 vacuum freeze가 발생했을 때 해당 페이지는 스킵함
- 0 : 해당 페이지에 dead tuple이 있꺼나, vacuum이 수행되지 않아 아직 모르는 상태임
Visibility Map 변화 확인

### 위에서 update 후 dead tuple이 있는 상태 

testdb=> select blkno, all_visible, all_frozen from pg_visibility('tb_test') limit 10;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | f           | f
     1 | f           | f
     2 | f           | f
     3 | f           | f
     4 | f           | f
     5 | f           | f
     6 | f           | f
     7 | f           | f
     8 | f           | f
     9 | f           | f
(10 rows)

### vacuum 후 정리된 상태
testdb=> vacuum freeze tb_test;
VACUUM
testdb=> select blkno, all_visible, all_frozen from pg_visibility('tb_test') limit 10;
 blkno | all_visible | all_frozen
-------+-------------+------------
     0 | t           | t
     1 | t           | t
     2 | t           | t
     3 | t           | t
     4 | t           | t
     5 | t           | t
     6 | t           | t
     7 | t           | t
     8 | t           | t
     9 | t           | t
(10 rows)

정리

정리하면, VISIBILITYMAP_ALL_VISIBLE = 0 인 상태, 데이터 페이지에 변경사항이 있다면 인덱스만 읽어서 될 게 아니라 테이블에 접근하여 해당 로우의 버전을 확인해야합니다.

나의 트랜잭션 아이디와 해당 로우의 버전을 비교하여 읽을 수 있는 데이터인지 읽을 수 없는 데이터인지를 확인해야 하기 때문입니다.

이것은 covering index인 Index Only Scan에서도 마찬가지라서 covering index임에도 불구하고 테이블 i/o가 발생할 수 있습니다.

index plan은 이슈없는데 테이블 스캔이 느리면 vacuum을 확인해보자!

Index Only Scan#

Visibility Map#

정리#

Index Only Scan

Visibility Map

정리