Slate Postgres 질의 성능
이 장은 Slate의 PostgreSQL 질의를 다룹니다. 새 앱에서는 온톨로지 연결을 우선 검토하고, 기존 Postgres 경로는 데이터 모델, 질의와 의존 관계를 함께 조정합니다.
측정 기준
약 1,000만 행 미만의 데이터에서 PostgreSQL 질의 실행시간 500ms 이하를 설계 목표로 삼을 수 있습니다. 데이터 모델과 질의 조건에 따라 달라지는 목표이며 보장 시간이나 입력 한도는 아닙니다.
반복 사용 성능을 비교할 때에는 여러 번 실행해 캐시가 준비된 상태의 통계를 수집합니다. 처음 읽는 경우와 캐시를 사용한 결과를 구분하여 비교합니다.
EXPLAIN 읽기
EXPLAIN은 실행 계획을 반환합니다. 계획의 노드는 작업 단위이며 들여쓰기된 자식 결과를 부모가 사용합니다. 위쪽에 표시된 노드는 뒤에 수행하는 상위 작업입니다.
| 항목 | 의미 |
|---|---|
| 노드 | Index Scan 같은 작업입니다. 병목 후보를 구분합니다. |
| cost | 시작 비용과 전체 완료 비용의 추정값입니다. 실제 밀리초가 아니라 통계에 기반한 I/O와 CPU 작업량입니다. |
| rows | 노드가 반환할 행 수의 추정값입니다. |
| width | 반환할 행의 평균 크기 추정값이며 bytes 단위입니다. |
예를 들어 cost=86.83..4577.07 rows=2368 width=10은 위 값들을 표현하는 형식입니다. Cost는 순차/임의 접근, 행과 연산자 처리 및 인덱스 항목 처리 등을 반영하여 계획을 고르는 데 사용합니다.
EXPLAIN ANALYZE
ANALYZE를 붙이면 실제 질의를 실행합니다. 시간 초과가 발생하는 질의의 계획만 먼저 보고 싶다면 ANALYZE 없이 EXPLAIN을 사용합니다. 변경 명령에 ANALYZE를 붙이면 변경도 실제로 실행하므로 주의합니다.
| 항목 | 의미 |
|---|---|
| actual time | 시작까지 걸린 시간과 노드 완료까지 걸린 실제 시간이며 ms입니다. |
| rows | 실제로 반환한 행 수입니다. |
| loops | 노드 실행 횟수입니다. time과 rows는 한 실행당 평균입니다. |
actual time=10.313..12.530 rows=150 loops=10처럼 표시하면 해당 노드의 총 시간은 평균 시간과 loops를 함께 봅니다. 추정 rows와 실제 rows의 차이도 계획 선택의 원인을 찾는 단서입니다.
계획의 작업 종류
Scan
| 종류 | 특성과 검토 지점 |
|---|---|
| Sequential Scan | 테이블을 순서대로 읽으며 일반적으로 시작 비용이 낮고 각 블록을 한 번 읽습니다. 정렬된 결과를 보장하지 않습니다. |
| Index Scan | 인덱스의 일치 항목을 찾고 테이블 행을 읽습니다. 선택도가 좋으면 유용하지만 임의 접근과 추가 I/O 및 같은 블록의 반복 방문이 생길 수 있습니다. B-tree의 순서를 활용할 수 있습니다. |
| Bitmap Index/Heap Scan | 인덱스의 행 위치를 모아 페이지 위치 순서로 읽습니다. 여러 인덱스를 결합할 수 있고 IN이나 ANY 및 많은 일치 항목에서 선택될 수 있습니다. 준비 시간이 들며 출력 순서를 보장하지 않습니다. |
| Index Only Scan | 필요한 열이 인덱스에 있으면 테이블 접근을 줄일 수 있습니다. MVCC 가시성 확인 조건에 따라 heap 접근이 여전히 필요할 수 있습니다. |
인덱스가 존재한다고 항상 더 빠른 것은 아닙니다. 결과의 정렬이 필요하면 SQL의 ORDER BY로 요구합니다.
Join
조인은 두 입력을 결합하며 여러 테이블에서는 중간 결과도 다음 조인의 입력이 됩니다.
| 종류 | 특성과 조건 |
|---|---|
| Nested Loop | 바깥 입력의 각 행마다 안쪽 입력을 찾습니다. 첫 결과를 빨리 만들 수 있으며 적은 외부 행과 효율적인 안쪽 인덱스에 적합할 수 있습니다. 반복할 안쪽 작업이 느리면 비용이 커집니다. CROSS JOIN과 부등 조건을 처리하는 경로입니다. |
| Merge Join | 정렬된 두 입력을 동등 조건으로 합칩니다. 큰 정렬 입력에 유용하지만 정렬이나 순서 있는 읽기가 필요합니다. |
| Hash Join | 한 입력의 hash table을 만들고 다른 입력으로 찾습니다. hash 가능한 동등 조건에 사용하며 보통 작은 쪽을 준비합니다. 초기 hash 작성과 메모리 사용이 필요하고 잘못된 크기 통계가 성능에 영향을 줍니다. |
한 방식이 항상 가장 빠르다고 순위를 정하지 않고 입력 크기, 정렬 상태, 인덱스와 추정/실제 행 수를 비교합니다.
집계와 기타 노드
| 노드 | 역할 |
|---|---|
| GroupAggregate | 정렬된 입력을 GROUP BY에 따라 집계하며 큰 중간 결과를 보관할 필요가 적습니다. |
| HashAggregate | 사전 정렬 없이 hash table로 그룹화합니다. 중간 결과 메모리가 필요하며 출력 순서는 의미가 없습니다. |
| Unique | 중복 제거입니다. |
| WindowAgg | window function 계산입니다. |
| Sort | ORDER BY, DISTINCT, GROUP BY, UNION 또는 Merge Join 등에 필요한 정렬입니다. work_mem 안에 들어오면 메모리에서 처리하고 넘으면 임시 파일로 내려갈 수 있습니다. |
| Limit | LIMIT와 OFFSET을 처리합니다. OFFSET으로 건너뛰는 행도 생성한 뒤 버립니다. 정렬과 결합하면 제한된 결과에 맞춘 방식이 선택될 수 있습니다. |
인덱스가 맞는 MIN/MAX 같은 요청에도 Limit 노드가 사용될 수 있습니다. 하위 scan에 전체 실행의 추정 비용이 표시되어도 상위 Limit가 일찍 멈추면 실제 실행량은 작을 수 있습니다.
질의 조정
SELECT와 중복 제거
화면에 필요한 열만 가져와 읽을 데이터와 전송량을 줄입니다. DISTINCT는 필요한 중복 제거의 의미를 확인하고 GROUP BY나 subquery 대안과 실제 계획을 비교합니다. DISTINCT가 언제나 더 느리다고 가정하지 않습니다.
JOIN
가능하면 a.id = b.person_id 같은 명확한 동등 조건을 사용합니다. 옵티마이저가 적합한 Hash/Merge Join 등을 고를 수 있게 합니다.
서브쿼리와 JOIN 중 의도를 더 잘 드러내는 형태를 검토합니다. 잘못된 조인으로 늘어난 행을 GROUP BY나 DISTINCT로 감추고 있지 않은지도 확인합니다. 큰 상관 서브쿼리는 반복 비용을 살피고, 존재 여부만 필요하면 첫 일치에서 멈출 수 있는 EXISTS를 고려합니다.
추정 테이블 크기가 크게 틀리면 통계 갱신과 vacuum 운영을 검토합니다.
WHERE와 GROUP BY
LIKE의 패턴, 큰 IN 목록과 조건 안의 함수 호출이 실제로 어떤 scan을 사용하는지 봅니다. 큰 값 목록은 JOIN이나 조건 구조 변경을 고려하고, 함수 조건은 해당 표현식에 맞는 인덱스 사용 가능성을 확인합니다.
여러 수준의 그룹 계산에는 PostgreSQL GROUPING SETS의 계약을 사용할 수 있습니다.
UNION
UNION은 중복 제거 작업을 수행하고 UNION ALL은 중복을 유지하여 행을 이어 붙입니다. 중복 제거가 필요 없다면 UNION ALL을 사용합니다. 같은 결합을 앱 로드마다 반복한다면 상류 transform에서 계산할 수도 있습니다. 두 연산의 결과 의미가 다르므로 성능만 보고 바꾸지 않습니다.
인덱스 검토
자주 쓰는 열에서 적은 비율을 선택하는 조건은 인덱스를 검토합니다. 복합 인덱스는 열 순서도 중요합니다. 작은 테이블이나 결과 비율이 큰 질의에는 순차 scan이 합리적일 수 있습니다. 5% 같은 비율은 검토용 참고값이지 고정된 분기 한도가 아닙니다.
다음 진단 질의는 통계 뷰에 접근할 수 있는 PostgreSQL 소스용입니다. public은 예제 대상 schema이며 실제 대상에 맞게 바꿉니다.
순차 scan이 많은 테이블
SELECT
relname,
seq_scan - COALESCE(idx_scan, 0) AS too_much_seq,
CASE
WHEN seq_scan - COALESCE(idx_scan, 0) > 0 THEN 'missing index?'
ELSE 'OK'
END,
pg_relation_size(relid) AS rel_size,
seq_scan,
idx_scan
FROM pg_stat_all_tables
WHERE schemaname = 'public'
AND pg_relation_size(relid) > 80000
ORDER BY too_much_seq DESC;
결과는 조사할 후보이며 순차 scan 횟수가 많다는 이유만으로 인덱스가 반드시 빠졌다고 판단하지 않습니다.
사용 기록이 없는 인덱스 후보
SELECT
indexrelid::regclass AS index,
relid::regclass AS table,
'DROP INDEX ' || indexrelid::regclass || ';' AS drop_statement
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
AND indisunique IS FALSE;
이 질의는 DROP INDEX 문자열을 만들 뿐 삭제를 실행하지 않습니다. 통계가 쌓인 기간과 초기화, 업무 주기 및 인덱스의 역할을 확인한 뒤 판단합니다. 실제 삭제 후보 목록으로 곧바로 사용하지 않습니다.
필요한 열을 포함하는 covering index는 Index Only Scan을 검토할 때 사용합니다. 전체 데이터 구성은 인덱스와 상류 계산, 앱의 의존 관계와 값 오류는 Slate 진단을 함께 확인합니다.