인덱스 전략: B-tree/GIN/GiST 선택

| 타입 | 적합 | 예시 |
|---|---|---|
| B-tree (기본) | 등호·범위·정렬 | id, created_at |
| Hash | 등호만 | UUID, hash 값 |
| GIN | 배열·JSONB·전문 검색 | tags, search |
| GiST | 기하·전문·범위 | location, tsrange |
| BRIN | 매우 큰 시계열 | logs (TB+) |
| SP-GiST | 분할 트리 | IP·geo |

-- 단일 컬럼
CREATE INDEX idx_users_email ON users(email);

-- 복합 (순서 중요!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- WHERE user_id = ? AND created_at > ? ORDER BY created_at DESC
-- → idx_orders_user_date 활용 ✅


-- 표현식 인덱스
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- WHERE LOWER(email) = LOWER('Alice@Example.com') ✅

-- 1. JSONB
ALTER TABLE products ADD COLUMN attributes JSONB;
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 빠름
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM products WHERE attributes ? 'size';


-- 2. 배열
ALTER TABLE posts ADD COLUMN tags TEXT[];
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

SELECT * FROM posts WHERE tags && ARRAY['react', 'typescript'];  -- 겹침
SELECT * FROM posts WHERE tags @> ARRAY['react'];                 -- 포함


-- 3. 전문 검색
ALTER TABLE posts ADD COLUMN search tsvector;

UPDATE posts SET search =
  setweight(to_tsvector('korean', title), 'A') ||
  setweight(to_tsvector('korean', content), 'B');

CREATE INDEX idx_posts_search ON posts USING GIN (search);

SELECT * FROM posts
WHERE search @@ to_tsquery('korean', 'react & typescript')
ORDER BY ts_rank(search, to_tsquery('korean', 'react & typescript')) DESC;

CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search :=
    setweight(to_tsvector('korean', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('korean', COALESCE(NEW.content, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_posts_search
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- PostGIS — 지리
CREATE EXTENSION postgis;

ALTER TABLE places ADD COLUMN location GEOGRAPHY(POINT, 4326);
CREATE INDEX idx_places_location ON places USING GIST (location);

-- 반경 5km 검색
SELECT name, ST_Distance(location, ST_MakePoint(127.0, 37.5)::geography) as dist
FROM places
WHERE ST_DWithin(location, ST_MakePoint(127.0, 37.5)::geography, 5000)
ORDER BY dist
LIMIT 20;


-- 시간 범위
ALTER TABLE bookings ADD COLUMN period TSTZRANGE;
CREATE INDEX idx_bookings_period ON bookings USING GIST (period);

-- 충돌 검사
SELECT * FROM bookings
WHERE period && tstzrange('2026-04-29 14:00', '2026-04-29 16:00');

-- 매우 큰 테이블 (수억 행) + 시간순 데이터
CREATE INDEX idx_logs_created_brin ON logs USING BRIN (created_at);

-- BRIN = Block Range Index
-- 디스크 블록 단위 min/max만 저장
-- B-tree의 1/100 크기
-- INSERT 부담 거의 없음

-- 단점: 점 검색은 느림 (범위 검색만 빠름)

WHERE 컬럼 = 값?
├─ 텍스트·숫자·날짜 → B-tree
├─ JSONB·배열 → GIN
├─ 전문 검색 → GIN (tsvector)
└─ 지리·범위 → GiST


매우 큰 시계열 (수억+) → BRIN

UUID·해시 등호만 → Hash

인덱스 1개 추가 = INSERT 5~10% 느림

→ 너무 많이 만들지 말기
→ unused index 주기적 제거

-- 미사용 인덱스 찾기
SELECT
  schemaname, relname AS table, indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;