pg_stat_statements를 이용한 인덱스와 필터 사용 분석

인덱스는 책에서 북마크 꽂아두는 거랑 비슷해. 덕분에 원하는 데이터를 빠르게 찾을 수 있지. 근데 북마크를 엄청 많이 꽂아놨는데 아무도 안 쓰면? 아니면, 더 안 좋은 건, 이상하게 꽂아놔서 결국 책 처음부터 끝까지 다 뒤져야 한다면? 그래서 인덱스가 실제로 잘 쓰이고 있는지 분석이 필요해.

구린 쿼리는 인덱스를 무시하고, 결국 전체 테이블을 쭉 훑는 Seq Scan을 하게 돼. 이러면 쿼리 느려지고 서버에 부담도 커져. 우리의 목표는 어떤 쿼리가 인덱스를 안 쓰는지, 그리고 왜 그런지 파악하는 거야.

인덱스가 사용되는지 어떻게 알 수 있을까?

두 가지 핵심 문제를 보자:

1. 우리가 만든 인덱스가 실제로 쓰이고 있나?
2. 쓰이고 있다면, 효과적으로 쓰이고 있나?

이걸 확인하려면 pg_stat_statements에서 쿼리 통계를 분석하면 돼. 특히 다음 컬럼들을 보면 좋아:

• rows: 쿼리로 처리된 행의 수.
• shared_blks_hit: 메모리에서 읽은 페이지 수(디스크에서가 아님).
• shared_blks_read: 실제로 디스크에서 읽은 페이지 수.

쿼리가 처리하는 행이 적고, shared_blks_hit 비율이 높을수록 인덱스가 잘 동작하는 거야.

인덱스 분석 예시

예를 들어, 학생 테이블이 있다고 해보자:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- grade_level에 인덱스 추가
CREATE INDEX idx_grade_level ON students(grade_level);

이제 실험용으로 데이터 넣어보자:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    '학생 ' || generate_series(1, 100000),
    '성씨',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

특정 학년의 학생을 찾는 쿼리를 실행해보자:

SELECT *
FROM students
WHERE grade_level = 10;

pg_stat_statements에서 확인하기

이 쿼리를 여러 번 실행한 다음, 통계를 확인해보자:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

결과 해석:

• rows: 쿼리가 너무 많은 행을 반환한다면, 인덱스가 의미 있을까? 셀렉티비티가 낮은 조건엔 인덱스가 별로 필요 없을 수도 있어.
• shared_blks_hit와 shared_blks_read: 디스크에서 읽은 페이지(shared_blks_read)가 많으면, 인덱스가 안 쓰이거나, 데이터가 버퍼 풀 밖에 있다는 뜻이야.

인덱스 최적화

인덱스를 만든다고 끝이 아니야. PostgreSQL이 실제로 그걸 써줘야 의미가 있지. 근데 가끔은, 아무리 인덱스를 만들어도 DB가 인덱스 대신 테이블 전체를 훑는 경우가 있어. 왜 그럴까? 같이 알아보자.

먼저, 인덱스가 명백히 유용해 보이는데도 무시되는 이유부터 살펴보고, 그 다음엔 DB가 인덱스를 꼭 쓰게 만드는 팁을 알아보자.

만약 인덱스가 안 쓰인다면?

가끔 PostgreSQL은 인덱스를 무시하고 Seq Scan을 해. 그 이유는 여러 가지가 있어:

1. 조건의 셀렉티비티가 낮음. 쿼리가 테이블의 절반 이상을 반환하면, 전체 스캔이 더 빠를 수도 있어.
2. 데이터 타입이나 함수 사용. 인덱스가 걸린 컬럼에 함수 쓰면 인덱스가 무시될 수 있어. 예를 들어:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- 인덱스 안 씀

이런 경우엔 쿼리를 이렇게 바꿔보자:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- 인덱스 사용

3. 인덱스 타입이 안 맞음. 예를 들어, 풀텍스트 검색엔 GIN이나 GiST 인덱스가 B-TREE보다 좋아.

4. 통계 정보가 잘못됨. 통계가 오래됐으면 옵티마이저가 잘못 판단할 수 있어. ANALYZE로 갱신해줘:

   ANALYZE students;
   

쿼리 개선하기

다시 예시로 돌아가서, 인덱스가 안 먹힌다면 이렇게 해보자:

1. 인덱스를 쓸 수 있는 필터를 썼는지 확인: 함수, 타입 변환 등은 피하자.
2. 필터가 너무 많은 값을 반환하면, 인덱스가 필요 없는지도 생각해보자. 자주 쓰는 쿼리라면 테이블 구조를 바꾸거나, 머티리얼라이즈드 뷰를 써보는 것도 방법이야.
3. 데이터가 너무 많아서 Seq Scan이 뜬다면, 테이블을 파티셔닝(PARTITION BY)하는 것도 고려해봐.

인덱스 효율성 체크

최적화 후에 쿼리를 다시 실행하고 통계를 확인해보자:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

최적화 전후로 메트릭을 비교해봐. 디스크 읽기(shared_blks_read)가 줄고, 메모리 히트(shared_blks_hit)가 늘어났으면 성공!

실전 케이스

1. 인덱스 잘못 사용한 경우

예를 들어, description이라는 텍스트 필드가 있는 상품 테이블이 있다고 하자:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- 풀텍스트 검색용 인덱스
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

이런 쿼리를 실행하면:

SELECT *
FROM products
WHERE description ILIKE '%스마트폰%';

인덱스는 사용되지 않아! 이유는 ILIKE가 GIN이랑 호환이 안 되기 때문이야. 인덱스를 쓰려면 쿼리를 이렇게 바꿔야 해:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('스마트폰');

2. 필요한 곳에 인덱스가 없는 경우

예를 들어, 이런 쿼리가 있다고 하자:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

이 쿼리가 Seq Scan을 한다면, birth_date에 인덱스가 없어서일 수 있어. 인덱스를 만들고:

CREATE INDEX idx_birth_date ON students(birth_date);

그리고 통계(ANALYZE students)도 갱신하면, 이 쿼리 속도가 확 빨라질 거야.