4.4Basic Query Tuning

인덱싱의 원리를 배웠으니, 실제 쿼리의 성능을 향상하기 위한 작업을 해보자.

PostgreSQL은 사용자가 보낸 SQL 쿼리를 다음과 같은 단계를 통해 실행한다:

1. Parser: 쿼리의 문법을 체크하고 쿼리 트리로 변환한다.
2. Rewwriter: 쿼리 트리를 수정한다.(VIEW로 변환하는데, 자세히는 추후에 알아보자.)
3. Planner: 쿼리를 통해 어떤 정보를 얻으려는지 확인하고, 최적의 실행 전략을 수립한다.(인덱스 사용 여부, JOIN 방식 등)
4. Execute: 실제 코드를 실행하고 결과를 반환한다.

이 중 이번 포스팅에선 Planner 단계에서 PostgreSQL이 어떻게 쿼리를 분석하고 실행 계획을 수립하는지 EXPLAIN 도구를 통해 확인해보자.

EXPLAIN과 EXPLAIN ANALYZE

1. EXPLAIN

쿼리를 실제로 실행하지 않고, 내부적으로 어떤 계획이 수립될지를 확인한다.

sql
EXPLAIN SELECT username, contents
FROM users
JOIN comments ON comments.user_id = users.id
WHERE username = 'Alyson14';

결과는 다음과 같다.

2. EXPLAIN ANALYZE

쿼리를 실제로 실행하고, 수립된 계획과 실제 실행 성능 정보를 같이 반환한다.

sql
EXPLAIN ANALYZE SELECT username, contents
FROM users
JOIN comments ON comments.user_id = users.id
WHERE username = 'Alyson14';

결과는 다음과 같다.

참고로, PGAdmin4를 쓴다면 EXPLAIN ANALYZE 키워드 없이, 위의 Run Query 버튼 오른쪽에 있는 버튼 들 중에 Explain Query 버튼이 있어 이걸 누르면 PGAdmin에서 다이어그램으로 planner를 보여준다.

실행 계획 해석하기

이렇게 나온 실행 계획을 해석해보자.

우선, ->가 있는 행은 쿼리 노드다. 맨 첫 행도 쿼리 노드지만, ->가 생략되어 있다.

위 실행 계획을 구조도로 직접 그리면 아래 도식과 같다.

최상위 연산은 Hash Join으로, 두 테이블을 comments.user_id = users.id 조건으로 연결한다.

Hash Join을 구성하는 두 입력은

• 왼쪽: comments 테이블의 Seq Scan(테이블 전체를 순차, 즉 sequential로 스캔한 것)
• 오른쪽: Hash 노드. 이 Hash는 users_username_idx를 사용한 users 테이블의 Index Scan으로 만들어진 것이다.

cost, rows, width의 의미

EXPLAIN에서 반환한 행에 있는 숫자들은 어떤 의미가 있을까?

Hash Join (cost=8.31..1795.11 rows=11 width=81)|

1. Hash Join: 해당 노드가 데이터를 만드는 방식
2. cost=8.31...1795.11: 이 단계를 수행하기 위해 든 프로세싱 파워. 다음 포스팅에서 자세히 다룬다.
3. rows=11: 예상 결과 행 수
4. width=81: 한 행의 평균 바이트 크기(열 수와 데이터 타입에 따라 결정된다)

여기서, rows와 width가 예상치라는 점이 흥미롭다. EXPLAIN 키워드로 조회하면 해당 쿼리는 실행되지 않고, PostgreSQL은 데이터에 접근하지 않는데, 그럼에도 예상치가 똑같이 나온다. 이 예상치는 어떻게 산출된 걸까?

pg_stats: 통계 기반의 Planner

Planner는 PostgreSQL 내부의 pg_stats 테이블을 참조해, 여기에 있는 통계 정보를 기반으로 예측을 수행한다.

sql
SELECT * FROM pg_stats
WHERE tablename = 'users';

pg_stats 테이블을 보면, PostgreSQL은 r테이블들과 컬럼에 대한 정보를 보관하고 있으며 이를 근거로 예상치를 산출한다는 것을 알 수 있다. 여기에는 각 컬럼의 null 비율이나 가장 자주 나오는 값, distinct 값 수 추정치 등이 들어있다.

cost에 대해서 보다 자세한 내용은 다음 포스팅에서 살펴보자.