※ Index in PostgreSQL.
안녕하세요. 듀스트림입니다.
설 연휴네요.
그래서 그냥 쉬면서 인덱스에 대한 내용을 정리해봤습니다.
1. 인덱스?
인덱스는 데이터베이스 관리 시스템(DBMS)에서 데이터를 더 빠르고 효율적으로 검색하기 위해 사용되는 데이터 구조입니다.
책의 목차처럼, 데이터가 저장된 위치를 미리 정리해 두어 원하는 데이터를 쉽게 찾을 수 있도록 도와줍니다.
2. 인덱스의 특징
• 빠른 데이터 검색: 테이블 전체를 검색(Full Table Scan)하는 대신, 인덱스를 사용하여 검색 시간을 단축합니다.
• 데이터 정렬: 인덱스는 특정 컬럼을 기준으로 데이터를 정렬된 상태로 유지합니다.
• 추가 저장 공간 필요: 인덱스를 저장하기 위한 추가적인 디스크 공간이 필요합니다.
• 삽입/삭제/수정 시 오버헤드 발생: 데이터를 수정할 때 인덱스도 갱신해야 하므로 성능에 영향을 줄 수 있습니다.
3. PostgreSQL 인덱스의 종류
• B-Tree: 균형 트리 구조. 범위 검색과 동등 조건 모두 잘 처리.
• Hash: = (equal) 연산 최적화. WAL 지원은 10 버전부터 안정화.
• GIN: 전체 텍스트 검색, JSONB, 배열 요소 검색에 최적화(역인덱스 구조).
• GiST: 지리 정보, 범위 연산 등 다양한 사용자 정의 자료형 지원.
• BRIN: 블록 단위 최소/최대 값을 관리, 대용량 테이블에서 범위 검색 시 효율적.
• SP-GiST: 공간 분할 트리 구조, 위치/좌표 기반 데이터에 특화.
• Bloom: 여러 컬럼의 부분 해시를 결합, 메모리 사용을 절약하며 특정 검색 패턴에 사용.
3.1. B-Tree 인덱스 동작과 내부 구조
• 주요 특징: PostgreSQL의 기본 인덱스 타입으로, 균형 트리(Balanced Tree) 구조를 기반으로 동작하며, 범위 검색 및 동등 비교 모두에 적합합니다.
• 트리 구조: 루트 노드, 내부 노드, 리프 노드로 구성되며, 항상 균형 상태를 유지하여 검색 성능이 일정합니다.
• 사용 용도: 대부분의 일반적인 쿼리에서 사용되며, 데이터 정렬, 동등 조건(=), 범위 조건(>, <, BETWEEN) 검색에 적합합니다.
• 내부 구조
- 루트 노드
→ 인덱스의 진입점 역할을 하며, 내부 노드로의 탐색 경로를 제공합니다.
→ 각 자식 노드의 최소값/최대값을 저장하여 적합한 내부 노드로의 경로를 결정합니다. - 내부 노드
→ 트리의 탐색 경로를 형성하는 노드로, 자식 노드에 포함된 키 값의 범위를 저장합니다.
→ 효율적인 탐색을 위해 각 노드에는 부모와 자식 간의 경로가 연결되어 있습니다. - 리프 노드
→ 실제 데이터의 인덱스 키와 테이블의 튜플 위치를 가리키는 TID(Tuple Identifier)를 저장합니다.
→ 모든 리프 노드는 왼쪽에서 오른쪽으로 정렬된 상태로 연결(Linked List)되어 있어, 순차 검색이 가능합니다.
• 동작 원리
- 페이지 분할(Page Split)
→ 인덱스 페이지가 꽉 찼을 경우, 새로운 페이지를 할당하여 데이터를 분할합니다.
→ 분할된 페이지는 부모 노드에 새로운 경로로 추가됩니다.
→ 잦은 페이지 분할은 단편화(bloat)를 유발할 수 있으므로 fillfactor를 조정하여 예방할 수 있습니다. - Index-Only Scan
→ PostgreSQL 9.2부터 지원되는 기능으로, 쿼리 결과에 필요한 데이터가 인덱스에 모두 포함되어 있다면 테이블 접근 없이 인덱스만으로 결과를 반환합니다.
→ 테이블 액세스(I/O)를 줄이므로 성능 최적화에 매우 유용합니다. - HOT Update(Heap-Only Tuple)와의 연관
→ 데이터 수정 시, 인덱스 키 값이 변경되지 않는 경우 테이블 페이지 내에서만 수정 작업이 이루어지며, 인덱스는 수정되지 않습니다.
→ HOT 업데이트 가능성을 높이기 위해 불필요한 인덱스를 줄이고, 자주 변경되는 컬럼에 인덱스를 생성하지 않는 것이 중요합니다.
• 장단점
- 장점:
→ 범위 검색, 동등 비교 등 일반적인 검색에 모두 적합.
→ 정렬된 트리 구조로 인해 일정한 검색 성능 유지.
→ Index-Only Scan을 통한 추가적인 성능 최적화 가능. - 단점:
→ 빈번한 페이지 분할은 단편화를 유발하고, 성능 저하를 초래할 수 있음.
→ 쓰기 작업(INSERT, UPDATE, DELETE)이 많을 경우, 추가적인 유지보수(VACUUM, REINDEX 등)가 필요.
• 활용 시나리오
→ 일반적인 동등 조건 및 범위 검색 쿼리
예) SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
→ 데이터 정렬이 필요한 쿼리
예) SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
→ Index-Only Scan이 가능한 쿼리
예) SELECT customer_id FROM orders WHERE order_date > '2023-01-01';
(결과에 필요한 데이터가 인덱스에 모두 포함된 경우)
3.2. Hash 인덱스의 동작과 내부 구조
• 주요 특징: 동등 비교(=) 연산을 빠르게 처리하기 위해 해시 함수를 사용.
• 내부 구조
- 해시 버킷(Hash Bucket)
→ 해시 함수를 통해 키를 버킷에 매핑.
→ 동일 해시 값(콜리전) 처리 시 체인 구조를 갖기도 함. - 메타 페이지(Meta Page)
→ 전체 해시 테이블 구조에 대한 정보(버킷 개수, 확장 주기 등)를 저장. - 페이지 확장(Page Split)과 재해시
→ 키 분포에 따라 해시 버킷이 일정 수준 이상 차면, 해시 테이블(버킷 수) 확장 필요.
• 장단점
- 장점:
→ 동등 비교에 최적화되어 있어, = 조건으로 자주 조회할 때 매우 빠른 검색 가능. - 단점:
→ 범위 검색(>, <, BETWEEN 등)에 사용 불가능.
→ 10 이전 버전에서는 WAL 미지원으로 인덱스 손상 시 재생성 필요했음(현재는 해결).
• 활용 시나리오
→ 간단한 키-값 조회 패턴이 매우 빈번한 테이블에서 효과적.
예) 사용자 토큰, ID 매핑 등 동등 조회가 대부분인 환경.
※ 실제 운영 환경에서는 B-Tree 인덱스를 주로 사용하며, Hash 인덱스 사용이 생각만큼 많지 않습니다.
3.3. GIN 인덱스의 동작과 내부 구조
• 주요 특징: 전체 텍스트 검색, JSONB, 배열 등의 “여러 요소”에 대해 역인덱스(Inverted Index)를 구축.
• 역인덱스 구조: 단어(토큰), 배열 요소, JSON 키-값 등 “토큰화”된 요소가 어떤 문서(TID)에 포함되어 있는지 매핑.
• 내부 구조
- Posting Tree / Posting List
→ 토큰(키)에 대한 리스트(Posting List)가 각 TID 집합을 가리킴. - Fast Update 영역
→ 쓰기 부담 완화를 위해, 수정 사항을 바로 트리에 반영하지 않고 별도의 “Pending 리스트”에 임시 저장하는 구조를 가짐.
• 장단점
- 장점:
→ 복합 요소(배열, 텍스트, JSON) 검색에 최적화.
→ “하나의 토큰”이 여러 행에 분산되어 있어도 빠르게 역으로 찾을 수 있음. - 단점:
→ 인덱스 생성/업데이트 비용이 높음. 대량 쓰기가 빈번한 환경에서는 부하가 커질 수 있음.
→ 단순 숫자 비교나 범위 검색에는 적합하지 않음(주로 텍스트, 배열, JSONB).
• 활용 시나리오
→ Full Text Search(자연어 검색), jsonb @> 연산, 배열 멤버 검색 등.
3.4. GiST 인덱스의 동작과 내부 구조
• 주요 특징: 범위 연산, 위치/좌표 연산 등 사용자 정의 타입의 검색 로직을 유연하게 지원(“Generalized Search Tree”).
• 사용자 정의: 지리 정보(PostGIS), IP 주소 범위, 문자열 유사도 등 다양한 연산자 클래스를 만들 수 있음.
• 내부 구조
- 트리 구조
→ B-Tree와 유사한 노드 구조를 가지나, “키” 개념이 단일 값이 아닌 “범위” 또는 “영역”이 될 수 있음. - Consistent / Union 함수
→ GiST 확장(Operator Class)을 작성하면, “이 노드가 검색 조건에 부합하는가?”를 판단하는 함수를 구현해 넣을 수 있음.
• 장단점
- 장점:
→ 매우 다양한 검색 유형을 지원.
→ 범위, 지역, 근접성(Nearest Neighbor) 검색 등에 활용. - 단점:
→ B-Tree처럼 일반적이지 않으므로, Operator Class 구현 시 주의가 필요.
→ 쓰기 부하나 업데이트에 따른 비용이 다소 클 수 있음.
• 활용 시나리오
→ PostGIS(공간 데이터), Range 타입(int4range, tsrange 등), 거리 기반 검색 등.
3.5. BRIN 인덱스의 동작과 내부 구조
• 주요 특징: 블록 범위 단위로 최소/최대 값을 관리하는 인덱스(“Block Range Index”).
• 저장 공간: B-Tree 대비 매우 작음. 대규모 테이블에서 범위 검색에 탁월.
• 내부 구조
- Range Summaries
→ 일정한 블록 범위(기본 128페이지 등)에 대해, 해당 범위의 최소/최대값 등의 요약정보를 저장. - 특화된 연산자 클래스
→ 정렬이 잘 된 칼럼(날짜, 시퀀스 번호 등)일수록 효과가 커짐.
• 장단점
- 장점:
→ 매우 작은 인덱스 크기.
→ 대량의 연속적(혹은 거의 정렬)인 데이터가 있을 때 범위 스캔이 빠름. - 단점:
→ 데이터가 무작위로 삽입되면 효율이 크게 떨어질 수 있음.
→ = 연산의 정확도나 세밀한 범위 검색에 B-Tree만큼 정밀하지 않을 수 있음.
• 활용 시나리오
→ 로그성 데이터, 시계열(시간 순으로 들어오는 데이터) 등에서 특정 기간/범위 검색을 수행할 때.
3.6. SP-GiST 인덱스의 동작과 내부 구조
• 주요 특징: 공간 분할 트리(Space-Partitioned GiST) 구조. 좌표/위치 정보, 트라이(Trie) 기반 문자열 검색 등에 사용.
• GiST와 차이점: GiST는 “범위” 기반 분할, SP-GiST는 “공간 분할” 로직을 활용하여 더 세분화된 인덱싱이 가능.
• 내부 구조
- 공간 분할 트리
→ 데이터를 여러 분할 영역으로 재귀적으로 나누어 저장(예: 쿼드 트리, K-d 트리 등). - Operator Class
→ SP-GiST를 사용할 자료형(좌표, 텍스트 등)에 맞춰 분할 규칙을 정의.
• 장단점
- 장점:
→ 클러스터링이 잘 되어 있지 않은 2D/3D 좌표 데이터, 불균일 분포를 잘 처리.
→ 특정 유형(문자열 패턴, 공간 데이터)에서 GiST보다 효율이 좋은 경우도 있음. - 단점:
→ GiST, B-Tree 등에 비해 일반적이지 않아 적용 범위가 제한적.
→ 구현이 복잡, 연산자 클래스가 필요.
• 활용 시나리오
→ 공간 정보(예: GIS, 위치 기반 서비스), 트라이 구조(문자열 전처리), 고차원 분포 데이터 검색 등.
3.7. Bloom 인덱스의 동작과 내부 구조
• 주요 특징: 여러 컬럼의 부분 해시(bit signature)를 결합하여, 메모리를 절약하고 빠른 검색을 가능케 하는 구조.
• Bloom 필터의 원리를 PostgreSQL 인덱스에 적용한 형태.
• 내부 구조
- Signature Page
→ 다중 컬럼을 해시화하여 비트 시그니처로 저장.
→ 조회 시, 시그니처가 일치할 가능성이 있는 튜플만 빠르게 후보로 추림. - 거짓 양성(False Positive)
→ Bloom 필터 특성상 거짓 양성 가능성이 존재. 실제 튜플을 다시 확인해 최종 필터링.
• 장단점
- 장점:
→ 여러 컬럼을 동시에 인덱스해야 할 때, B-Tree 복합 인덱스보다 공간 절약.
→ 대체로 빠른 검출 가능. - 단점:
→ 부분적으로 부정확할 수 있어 후보 튜플 재확인을 수행해야 함.
→ PostgreSQL 환경에서 일반적인 인덱스만큼 널리 쓰이진 않음.
• 활용 시나리오
→ 다중 컬럼에 대한 선택도가 낮으나, 일부 빠른 필터링이 필요한 경우.
→ 대용량 테이블에서 “가능성 있는 후보”를 먼저 걸러내야 할 때.
※ PostgreSQL 확장 중 하나로, 기본 설치에는 포함돼 있지만 기본적으로 로드되지 않은 "contrib 모듈"에 속합니다.
→ 사용하려면 CREATE EXTENSION bloom; 과 같은 작업이 필요합니다.
4. MVCC 관점에서의 인덱스
PostgreSQL은 읽기 작업과 쓰기 작업이 충돌하지 않도록 설계되었으며, 이를 위해 인덱스 관리 시에도 MVCC 메커니즘을 사용합니다.
• 인덱스는 Heap(테이블)의 MVCC 정보를 참조하며, HOT Update나 Index-Only Scan 등에서 MVCC 동작을 고려하기 위한 최소한의 메타정보를 관리
→ PostgreSQL에서는 인덱스가 “TID(튜플 식별자)”만을 주로 저장하고, 실제 MVCC 판별(해당 행이 현재 트랜잭션 시점에 보이는지 여부)은 Heap(테이블) 쪽에서 이루어집니다.
→ 각 인덱스 엔트리에는 해당 행의 MVCC 정보를 참조할 수 있도록 하는 메타데이터(Visibility Map 등)이 포함됩니다.
→ 이 메타데이터는 xmin(생성 트랜잭션)과 xmax(삭제 트랜잭션) 정보와 연결되어 트랜잭션의 유효성을 결정합니다.
→ “Index-Only Scan” 을 위해 Visibility Map을 활용하거나, GIN/BRIN 등에서는 특정 메타정보(예: posting list 등)와 함께 부분적인 버전 정보를 가지고 있을 수 있습니다.
• 트랜잭션 격리 수준 관리
→ 특정 트랜잭션에서 인덱스를 조회하거나 수정할 때, 다른 트랜잭션의 영향을 받지 않도록 보장합니다.
• 투명한 버전 관리
→ 읽기 트랜잭션은 자신이 유효한 MVCC 스냅샷만 참조하며, 쓰기 트랜잭션은 새로운 버전을 작성합니다.
→ 인덱스도 동일한 방식으로 동작합니다.
4.1. B-Tree 인덱스
• PostgreSQL의 기본 인덱스 유형인 B-Tree는 MVCC를 지원하기 위해 각 인덱스 엔트리에 MVCC 정보를 저장합니다.
• MVCC 정보가 포함되어 있으므로, 트랜잭션 격리 수준에 따라 특정 트랜잭션이 유효한 데이터를 참조하도록 보장합니다.
• 동일한 키 값이 다른 트랜잭션에 의해 변경되더라도 기존 데이터를 유지하여 읽기 작업이 차단되지 않도록 합니다.(다중 버전 엔트리 관리)
4.2. BRIN(Block Range INdex) 인덱스
• 대용량 테이블에서 범위 기반 검색을 최적화하기 위해 설계된 BRIN은 MVCC를 고려하여 동작하지만, 인덱스 자체에 상세한 MVCC 정보를 저장하지는 않습니다.
• 범위 메타데이터와 테이블의 MVCC 정보를 함께 사용하여 적절한 데이터 접근을 보장합니다.
4.3. GIN(Generalized Inverted Index) 인덱스
다중 값 데이터(예: JSON, 배열, 텍스트 검색 등)에 적합한 인덱스로, MVCC 메커니즘을 통해 쓰기 작업 중에도 읽기 작업이 차단되지 않도록 관리됩니다.
• MVCC 메타데이터
→ GIN 인덱스는 업데이트 시 새로운 트랜잭션 버전에 대한 정보를 저장합니다.
→ 인덱스 엔트리는 데이터가 변경될 때마다 새로운 MVCC 버전을 생성하며, 이전 버전은 여전히 읽기 트랜잭션에서 접근 가능합니다.
• 동시성 처리
→ GIN 인덱스는 "fast update" 메커니즘을 사용하여, 변경된 내용을 인덱스의 Pending list에 임시 저장한 뒤, 적절한 시점에 인덱스를 정리합니다.
→ pending list는 읽기 작업에서 사용되지 않으며, 동시성 유지에 기여합니다.
5. 파티셔닝(Partitioning)과 인덱스
파티션별 인덱스를 관리할 수 있으나, "글로벌 인덱스" 기능은 아직 제공되지 않습니다.
• 파티션별 인덱스
→ 각 파티션 테이블에 개별 인덱스를 생성해야 함.
→ 파티션 키를 자주 사용하는 쿼리에 매우 효율적.
→ 테이블 및 인덱스 증분 유지보수
파티션별 Reindex, Vacuum, Analyze 등을 개별로 수행해 운영 부담을 줄일 수 있음.
• 주의할 점
→ 파티셔닝이 복잡해질수록 인덱스 개수도 늘어나므로 설계를 신중히 해야 함.
→ 필요 이상으로 쪼개면 오히려 오버헤드가 커짐.
6. 운영팁
• pg_stat_statements, auto_explain 모듈 활용해 쿼리와 인덱스 사용량을 추적.
• 정기적인 VACUUM 및 ANALYZE로 통계 정보를 최신화, 인덱스 bloat도 최소화.
• 쓰기 작업이 많은 테이블에 불필요한 인덱스가 없는지 주기적으로 점검.
7. 테스트 예제
7.1. 테이블 생성 및 데이터 삽입
-- 1. 테스트 데이터베이스
CREATE DATABASE test_index;
\c test_index
-- 2. 테이블 생성
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
amount NUMERIC(10,2),
status VARCHAR(20),
metadata JSONB DEFAULT '{}'
);
-- 3. 대량 데이터 삽입(100만 건)
INSERT INTO orders (customer_id, order_date, amount, status, metadata)
SELECT
(random() * 1000000)::BIGINT,
CURRENT_DATE - ((random() * 365)::INT),
(random() * 10000)::NUMERIC(10,2),
CASE WHEN random() < 0.5 THEN 'NEW' ELSE 'COMPLETE' END,
jsonb_build_object(
'category', CASE WHEN random() < 0.5 THEN 'A' ELSE 'B' END,
'priority', (random() * 10)::INT
)
FROM generate_series(1, 1000000);
-- 4. 실행계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;
--- QUERY PLAN ---
Gather (cost=1000.00..18843.46 rows=2 width=80) (actual time=16.179..18.333 rows=0 loops=1)
Workers Planned: 3
Workers Launched: 3
Buffers: shared hit=13811
-> Parallel Seq Scan on orders (cost=0.00..17843.26 rows=1 width=80) (actual time=14.089..14.089 rows=0 loops=4)
Filter: (customer_id = 50000)
Rows Removed by Filter: 250000
Buffers: shared hit=13811
Planning Time: 0.056 ms
Execution Time: 18.353 ms
(10 rows)7.2. B-TREE 인덱스 테스트
▸ 단일 컬럼 인덱스
-- 1. 단일 컬럼 인덱스 생성
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- 2. 실행계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000;
--- QUERY PLAN ---
Index Scan using idx_orders_customer_id on orders (cost=0.42..3.76 rows=2 width=80) (actual time=0.035..0.035 rows=0 loops=1)
Index Cond: (customer_id = 50000)
Buffers: shared hit=3 read=3
Planning:
Buffers: shared hit=16 read=1
Planning Time: 0.207 ms
Execution Time: 0.051 ms
(7 rows)
▸ 복합 컬럼 인덱스
-- 1. 복합 컬럼 인덱스 생성
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);
-- 2. 실행계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 50000
AND order_date BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE;
--- QUERY PLAN ---
Index Scan using idx_orders_cust_date on orders (cost=0.43..2.66 rows=1 width=80) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: ((customer_id = 50000) AND (order_date >= (CURRENT_DATE - 30)) AND (order_date <= CURRENT_DATE))
Buffers: shared read=3
Planning:
Buffers: shared hit=30 read=1 dirtied=3
Planning Time: 0.259 ms
Execution Time: 0.037 ms
(7 rows)
▸ 부분(Partial) 인덱스
-- 1. 파샬 인덱스
CREATE INDEX idx_orders_complete_partial
ON orders (order_date)
WHERE status = 'COMPLETE';
-- 2. 실행계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE status = 'COMPLETE'
AND order_date BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE;
--- QUERY PLAN ---
Bitmap Heap Scan on orders (cost=124.63..8483.38 rows=11044 width=80) (actual time=1.692..11.588 rows=10274 loops=1)
Recheck Cond: ((order_date >= (CURRENT_DATE - 7)) AND (order_date <= CURRENT_DATE) AND ((status)::text = 'COMPLETE'::text))
Heap Blocks: exact=7301
Buffers: shared hit=7301 read=12
-> Bitmap Index Scan on idx_orders_complete_partial (cost=0.00..121.87 rows=11044 width=0) (actual time=0.811..0.811 rows=10274 loops=1)
Index Cond: ((order_date >= (CURRENT_DATE - 7)) AND (order_date <= CURRENT_DATE))
Buffers: shared read=12
Planning:
Buffers: shared hit=28 read=1
Planning Time: 0.280 ms
Execution Time: 12.686 ms
(11 rows)7.3. GIN 인덱스 테스트 (JSONB 검색)
-- 1. GIN 인덱스 생성
CREATE INDEX idx_orders_metadata_gin
ON orders USING GIN (metadata jsonb_path_ops);
-- 2. 실행계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE metadata @> '{"category": "A"}';
--- QUERY PLAN ---
Bitmap Heap Scan on orders (cost=4091.93..24174.60 rows=501733 width=80) (actual time=29.651..202.714 rows=500483 loops=1)
Recheck Cond: (metadata @> '{"category": "A"}'::jsonb)
Heap Blocks: exact=13811
Buffers: shared hit=13881
-> Bitmap Index Scan on idx_orders_metadata_gin (cost=0.00..3966.50 rows=501733 width=0) (actual time=27.595..27.595 rows=500483 loops=1)
Index Cond: (metadata @> '{"category": "A"}'::jsonb)
Buffers: shared hit=70
Planning:
Buffers: shared hit=24
Planning Time: 0.252 ms
Execution Time: 227.286 ms
(11 rows)7.4. BRIN 인덱스 테스트 (대용량 범위 검색)
-- 1. BRIN 인덱스 생성
CREATE INDEX idx_orders_brin_date
ON orders USING BRIN (order_date)
WITH (pages_per_range = 8);
-- 2. 실행계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE order_date BETWEEN CURRENT_DATE - 180 AND CURRENT_DATE;
--- QUERY PLAN ---
Bitmap Heap Scan on orders (cost=136.13..36448.13 rows=495889 width=80) (actual time=0.308..113.568 rows=493712 loops=1)
Recheck Cond: ((order_date >= (CURRENT_DATE - 180)) AND (order_date <= CURRENT_DATE))
Rows Removed by Index Recheck: 506288
Heap Blocks: lossy=13812
Buffers: shared hit=13821
-> Bitmap Index Scan on idx_orders_brin_date (cost=0.00..12.16 rows=1000000 width=0) (actual time=0.297..0.298 rows=138120 loops=1)
Index Cond: ((order_date >= (CURRENT_DATE - 180)) AND (order_date <= CURRENT_DATE))
Buffers: shared hit=9
Planning:
Buffers: shared hit=18
Planning Time: 0.144 ms
Execution Time: 134.591 ms
(12 rows)결론적으로 인덱스는 단순히 인덱스 구조를 아는 데서 그치지 않고 데이터 패턴, 접근 패턴, 파티셔닝 여부, 쓰기 vs. 읽기 비율, 인덱스 유지보수 전략 전반을 유기적으로 설계해야 합니다.
인덱스 디자인과 유지보수는 “쿼리 패턴과 데이터 특성”을 기반으로 지속적으로 재점검해야 하며, 오버엔지니어링(불필요한 인덱스 남발)을 피하고, 모니터링 지표를 통해 최적의 수준을 찾아가는 과정이 필수적입니다.
인덱스 튜닝을 아무리 잘해도 기본 적으로 설계되어 있는 테이블 설계와 접근 패턴 등은 수정이 어렵기 때문에 전체 성능은 인덱스 튜닝보다는 설계 역량에 따라 더 크게 좌우됩니다.
이번 포스팅은 여기까지~
'PostgreSQL' 카테고리의 다른 글
| PostgreSQL: Citus Extension 파라미터 설정 가이드 (0) | 2025.02.13 |
|---|---|
| PostgreSQL: Citus Extension 설치 및 사용법 (0) | 2025.02.05 |
| PostgreSQL: VACUUM index_cleanup 옵션과 REINDEX의 차이 (0) | 2025.01.17 |
| PostgreSQL: Optimizer(Planner) (0) | 2025.01.14 |
| PostgreSQL: 바인드 변수 실행 계획 최적화 (0) | 2025.01.10 |