B-tree 인덱스 vs Hash 인덱스

B-tree 인덱스 vs Hash 인덱스

                                           B-tree 인덱스                                                                              Hash 인덱스

 

지원하는 연산	=, <, >, BETWEEN, LIKE 'abc%'	= 만 지원
내부 구조	정렬된 트리 구조	해시 버킷 구조
검색 속도	O(log n)	O(1) (이론상 더 빠름)
정렬된 데이터	정렬 유지됨 → ORDER BY, 범위 조회 가능	정렬 불가
PostgreSQL 기본값	✅ 기본 인덱스 타입	❌ 사용 시 USING HASH 명시 필요

 

 

✔️ 언제 Hash 인덱스가 유리할까?

• 조건이 정확히 일치하는 경우만 있고 (WHERE username = 'abc')
• 데이터가 매우 많고, 인덱스 탐색이 병목인 경우

→ 이럴 땐 Hash가 이론적으로 빠르긴 한데, PostgreSQL에서는 B-tree가 워낙 최적화되어 있어서 대부분 B-tree가 우위이다.

 

 

✔️ 인덱스가 불필요한 경우

1. row 수가 너무 적으면

• 예: users 테이블에 10개 row만 있을 때
• → 전체 테이블을 순회(Seq Scan)하는 게 오히려 빠름

2. 조건이 인덱스를 안 타는 경우

• WHERE LOWER(username) = 'abc' → 인덱스 못 씀 (함수 때문에)
• LIKE '%abc%' → 앞이 와일드카드면 정렬 정보 못 씀

해결 방법 expression index나 GIN/Trigram index 사용 가능

 

 

 

❌ 인덱스를 못 타는 예: 함수 사용

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id serial PRIMARY KEY,
    username text,
    email text
);

-- 10,000개의 더미 데이터 삽입
INSERT INTO users (username, email)
SELECT 'user_' || i, 'user_' || i || '@example.com'
FROM generate_series(1, 10000) AS i;

-- 특정 유저 한 명 추가
INSERT INTO users (username, email)
VALUES ('special_user', 'special@example.com');

 

 

 

testdb=# create index idx_username on users(username);
CREATE INDEX


testdb=# explain analyze
testdb-# select*from users where lower(username) = 'special_user';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..224.01 rows=50 width=29) (actual time=2.209..2.209 rows=1 loops=1)
   Filter: (lower(username) = 'special_user'::text)
   Rows Removed by Filter: 10000
 Planning Time: 0.077 ms
 Execution Time: 2.223 ms
(5개 행)


testdb=# EXPLAIN ANALYZE
testdb-# SELECT * FROM users WHERE username = 'special_user';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_username on users  (cost=0.29..8.30 rows=1 width=29) (actual time=0.023..0.024 rows=1 loops=1)
   Index Cond: (username = 'special_user'::text)
 Planning Time: 0.135 ms
 Execution Time: 0.037 ms
(4개 행)

 

 

✅ 해결 방법 : 함수 기반 인덱스

testdb=# create index idx_lower_username on users(lower(username));
CREATE INDEX
testdb=# explain analyze
testdb-# select * from users where(username) ='special_user';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_username on users  (cost=0.29..8.30 rows=1 width=29) (actual time=0.014..0.015 rows=1 loops=1)
   Index Cond: (username = 'special_user'::text)
 Planning Time: 0.336 ms
 Execution Time: 0.030 ms
(4개 행)

 

 

 

✅ users 테이블 인덱스 목록 확인

testdb=# SELECT
testdb-#     indexname,
testdb-#     indexdef
testdb-# FROM
testdb-#     pg_indexes
testdb-# WHERE
testdb-#     tablename = 'users';
     indexname      |                                   indexdef
--------------------+-------------------------------------------------------------------------------
 users_pkey         | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
 idx_username       | CREATE INDEX idx_username ON public.users USING btree (username)
 idx_lower_username | CREATE INDEX idx_lower_username ON public.users USING btree (lower(username))
(3개 행)

 

✅ 인덱스 상세 + 사용 횟수까지 확인

SELECT
    i.relname AS index_name,
    ix.indisunique AS is_unique,
    ix.indisprimary AS is_primary,
    a.idx_scan AS times_used,
    pg_get_indexdef(i.oid) AS definition
FROM
    pg_class t
JOIN
    pg_index ix ON t.oid = ix.indrelid
JOIN
    pg_class i ON i.oid = ix.indexrelid
LEFT JOIN
    pg_stat_user_indexes a ON i.oid = a.indexrelid
WHERE
    t.relname = 'users';