Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.
인덱스는 데이터베이스 성능을 향상시키는 일반적인 방법이다. 인덱스를 사용하면 데이터베이스 서버가 인덱스가 없는 경우보다 훨씬 더 빨리 특정 행을 찾고 검색할 수 있다. 하지만 인덱스는 데이터베이스 시스템 전체에 오버헤드를 추가하므로 이를 현명하게 사용해야 한다.
* 오버헤드 : 어떤 처리를 하기 위해 들어가는 간접적인 처리 시간 · 메모리 등을 말한다.
PostgreSQL은 B-Tree, Hash, GiST, SP-GiST, GIN, BRIN 등 총 6가지 인덱스 방식을 제공합니다. 이 중 B-Tree가 기본 설정이며 가장 일반적으로 사용된다.
😃 B-Tree
각 노드가 여러 개의 자식 노드를 가질 수 있어 트리의 높이를 낮추고, 빠른 데이터 접근이 가능하게 설계된 것이 특징이며, 주요한 B-Tree의 특성은 다음과 같다.
- 루트 노드, 브랜치 노드, 리프 노드로 구성 - 항상 균형 잡힌 트리로 구성되어 성능에 안정성을 가짐 - 각 노드는 여러 개의 키와 포인터를 가짐 - 리프 노드는 데이터 주소를 가리키는 포인터를 가짐
📈 Index 성능 분석
1️⃣ 단일 인덱스에 대한 성능 분석 - 유저 포인트 조회
💬 유저의 포인트를 조회한다는 가정하에 성능을 테스트해보고자 한다.
포인트 테이블 생성
CREATE TABLE point (
id SERIAL PRIMARY KEY,
user_id BIGINT,
amount BIGINT NOT NULL DEFAULT 0,
created_date timestamp(6) NOT NULL DEFAULT now()
)
* 인덱스 추가
CREATE INDEX idx_user_id ON point(user_id);
더미 데티어 생성
INSERT INTO point (user_id, amount)
SELECT
generate_series, 10000
FROM generate_series(1, 10000000);
CREATE TABLE concert (
id SERIAL PRIMARY KEY,
created_date TIMESTAMP(6) NOT NULL DEFAULT NOW()
)
CREATE TABLE concert_schedule (
id SERIAL PRIMARY KEY,
concert_id BIGINT NOT NULL,
performance_date TIMESTAMP(6) NOT NULL,
booking_start_date TIMESTAMP(6) NOT NULL,
booking_end_date TIMESTAMP(6) NOT NULL,
created_date TIMESTAMP(6) NOT NULL DEFAULT NOW()
)
더미 데티어 생성
INSERT INTO concert
SELECT
FROM generate_series(1, 10000000);
WITH schedule_data AS (
SELECT
floor(random() * 100000) + 1 AS concert_id,
CASE
WHEN random() < 0.5 THEN
CURRENT_TIMESTAMP - (random() * INTERVAL '30 days')
ELSE
CURRENT_TIMESTAMP + (random() * INTERVAL '30 days')
END AS booking_start_date
FROM generate_series(1, 10000000)
)
INSERT INTO concert_schedule (concert_id, booking_start_date, booking_end_date, performance_date)
SELECT
concert_id,
booking_start_date,
booking_start_date + INTERVAL '50 days' AS booking_end_date,
booking_start_date + INTERVAL '60 days' AS performance_date
FROM schedule_data;
조회
SELECT * FROM concert_schedules
LEFT JOIN concerts ON concert_id = concerts.id
WHERE
concert_id = 100
AND booking_start_at <= CURRENT_TIMESTAMP
AND booking_end_at > CURRENT_TIMESTAMP;
성능 비교
인덱스 X
Nested Loop Left Join (cost=1000.43..188222.14 rows=50 width=56)
-> Gather (cost=1000.00..188213.06 rows=50 width=44)
Workers Planned: 2
-> Parallel Seq Scan on concert_schedule (cost=0.00..187208.06 rows=21 width=44)
Filter: ((concert_id = 100) AND (booking_start_date <= CURRENT_TIMESTAMP) AND (booking_end_date > CURRENT_TIMESTAMP))
-> Materialize (cost=0.43..8.46 rows=1 width=12)
-> Index Scan using concert_pkey on concert (cost=0.43..8.45 rows=1 width=12)
Index Cond: (id = 100)
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
