[postgresql] postgresql의 Index Only Scan

Postgresql은 Index Only Scan을 지원한다. Index Only Scan은 index에 필요로 하는 데이터가 있을때 인덱스 영역만 스캔하는 방식이다.

Index Only Scan

Index Only Scan을 테스트 해보기 위해서 테이블에 100만개 정도의 더미 데이터를 넣고 테스트 해보았다.

create table product
(
    id          bigint generated by default as identity primary key,
    created_at  timestamp with time zone not null,
    updated_at  timestamp with time zone not null,
    name        varchar(60)              not null,
    price       bigint                   not null,
    description varchar(200)             not null,
    status      smallint                 not null
);

select count(*) from product;	-- 1000000

pk값인 id는 기본으로 인덱스가 설정되어있으므로 조회하는 값과 조건이 id이면 Index Only Scan이 된다.

explain analyze select id from product where id > 5000 and id < 8000;	-- Index Only Scan

만약, 조회해야 하는 값에 인덱스 영역에 없는 데이터가 포함되어있다면 Index Scan을 하게 된다.

explain analyze select id, price from product where id > 5000 and id < 8000; -- Index Scan

 

Index Only Scan과 Index Scan의 차이점은 뭘까?

• Index Only Scan: 인덱스 영역만 스캔한다.
• Index Scan: 인덱스 영역을 스캔한 뒤, 그에 해당하는 힙 영역을 스캔한다.

힙 영역은 인덱스 영역처럼 인덱스들끼리 정렬되어있는것이 아니라 데이터 블록이 무작위로 위치하기 때문에, 실제 데이터가 있는 힙 영역을 스캔하게 되면 많은 비용이 들게 된다.

 

Index Only Scan 주의 사항

select id from product;

where 조건을 걸지 않고, select id from product 만 하는 경우 Seq Scan이 된다.

id만 조회하는건데 왜 이렇게 되는지 궁금해서 구글링 해보았더니  https://stackoverflow.com/questions/5203755/why-does-postgresql-perform-sequential-scan-on-indexed-column 여기서 답을 얻을 수 있었다.

인덱스를 걸더라도 전체 행의 5~10% 이상 조회해야하는 경우 Seq Scan이 더 빠르기 때문이다. 그게 id만 조회하는 상황이라도 힙 영역만 읽는게 더 빠르다고 판단하여 Seq Scan이 된다. 

 

일반적으로 Table Full Scan을 하는 경우 시퀀셜 액세스 + MultiBlock I/O 방식으로 한 번에 여러개의 블록을 가져오고, Index Scan을 하는 경우에는 랜덤 액세스 + Single Block I/O 방식으로 하나씩 블록을 가져온다. postgresql에서도 비슷한 방식으로 동작할 것이므로 Seq Scan으로 데이터를 가져오는게 더 빠를것이다.

 

인덱스라고 무조건 Index Only Scan을 사용할 수 있는건 아니다.

결과값이 Visiblity Map의 ALL_VISIBLE값이 True인 페이지에 존재해야한다.

Visiblity Map은 힙 영역에 존재하지만 자주 변경되는 데이터가 아닌 경우, 메모리에 캐시된 Visibility Map을 사용하므로 메모리에 캐시된 Visiblity Map을 참조할 수 없는 경우 힙 영역도 스캔하게 된다.

Index Only Scan를 사용해서 쿼리 개선하기

사실  Index Only Scan, Covering Index 를 검색하면 많이 나오는 성능 개선 사례가 페이지네이션이다. offset, limit을 사용하게 되면 offset만큼 힙 영역(데이터 영역)을 스캔하게 되는데, Index Only Scan을 사용하면 인덱스 영역에서면 offset 만큼 스캔하고, 그 이후는 인덱스 스캔을 하게 된다. 그렇게 하면 상대적으로 적은 페이지를 스캔하기 때문에 성능을 향상시킬 수 있다.

-- OFFSET 20000 이후의 데이터를 찾기 위해 20010개의 row를 데이터 영역에서 스캔
select * from product order by id desc offset 20000 limit 10;

-- Index Only Scan 으로 성능 개선
explain analyze select * from product join (select id from product order by id desc offset 20000 limit 10) as p on p.id = product.id;

참고

https://junhkang.tistory.com/70

[PostgreSQL] Index-Only 스캔과 Covering 인덱스, Index-only스캔의 효율적인 사용

https://jojoldu.tistory.com/529

2. 페이징 성능 개선하기 - 커버링 인덱스 사용하기

https://www.postgresql.org/docs/16/indexes-index-only-scans.html

11.9. Index-Only Scans and Covering Indexes

https://www.yes24.com/Product/Goods/61254539

친절한 SQL 튜닝 - 예스24