PostgreSQL 고급 인덱싱 완전 가이드: GIN·GiST·BRIN·Partial Index 실전 활용

-- 테이블 생성
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    metadata JSONB NOT NULL
);

-- 100만 건 테스트 데이터 삽입
INSERT INTO products (name, metadata)
SELECT
    'product_' || i,
    jsonb_build_object(
        'category', (ARRAY['electronics', 'clothing', 'food', 'toys'])[1 + (i % 4)],
        'price', (random() * 1000)::int,
        'tags', jsonb_build_array(
            (ARRAY['sale', 'new', 'popular', 'limited'])[1 + (i % 4)],
            (ARRAY['premium', 'budget', 'mid-range'])[1 + (i % 3)]
        ),
        'in_stock', (i % 2 = 0)
    )
FROM generate_series(1, 1000000) AS i;

-- GIN 인덱스 생성 (기본 jsonb_ops)
CREATE INDEX idx_products_metadata_gin ON products USING gin (metadata);

-- jsonb_path_ops 연산자 클래스 (더 작은 인덱스, @> 전용)
CREATE INDEX idx_products_metadata_path ON products USING gin (metadata jsonb_path_ops);

-- 포함 검색 쿼리와 EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT id, name
FROM products
WHERE metadata @> '{"category": "electronics", "in_stock": true}';

Bitmap Heap Scan on products  (cost=52.01..2084.18 rows=500 width=20)
  (actual time=1.234..5.678 rows=125000 loops=1)
  Recheck Cond: (metadata @> '{"category": "electronics", "in_stock": true}'::jsonb)
  Heap Blocks: exact=8334
  ->  Bitmap Index Scan on idx_products_metadata_gin  (cost=0.00..51.88 rows=500 width=0)
      (actual time=0.891..0.891 rows=125000 loops=1)
        Index Cond: (metadata @> '{"category": "electronics", "in_stock": true}'::jsonb)
Planning Time: 0.152 ms
Execution Time: 12.345 ms

-- 전문 검색용 테이블 및 GIN 인덱스
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    tsv tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', title), 'A') ||
        setweight(to_tsvector('english', body), 'B')
    ) STORED
);

-- GIN 인덱스 생성
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

-- 전문 검색 쿼리
EXPLAIN ANALYZE
SELECT id, title, ts_rank(tsv, q) AS rank
FROM articles, to_tsquery('english', 'postgresql & indexing') AS q
WHERE tsv @@ q
ORDER BY rank DESC
LIMIT 10;

-- 태그 배열에 GIN 인덱스 생성
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

CREATE INDEX idx_posts_tags_gin ON posts USING gin (tags);

-- 배열 포함 검색
EXPLAIN ANALYZE
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

-- 배열 겹침 검색
EXPLAIN ANALYZE
SELECT * FROM posts WHERE tags && ARRAY['database', 'backend'];

-- PostGIS 확장 설치
CREATE EXTENSION IF NOT EXISTS postgis;

-- 공간 데이터 테이블
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    location GEOMETRY(Point, 4326) NOT NULL
);

-- 100만 건 테스트 데이터 (서울 주변 랜덤 좌표)
INSERT INTO stores (name, location)
SELECT
    'store_' || i,
    ST_SetSRID(ST_MakePoint(
        126.9 + random() * 0.2,   -- 경도
        37.4 + random() * 0.2     -- 위도
    ), 4326)
FROM generate_series(1, 1000000) AS i;

-- GiST 인덱스 생성
CREATE INDEX idx_stores_location_gist ON stores USING gist (location);

-- 반경 1km 이내 매장 검색
EXPLAIN ANALYZE
SELECT id, name,
       ST_Distance(location::geography,
                   ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326)::geography) AS distance_m
FROM stores
WHERE ST_DWithin(location::geography,
                 ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326)::geography,
                 1000)
ORDER BY distance_m
LIMIT 20;

Limit  (cost=8.45..8.50 rows=20 width=44)
  (actual time=2.345..2.678 rows=20 loops=1)
  ->  Sort  (cost=8.45..8.52 rows=25 width=44)
      (actual time=2.340..2.350 rows=20 loops=1)
        Sort Key: (st_distance(...))
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Index Scan using idx_stores_location_gist on stores
            (cost=0.42..7.89 rows=25 width=44)
            (actual time=0.234..2.123 rows=785 loops=1)
              Index Cond: (location && ...)
              Filter: st_dwithin(...)
Planning Time: 0.456 ms
Execution Time: 2.789 ms

-- 예약 시스템에서 시간 범위 겹침 검색
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    period TSTZRANGE NOT NULL,
    guest_name TEXT
);

-- GiST 인덱스로 범위 겹침 검색 최적화
CREATE INDEX idx_reservations_period_gist ON reservations USING gist (period);

-- 특정 기간과 겹치는 예약 조회
EXPLAIN ANALYZE
SELECT * FROM reservations
WHERE period && tstzrange('2026-03-10 14:00', '2026-03-10 18:00', '[)');

-- EXCLUDE 제약 조건으로 겹침 방지 (GiST 필수)
ALTER TABLE reservations
ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, period WITH &&);

-- 가장 가까운 10개 매장을 인덱스 스캔으로 조회
SELECT id, name, location <-> ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326) AS dist
FROM stores
ORDER BY location <-> ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326)
LIMIT 10;

-- 이벤트 로그 테이블 (시계열)
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    event_type TEXT NOT NULL,
    payload JSONB
);

-- 5000만 건 테스트 데이터 삽입
INSERT INTO events (created_at, event_type, payload)
SELECT
    '2025-01-01'::timestamptz + (i || ' seconds')::interval,
    (ARRAY['click', 'view', 'purchase', 'signup'])[1 + (i % 4)],
    jsonb_build_object('user_id', (i % 100000), 'value', random() * 100)
FROM generate_series(1, 50000000) AS i;

-- B-tree 인덱스 생성 (비교 기준)
CREATE INDEX idx_events_created_btree ON events (created_at);

-- BRIN 인덱스 생성
CREATE INDEX idx_events_created_brin ON events USING brin (created_at)
WITH (pages_per_range = 128);

-- 인덱스 크기 비교
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'events' AND indexname LIKE 'idx_events_created%';

       indexname              | index_size
------------------------------+------------
 idx_events_created_btree     | 1071 MB
 idx_events_created_brin      | 128 kB

-- BRIN 인덱스를 사용한 범위 검색
EXPLAIN ANALYZE
SELECT count(*) FROM events
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30';

Aggregate  (cost=456789.12..456789.13 rows=1 width=8)
  (actual time=234.567..234.568 rows=1 loops=1)
  ->  Bitmap Heap Scan on events  (cost=48.12..445678.90 rows=2592000 width=0)
      (actual time=12.345..198.765 rows=2592000 loops=1)
        Recheck Cond: (created_at >= ... AND created_at <= ...)
        Rows Removed by Recheck: 45678
        Heap Blocks: lossy=19200
        ->  Bitmap Index Scan on idx_events_created_brin  (cost=0.00..47.50 rows=2600000 width=0)
            (actual time=0.234..0.234 rows=192000 loops=1)
Planning Time: 0.123 ms
Execution Time: 256.789 ms

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';
-- correlation 값이 0.95 이상이면 BRIN 사용 적합

-- 주문 테이블에서 활성 주문만 인덱싱
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    total_amount NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 전체 인덱스 vs 부분 인덱스 크기 비교
CREATE INDEX idx_orders_status_full ON orders (status, created_at);
CREATE INDEX idx_orders_status_partial ON orders (created_at)
    WHERE status IN ('pending', 'processing');

-- 부분 인덱스가 사용되는 쿼리
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > now() - interval '7 days'
ORDER BY created_at DESC;

-- 사용자별 활성 이메일은 하나만 허용
CREATE UNIQUE INDEX idx_unique_active_email
ON users (email) WHERE is_active = true;

-- is_active = false인 행은 중복 이메일 허용
-- is_active = true인 행은 이메일 유니크 보장

-- 대소문자 무시 검색을 위한 표현식 인덱스
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 이 쿼리는 인덱스 사용
EXPLAIN ANALYZE
SELECT * FROM users WHERE lower(email) = 'user@example.com';

-- 날짜 추출 표현식 인덱스
CREATE INDEX idx_orders_created_date ON orders ((created_at::date));

-- 날짜별 집계에 활용
EXPLAIN ANALYZE
SELECT created_at::date AS order_date, count(*)
FROM orders
WHERE created_at::date = '2026-03-10'
GROUP BY created_at::date;

-- JSONB 특정 키에 대한 표현식 인덱스 (GIN보다 작은 크기)
CREATE INDEX idx_products_category ON products ((metadata->>'category'));

-- B-tree 기반이므로 등호/범위 검색 가능
EXPLAIN ANALYZE
SELECT * FROM products WHERE metadata->>'category' = 'electronics';

-- 최근 7일간 활성 사용자의 이메일 검색 최적화
CREATE INDEX idx_recent_active_users_email
ON users (lower(email))
WHERE last_login_at > now() - interval '7 days'
  AND is_active = true;

-- 기본 EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- 실행 계획 핵심 확인 포인트:
-- 1. Scan 타입: Index Scan vs Bitmap Index Scan vs Seq Scan
-- 2. actual time: 실제 소요 시간
-- 3. rows: 예상 행 수 vs 실제 행 수 차이
-- 4. Buffers: shared hit (캐시) vs shared read (디스크)

-- 사용되지 않는 인덱스 찾기
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique
  AND NOT indisprimary
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- pgstattuple 확장으로 블로트 측정
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    avg_leaf_density,
    leaf_pages,
    empty_pages,
    deleted_pages,
    round(100 - avg_leaf_density, 1) AS bloat_pct
FROM pgstatindex('idx_orders_status_full');

-- bloat_pct가 20%를 초과하면 REINDEX 고려

-- 특정 인덱스 재구축 (무중단)
REINDEX INDEX CONCURRENTLY idx_orders_status_full;

-- 테이블의 모든 인덱스 재구축
REINDEX TABLE CONCURRENTLY orders;

-- 스키마 전체 인덱스 재구축
REINDEX SCHEMA CONCURRENTLY public;

-- 유효하지 않은 인덱스 확인
SELECT indexrelid::regclass AS index_name,
       indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- 유효하지 않은 인덱스 삭제
-- DROP INDEX CONCURRENTLY idx_name_ccnew;

-- 블로트 20% 초과 인덱스를 자동으로 찾는 모니터링 쿼리
SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size,
    round(100 - (pgstatindex(indexname)).avg_leaf_density, 1) AS bloat_pct
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY bloat_pct DESC NULLS LAST;