여기 진짜 중요한 포인트가 있어: SQL 쿼리는 그냥 코드 한 줄이 아니라, 데이터베이스랑 대화하는 거야. 만약 너가 살짝 "SELECT *"라고 속삭이면, 데이터베이스도 잘 알아듣고 바로 실행해줄 거야. 근데 구조 없는 SQL 소설을 들이밀면, 데이터베이스가 고민하다가... 느려지기 시작하지.
쿼리 최적화는 데이터베이스가 이해하기 쉽고 간결한 언어로 말하는 능력이야. 쿼리가 깔끔하고 효율적으로 작성되면, 빠르게 실행되고 시스템에 부담도 안 주고, 다른 프로세스에도 방해 안 돼. 근데 쿼리가 엉망이면 시스템 전체가 느려질 수 있어: 데이터베이스가 CPU랑 메모리를 더 많이 쓰고, 디스크도 쓸데없이 읽고 쓰고, 데이터베이스를 쓰는 다른 앱들도 버벅거릴 수 있지.
EXPLAIN ANALYZE는 이런 문제 구간을 찾아내고, 쿼리가 어디서 "힘들어하는지" 파악하게 도와줘. 이건 진단이야 — 진단 없이 성능을 고치기 힘들지.
쿼리에서 흔한 문제랑 그걸 찾는 방법
이제 성능 저하의 용의자들을 만나볼 시간이야. EXPLAIN ANALYZE라는 무기를 들고 가자.
문제 1: 순차 스캔 (Seq Scan)
Seq Scan (순차 스캔)은 PostgreSQL이 데이터를 찾으려고 테이블의 모든 행을 하나씩 다 뒤지는 거야. 테이블이 작으면 괜찮은데, 크면 진짜 고통이지.
Seq Scan이 쓰이고 있는지 어떻게 알까? 그냥 EXPLAIN ANALYZE로 분석해봐. 예시:
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE student_id = 123;
결과는 이렇게 나올 수 있어 (Seq Scan에 주목!):
Seq Scan on students (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)
이 문제를 어떻게 해결하지?
student_id에 인덱스가 없다면 만들어줘:
CREATE INDEX idx_student_id ON students(student_id);
그 다음 다시 EXPLAIN ANALYZE를 돌려봐. 이제 Seq Scan 대신 Index Scan이 나와야 해.
문제 2: 조건의 낮은 셀렉티비티
셀렉티비티는 원하는 걸 찾으려면 몇 행을 처리해야 하는지야. 필터가 테이블 거의 전체를 커버하면, 인덱스도 소용없어.
셀렉티비티가 낮은 쿼리 예시:
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE program = 'Computer Science';
만약 테이블에 90% 학생이 Computer Science 전공이면, 인덱스가 있어도 Seq Scan이 동작할 수 있어.
쿼리를 어떻게 개선할까?
- 쿼리 로직을 다시 생각해봐: 필터를 더 명확하게 추가할 수 있는지 확인해봐.
- 테이블 통계가 최신인지 확인해 (이게 PostgreSQL이 셀렉티비티를 제대로 평가하는 데 도움돼):
ANALYZE students;
- 쿼리가 괜히 인덱스 대신 순차 스캔을 쓰면, PostgreSQL한테 인덱스를 강제로 쓰라고 해볼 수도 있어:
SET enable_seqscan = OFF;
문제 3: 불필요한 정렬 연산
정렬(Sort)은 특히 데이터가 메모리에 안 들어갈 때 비싼 연산이야. ORDER BY가 대표적인 정렬을 요구하는 구문이지.
문제 예시:
EXPLAIN ANALYZE
SELECT *
FROM students
ORDER BY last_name;
이런 결과가 나올 수 있어:
Sort (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)
정렬을 더 빠르게 하려면? 특정 컬럼으로 자주 정렬한다면, 인덱스를 만들어봐:
CREATE INDEX idx_last_name ON students(last_name);
이제 PostgreSQL이 인덱스를 써서 정렬 없이 바로 정렬된 데이터를 뽑을 수 있어.
문제 4: 제한(LIMIT)이 없는 쿼리
SELECT에서 반환 행 수 제한 없이 데이터를 요청하면, 사실상 테이블 전체를 다 읽게 돼. 사실 너한테 필요한 건 첫 번째 행일 수도 있는데 말이지.
이렇게 생겼어:
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE gpa > 3.5;
만약 데이터베이스에 백만 행이 있고, gpa > 3.5가 테이블의 80%를 반환한다면, 오래 기다려야 할 거야.
진짜 상위 10명만 필요하다면 LIMIT을 써:
SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;
그리고 LIMIT이랑 OFFSET을 같이 써서 페이지네이션도 할 수 있어.
실행 파라미터 관리: SET
PostgreSQL에서 SET 명령은 세션이나 쿼리 실행 파라미터를 바꿀 때 써. 이건 일시적인 세팅이라, 현재 연결에서만 데이터베이스 동작에 영향을 줘.
쉽게 말해서, SET은 PostgreSQL의 "기분"을 실시간으로 조절하는 방법이야. 글로벌 세팅을 건드릴 필요 없이!
언제 쓰냐면?
- 리포트 돌리기 전에 언어나 날짜 포맷 바꿀 때.
- 무거운 쿼리 하나에만 메모리 늘리고 싶을 때.
- 대량 데이터 적재할 때 로그 끄고 싶을 때.
- 스키마 검색 경로(
search_path)를 잠깐 바꿀 때. - 보안 관리 (예: 일시적으로 유저 권한 낮추기).
기본 문법
SET 파라미터 = 값;
현재 파라미터 값을 보고 싶으면:
SHOW 파라미터;
기본값으로 되돌리려면:
RESET 파라미터;
복합 최적화 예시
예를 들어, 이런 미션이 있다고 해보자: Computer Science 전공 중 GPA가 가장 높은 학생 10명을 찾기. 원래 쿼리는 이래:
SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;
쿼리 분석: 먼저
EXPLAIN ANALYZE를 실행해봐:EXPLAIN ANALYZE SELECT * FROM students WHERE program = 'Computer Science' ORDER BY gpa DESC LIMIT 10;순차 스캔이랑 정렬이 보이면, 최적화 신호야.
필터랑 정렬에 인덱스 추가:
두 컬럼을 포함하는 복합 인덱스를 만들어봐:
CREATE INDEX idx_program_gpa ON students(program, gpa DESC);개선 확인:
다시
EXPLAIN ANALYZE를 실행해봐. 이제 위 인덱스를 써서 정렬이랑 순차 스캔을 피할 수 있어야 해.
쿼리 최적화 방법론
현재 실행 플랜부터 분석해.
EXPLAIN ANALYZE로 문제 연산을 찾아봐.병목 구간을 파악해. 플랜에서 시간이나 리소스를 많이 쓰는 노드를 찾아.
인덱스를 추가해. 필터링이나 정렬에 쓰이는 컬럼을 확인하고, 필요한 인덱스를 만들어.
데이터 양을 최소화해.
LIMIT,OFFSET그리고 정확한 필터 조건을 써.통계를 최신으로 유지해.
ANALYZE를 실행해서 PostgreSQL이 데이터 분포를 잘 알게 해.변경 후 테스트해. 최적화 후 다시
EXPLAIN ANALYZE를 돌려서 성능이 좋아졌는지 확인해.
이제 뭐하지?
지금 너는 쿼리 최적화 초스피드 코스를 끝냈어. 축하해! EXPLAIN ANALYZE로 실험을 많이 해볼수록 PostgreSQL의 속사정을 더 잘 알게 될 거야. 그리고 기억해: 아무리 마법 같은 인덱스도 쿼리가 너무 복잡하거나 애매하면 소용없어. SQL도 다른 언어처럼, 명확함을 좋아해.
GO TO FULL VERSION