쿼리 수명 주기와 플래너
PostgreSQL에서 쿼리 성능 개선하기
Amy McCarty
Instructor
기본 쿼리 수명 주기
| 시스템 | 프런트엔드 단계 | 백엔드 프로세스 | |
|---|---|---|---|
| 1 | 파서 | 쿼리를 데이터베이스로 전송 | 구문 검사. 시스템 규칙에 따라 SQL을 기계 친화적 구문으로 변환. |
| 2 | 플래너 & 옵티마이저 | 쿼리 작업 평가·최적화 | DB 통계를 사용해 쿼리 계획 수립. 비용 계산 후 최적 계획 선택. |
| 3 | 실행기 | 쿼리 결과 반환 | 쿼리 계획을 따라 실행. |
쿼리 플래너와 옵티마이저
SQL 구조 변화에 반응
- 계획 트리 생성
- 단계에 대응하는 노드
- EXPLAIN으로 시각화
- 각 트리 비용 추정
- pg_tables의 통계 사용
- 시간 기반 최적화
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
pg_tables의 통계
SELECT * FROM pg_class
WHERE relname = 'mytable'
-- sample of output columns
| relname | relhasindex |
SELECT * FROM pg_stats
WHERE tablename = 'mytable'
-- sample of output columns
null_frac | avg_width | n_distinct |
- 열 인덱스 여부
- NULL 값 개수
- 열 너비
- 고유 값 수
EXPLAIN
- 쿼리 계획을 들여다보는 창
- 단계와 비용은 추정치
- 쿼리를 실행하지 않음
- cheeses 테이블 순차 스캔
- 비용·크기 추정치
EXPLAIN
SELECT * FROM cheeses
Seq Scan on cheeses
(cost=0.00..10.50 rows=5725 width=296)
EXPLAIN: Scan
- 쿼리 계획 단계
- 반환 행
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- Seq Scan: 테이블의 모든 행을 스캔
EXPLAIN: Cost
- 무차원 지표
- 동일 출력 구조끼리 비교
- 출력이 다른 쿼리는 비교하지 말 것
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- 0.00.. : 시작 비용
..10.50 : 총 비용
총 비용 = 시작 + 실행 비용
EXPLAIN: Size
- 크기 추정치
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- rows: 실행 시 검사할 행 수
- width: 행의 바이트 너비
WHERE 절이 있는 EXPLAIN
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep')
Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
-> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
- 아래에서 위로 해석
- 1단계: 필터
- 2단계: 순차 스캔
- WHERE 절
- 스캔 행 수 감소, 총 비용 증가
인덱스가 있는 EXPLAIN
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column
Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
- 1단계: 비트맵 인덱스 스캔
- Index Cond가 스캔 단계를 설명
- INDEX
- 시작 비용이 0에서 증가
- 전체 비용이 379에서 감소
Ayo berlatih!
PostgreSQL에서 쿼리 성능 개선하기
