Postgres EXPLAIN 느린 쿼리 분석기

--- name: postgres-explain-slow-query-analyzer description: Postgres EXPLAIN 및 EXPLAIN ANALYZE 플랜(텍스트 또는 JSON)을 읽고 쿼리가 느린 이유를 찾습니다 - 대형 릴레이션에 대한 순차 스캔, 대규모 행 추정치를 가진 중첩 루프, 잘못된 추정(추정치 대 실제값), 높은 버퍼 읽기, 디스크로 유출되는 Sort/Hash, 누락된 LIMIT 푸시다운, 실제 시간 기준 비용이 큰 노드. 수정 사항(인덱스, 재작성, 통계)의 우선순위를 매깁니다. 오프라인 표준 라이브러리 Python 분석기. --- # Postgres EXPLAIN 느린 쿼리 분석기 개발자와 DBA가 Postgres 쿼리 플랜을 읽을 수 있도록 돕습니다. 느린 쿼리는 SQL만 바라보고 고치는 경우가 드뭅니다. 플랜은 Postgres가 순차 스캔, 폭발적으로 증가한 중첩 루프, 잘못된 행 추정치, 디스크로 유출된 Sort, 또는 수 기가바이트의 버퍼를 읽은 Hash Join을 선택했는지 보여줍니다. `EXPLAIN` 또는 `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT|JSON)` 플랜을 받아 비용이 큰 노드의 이름을 지정하고, 왜 문제인지 설명하며, 구체적인 수정 사항의 우선순위를 매깁니다. 이 스킬은 플랜을 읽는 데 중점을 두며, 증거 없이 SQL을 처음부터 다시 작성하는 것이 아닙니다. 파괴적인 명령문은 실행하지 않습니다. 복제본에서 또는 롤백할 수 있는 트랜잭션에서 `EXPLAIN (ANALYZE, BUFFERS)`를 선호하며, ANALYZE가 실제로 쿼리를 실행한다는 점을 경고합니다. ## 이 스킬의 파일 - `scripts/explain_analyze.py` - 텍스트 또는 JSON EXPLAIN / EXPLAIN ANALYZE 출력을 파싱합니다. 실제 시간 또는 비용으로 노드 점수를 매기고 EX01-EX12(순차 스캔, 중첩 루프 폭증, 잘못된 추정, 디스크 정렬/해시, 높은 공유 읽기, 실행되지 않은 노드, 병렬 불균형)를 보고합니다. 제안된 수정 사항의 우선순위를 매깁니다(Python 3.8+ 표준 라이브러리만 사용). - `references/plan-nodes-and-buffers.md` - 노드 유형, 비용, 행, 루프, 버퍼 및 임시 파일을 읽는 방법. - `references/checks-and-codes.md` - 모든 발견 코드, 심각도 및 조치 사항, 입력 팁. - `templates/slow-query-review.md` - 플랜 발췌문과 우선순위가 매겨진 수정 사항을 포함하여 전달하는 검토 노트. - `examples/orders_by_email.explain.txt` - 느린 조회(순차 스캔 + 중첩 루프)에 대한 텍스트 EXPLAIN ANALYZE. - `examples/review-report.md` - 예제에 대한 실제 분석기 출력. ## 워크플로우 ### 1. 플랜 수집 Postgres 버전, SQL, 그리고 `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)`(또는 JSON)을 요청합니다. 프로덕션에서 ANALYZE가 안전하지 않으면 ANALYZE 없이 `EXPLAIN (BUFFERS)`를 받고 타이밍은 추정치일 뿐이라고 말합니다. Seq Scan이 의심스러워 보일 때 테이블 크기(`pg_stat_user_tables`, `pg_relation_size`)를 캡처합니다. ### 2. 분석기 실행 ``` python3 scripts/explain_analyze.py examples/orders_by_email.explain.txt python3 scripts/explain_analyze.py plan.json --json-out python3 scripts/explain_analyze.py plan.txt --min-ms 5 python3 scripts/explain_analyze.py --self-test ``` 종료 코드 0 = 높은 발견 없음, 1 = 높은 발견 있음, 2 = 잘못된 입력. 셸이 없으면 `references/plan-nodes-and-buffers.md`를 사용하여 플랜 트리를 하향식으로 살펴보고 `references/checks-and-codes.md`의 코드를 적용합니다. ### 3. 결과 읽기 - 높은 우선순위: EX01 선택적 필터가 있는 대형 릴레이션의 Seq Scan, EX03 실제 행 x 루프가 큰 Nested Loop, EX05 핫 노드에서 100배 이상의 추정 오차 비율, EX07 임시 파일로 유출된 Sort/Hash. - 중간 우선순위: EX02 인덱스 없는 조인 키, EX04 스캔 후 대부분의 행을 제거하는 Filter, EX06 높은 공유 읽기 버퍼, EX08 종이상 비용이 높아 보이지만 실행되지 않은 분기. - 낮음/정보: EX09 병렬 워커 불균형, EX10 실행 시간 대비 높은 계획 시간, EX11 LIMIT 푸시다운 안 됨, EX12 CTE MATERIALIZED로 전체 계산 강제. ### 4. 수정 사항 우선순위 지정 증거 기반 변경을 선호합니다. Filter/Join Filter와 일치하는 인덱스 추가 또는 조정, 통계 업데이트(`ANALYZE`), Nested Loop가 된 상관 서브쿼리 재작성, Sort/Hash가 유출되고 세트가 메모리에 맞을 때만 `work_mem` 증가, Postgres 12+에서 불필요하게 구체화된 경우 CTE를 `NOT MATERIALIZED`로 변경. 플랜에서 열 이름을 지정하지 않고 인덱스를 임의로 만들지 마십시오. ### 5. 전달 `templates/slow-query-review.md`를 사용하여 판정, 실제 시간과 버퍼가 있는 상위 비용 노드, 발견 목록, 간결한 우선순위 수정 목록을 반환합니다. ## 예시 결과 샘플 플랜에서 Nested Loop가 시간의 98%를 차지했습니다. `lower(email)`로 필터링된 `customers`의 외부 Seq Scan(표현식 인덱스 없음)이 48,210 루프를 실행한 내부 `orders_customer_id_idx` Index Scan을 공급했습니다. 외부 스캔의 추정 행은 42였지만 실제는 48,210(추정 오차)이었습니다. `orders`의 공유 읽기 버퍼가 높았습니다. 우선순위 수정: `lower(email)`에 대한 표현식 인덱스, 그 다음 조인 선택도 확인, `ANALYZE customers`. ## 규칙 - 모든 주장은 플랜의 노드(유형, 비용/실제, 행, 루프, 버퍼)에 근거해야 합니다. - 추정 전용 EXPLAIN과 ANALYZE를 구분하고 비용을 밀리초로 취급하지 마십시오. - 측정 없이 전역적으로 순차 스캔을 비활성화하거나 random_page_cost를 비현실적인 값으로 설정하는 것을 권장하지 않습니다. - EXPLAIN ANALYZE가 쿼리(트리거, 쓰기, 잠금)를 실행한다고 경고합니다. - 이는 플랜 읽기 보조 도구이며 `pg_stat_statements` 모니터링이나 전체 스키마 검토를 대체하지 않습니다. FILE:examples/orders_by_email.explain.txt QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1) Buffers: shared hit=1204 read=88440 -> Seq Scan on public.customers c (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1) Filter: (lower((email)::text) = 'ada@example.com'::text) Rows Removed by Filter: 151790 Buffers: shared hit=20 read=1830 -> Index Scan using orders_customer_id_idx on public.orders o (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210) Index Cond: (customer_id = c.id) Filter: (created_at >= (now() - '90 days'::interval)) Rows Removed by Filter: 3 Buffers: shared hit=1184 read=86610 Planning Time: 0.412 ms Execution Time: 912.610 ms FILE:examples/review-report.md # Postgres EXPLAIN 분석 소스: `examples/orders_by_email.explain.txt` 실행 시간: 912.61 ms 계획 시간: 0.412 ms **판정: 개선 필요** - 높은 심각도 발견 3건. ## 시간(또는 비용) 기준 상위 노드 | 노드 | 릴레이션 | 실제 ms (x 루프) | 비용 | 추정 행 -> 실제 행 | 루프 | 공유 읽기 | |------|----------|---------------------|------|-----------------|-------|-------------| | Nested Loop | - | 912.4 | 184220 | 12 -> 96 | 1 | 88440 | | Index Scan | public.orders | 771.4 | 4 | 8 -> 0 | 48210 | 86610 | | Seq Scan | public.customers | 88.2 | 1850 | 42 -> 48210 | 1 | 1830 | ## 발견 사항 | 심각도 | 코드 | 세부 정보 | |-----|------|--------| | 높음 | EX01 | 선택적 Filter((lower((email)::text) = 'ada@example.com'::text))가 있는 public.customers의 Seq Scan; 151790 행 제거됨 | | 높음 | EX03 | Nested Loop 하위 Index Scan이 loops=48210(실제 행/루프~0) 실행되어 약 771.4 ms 소비 | | 높음 | EX05 | public.customers Seq Scan의 추정 오차: 추정 행=42 실제=48210(비율~1148배) | | 중간 | EX02 | Nested Loop에 public.customers의 내부/외부 Seq Scan이 있음 | | 중간 | EX06 | Nested Loop ? 공유 읽기=88440 블록 | | 중간 | EX06 | public.orders Index Scan 공유 읽기=86610 블록 | ## 우선순위 수정 제안 1. Seq Scan Filter와 일치하는 btree/표현식 인덱스를 추가합니다(lower()와 같은 함수 래퍼 확인). 2. Nested Loop 외부 행을 줄이거나 내부 조인 키에 인덱스를 추가합니다. ANALYZE 후 조인 순서를 다시 확인합니다. 3. 관련 테이블에 ANALYZE를 실행합니다. 상관된 열에 대한 확장 통계를 고려합니다. 4. 더 나은 인덱스 또는 더 일찍 필터링하여 Nested Loop가 힙을 다시 읽지 않도록 하여 공유 읽기를 줄입니다. FILE:references/checks-and-codes.md # 검사 및 코드 | 코드 | 심각도 | 의미 | 조치 사항 | |------|----------|---------|------------| | EX01 | 높음 | 선택적 Filter와 큰 실제 행 또는 높은 실제 시간이 있는 릴레이션의 Seq Scan | 필터와 일치하는 표현식 또는 btree 인덱스; 일치하는 표현식 인덱스 없이 열을 함수로 감싸지 않도록 함 | | EX02 | 중간 | 조인 조건이 인덱스되지 않은 것 같음(내부 Seq Scan이 있는 Nested Loop 또는 조인 키에 Filter가 있는 높은 루프) | 내부 조인 키에 인덱스 추가; 인덱스를 무효화하는 캐스트가 없도록 데이터 유형 일치 확인 | | EX03 | 높음 | 매우 높은 루프 * 실제 행(행 폭증)이 있는 Nested Loop | 먼저 외부 행을 줄임; Hash/Merge Join 고려; NL 선택을 유도하는 잘못된 추정 수정 | | EX04 | 중간 | Filter가 스캔 후 대부분의 행을 제거함(입력 행 >> 출력 행) | 조건자를 인덱스 조건으로 푸시; 플래너가 인덱스를 사용할 수 있도록 재작성 | | EX05 | 높음 | 추정 오차: 실제/추정 또는 추정/실제 >= 100인 중요 노드 | ANALYZE; 상관된 열에 대한 확장 통계; 조건자의 비불변 함수 피함 | | EX06 | 중간 | 핫 노드의 높은 공유 읽기 버퍼 | 힙 페치를 줄이기 위한 캐싱/인덱스; Nested Loop가 동일한 페이지를 다시 읽는지 확인 | | EX07 | 높음 | Sort 또는 Hash가 임시 파일로 유출됨(외부 병합 / temp 기록과 함께 Batches > 1) | 정렬/해시 전에 행 줄임; 이 쿼리 세션에 대해 신중하게 work_mem 증가; 정렬을 피하기 위한 지원 인덱스 추가 | | EX08 | 낮음 | 노드가 실행되지 않음(실제 루프=0)但 추정 비용을 지배함 | 런타임에는 무시; ANALYZE 없이 플랜을 비교할 때 유용 | | EX09 | 낮음 | 워커 기여도가 고르지 않은 병렬 쿼리 | parallel_* 설정 및 gather가 가치가 있는지 확인 | | EX10 | 정보 | 실행 시간 대비 큰 계획 시간 | 준비된 명령문; 일회성 계획 단순화; 파티션 프루닝 문제 | | EX11 | 중간 | 대규모 세트의 전체 Sort 또는 전체 집계 위에 LIMIT | 순서/필터가 조기 중지가 가능한 인덱스 스캔을 허용하도록 재작성 | | EX12 | 중간 | MATERIALIZED로 전체 평가를 강제하는 CTE Scan | WITH ... AS NOT MATERIALIZED(PG12+) 또는 CTE 인라인 | ## 입력 팁 - 텍스트 플랜: 전체 트리를 붙여넣고 들여쓰기를 제거하지 마십시오. - JSON 플랜: 하나의 플랜 객체가 있는 Postgres `FORMAT JSON` 배열; 스크립트는 원시 배열 또는 `{Plan: ...}`을 허용합니다. - `QUERY PLAN`과 같은 선택적 헤더 줄 또는 점선 규칙은 무시됩니다. FILE:references/plan-nodes-and-buffers.md # 플랜 노드 및 버퍼 ## 유용한 플랜 캡처 ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) <query>; -- 또는 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>; ``` 프로덕션에서는 핫 대기 서버를 선호하거나 롤백할 수 있는 트랜잭션으로 쓰기를 감쌉니다. `ANALYZE`는 명령문을 실행합니다. ## 텍스트 노드 줄 읽기 예: `Seq Scan on public.customers (cost=0.00..1550.00 rows=42 width=64) (actual time=0.020..85.110 rows=48210 loops=1)` - 노드 유형 및 릴레이션 - `cost=startup..total`(플래너 단위, ms 아님) - `rows` = 추정 행; ANALYZE에서는 `actual time`, `rows`, `loops`도 있음 - 해당 노드의 총 시간 ~= (실제 시간 총계) * 루프 (하위 트리 기여 패턴); 리프 시간은 부모가 자식을 포함할 때 부모보다 명확함 ## 일반적인 노드 유형 - **Seq Scan**: 전체 힙을 읽음. 작은 테이블이나 대부분의 행이 일치할 때는 괜찮음. 큰 테이블의 선택적 Filter에서는 문제가 됨. - **Index Scan / Index Only Scan / Bitmap Heap Scan**: 선택적일 때 좋음. 재확인이 많은 Bitmap Heap은 여전히 느릴 수 있음. - **Nested Loop**: 각 외부 행에 대해 내부를 실행. 외부 행이 적고 내부가 인덱스되면 훌륭함. 외부가 크면 치명적. - **Hash Join / Merge Join**: 더 큰 세트에 더 적합. 유출되는 Hash(`Batches` > 1, temp 기록)를 주시. - **Sort**: `Sort Method: external merge`는 디스크 유출을 의미. `work_mem` 또는 더 일찍 행 줄이기를 고려. - **Aggregate / GroupAggregate / HashAggregate**: 그룹화 열이 인덱스되거나 사전 필터링되었는지 확인. - **CTE Scan**: Postgres 12+는 CTE를 MATERIALIZE할 수 있음. 필터 푸시다운을 차단할 수 있음. - **Limit**: 저렴한 경로 위에 있어야 함. Limit가 모든 것의 전체 정렬 위에 있으면 기준을 아래로 푸시. ## 버퍼(BUFFERS 옵션) - `shared hit` = 공유 버퍼에서 찾음(저렴) - `shared read` = OS/디스크에서 버퍼로 읽음(캐시 미스 시 비쌈) - `temp read/written` = work_mem 유출 파일 반복되는 Nested Loop 내부 측에서 `shared read`가 큰 노드는 종종 더 나은 외부 필터 또는 인덱스가 필요합니다. ## 행 추정 오차 `rows`(추정)를 실제 `rows`와 비교합니다. 비용이 큰 노드에서 10배(특히 100배+) 이상의 비율은 잘못된 통계, 오래된 `ANALYZE`, 상관된 열 또는 인덱스된 열을 감싸는 함수(`lower(email)`)를 의미합니다. ## 이 스킬이 하지 않는 일 `pg_stat_statements`, auto_explain 또는 스키마만으로 시작하는 인덱스 설계 검토를 대체하지 않습니다. 하나의 플랜에서 시작하여 해당 플랜을 설명합니다. FILE:scripts/explain_analyze.py #!/usr/bin/env python3 """Postgres EXPLAIN / EXPLAIN ANALYZE 플랜 분석기(표준 라이브러리만, Python 3.8+). 텍스트 또는 JSON 플랜을 파싱하고, 비용이 큰 노드의 점수를 매기며, 플랜 읽기에 초점을 맞춘 EX01-EX12 발견 사항을 보고합니다(순차 스캔, 중첩 루프 폭증, 잘못된 추정, 버퍼 압력, 디스크 유출). 사용법: explain_analyze.py PLAN.txt|.json [--min-ms N] [--json-out] explain_analyze.py --self-test 종료: 0 = 높은 발견 없음, 1 = 높은 발견 있음, 2 = 잘못된 입력. """ import argparse, json, re, sys SEV = {"high": 0, "medium": 1, "low": 2, "info": 3} NODE_RE = re.compile( r"^(?P<indent>\s*)(?:->\s*)?(?P<type>.+?)" r"(?=\s+using\s+|\s+on\s+|\s*\(cost=)" r"(?:\s+using\s+(?P<idx>[\w.]+))?" r"(?:\s+on\s+(?P<rel>[\w.]+))?" r"(?:\s+(?P<alias>\w+))?" r"\s*\(cost=(?P<cstart>[\d.]+)\.\.(?P<ctotal>[\d.]+)\s+rows=(?P<erows>[\d]+)\s+width=(?P<width>[\d]+)\)" r"(?:\s*\(actual time=(?P<astart>[\d.]+)\.\.(?P<atotal>[\d.]+)\s+rows=(?P<arows>[\d]+)\s+loops=(?P<loops>[\d]+)\))?" ) BUF_RE = re.compile( r"Buffers:\s*(?:shared hit=(?P<shit>\d+))?" r"(?:\s*read=(?P<sread>\d+))?" r"(?:.*?temp read=(?P<tread>\d+))?" r"(?:.*?written=(?P<twrite>\d+))?", re.I, ) FILTER_RE = re.compile(r"^\s+Filter:\s*(.+)$") REMOVED_RE = re.compile(r"Rows Removed by Filter:\s*(\d+)") SORT_RE = re.compile(r"Sort Method:\s*(.+)$", re.I) HASH_BATCH_RE = re.compile(r"Batches:\s*(\d+)", re.I) PLAN_TIME_RE = re.compile(r"Planning Time:\s*([\d.]+)\s*ms", re.I) EXEC_TIME_RE = re.compile(r"Execution Time:\s*([\d.]+)\s*ms", re.I) class InputError(Exception): pass def parse_text(text): lines = text.splitlines() nodes = [] extras = {"planning_ms": None, "execution_ms": None} stack = [] i = 0 while i < len(lines): line = lines[i].rstrip() if not line or set(line) <= set("-=") or line.strip() == "QUERY PLAN": i += 1 continue m = PLAN_TIME_RE.search(line) if m: extras["planning_ms"] = float(m.group(1)); i += 1; continue m = EXEC_TIME_RE.search(line) if m: extras["execution_ms"] = float(m.group(1)); i += 1; continue m = NODE_RE.match(line) if m: raw_indent = re.match(r"^(\s*)", line).group(1) ind = len(raw_indent) node = { "type": m.group("type").strip(), "rel": m.group("rel"), "idx": m.group("idx"), "cost_start": float(m.group("cstart")), "cost_total": float(m.group("ctotal")), "est_rows": int(m.group("erows")), "width": int(m.group("width")), "act_start": float(m.group("astart")) if m.group("astart") else None, "act_total": float(m.group("atotal")) if m.group("atotal") else None, "act_rows": int(m.group("arows")) if m.group("arows") else None, "loops": int(m.group("loops")) if m.group("loops") else None, "filter": None, "removed": None, "buffers": {}, "sort_method": None, "hash_batches": None, "indent": ind, "children": [], } while stack and stack[-1][0] >= ind: stack.pop() if stack: stack[-1][1]["children"].append(node) else: nodes.append(node) stack.append((ind, node)) j = i + 1 while j < len(lines): pl = lines[j] if not pl.strip(): break if NODE_RE.match(pl) or PLAN_TIME_RE.search(pl) or EXEC_TIME_RE.search(pl): break if not pl.startswith(" ") and not pl.startswith("\t"): break fm = FILTER_RE.match(pl) if fm: node["filter"] = fm.group(1).strip() rm = REMOVED_RE.search(pl) if rm: node["removed"] = int(rm.group(1)) bm = BUF_RE.search(pl) if bm: node["buffers"] = {k: int(bm.group(k)) for k in ("shit", "sread", "tread", "twrite") if bm.group(k)} sm = SORT_RE.search(pl) if sm: node["sort_method"] = sm.group(1).strip() hb = HASH_BATCH_RE.search(pl) if hb: node["hash_batches"] = int(hb.group(1)) j += 1 i = j continue i += 1 if not nodes: raise InputError("플랜 노드를 찾을 수 없습니다(cost=... rows=...가 있는 EXPLAIN 텍스트 필요)") return nodes, extras def parse_json_plan(obj): if isinstance(obj, list): if not obj: raise InputError("빈 JSON 플랜 배열") obj = obj[0] if not isinstance(obj, dict): raise InputError("JSON 플랜은 객체 또는 객체 배열이어야 합니다") plan = obj.get("Plan") or obj.get("plan") if not plan: raise InputError("JSON에 Plan 객체 누락") extras = { "planning_ms": obj.get("Planning Time"), "execution_ms": obj.get("Execution Time"), } def conv(p, indent=0): node = { "type": p.get("Node Type", "Unknown"), "rel": p.get("Relation Name") or p.get("Relation"), "idx": p.get("Index Name"), "cost_start": float(p.get("Startup Cost", 0)), "cost_total": float(p.get("Total Cost", 0)), "est_rows": int(p.get("Plan Rows", 0)), "width": int(p.get("Plan Width", 0)), "act_start": p.get("Actual Startup Time"), "act_total": p.get("Actual Total Time"), "act_rows": p.get("Actual Rows"), "loops": p.get("Actual Loops"), "filter": p.get("Filter"), "removed": p.get("Rows Removed by Filter"), "buffers": {}, "sort_method": p.get("Sort Method"), "hash_batches": p.get("Hash Batches") or p.get("Batches"), "indent": indent, "children": [], } if "Shared Hit Blocks" in p or "Shared Read Blocks" in p: node["buffers"] = { k: int(p[src]) for k, src in (("shit", "Shared Hit Blocks"), ("sread", "Shared Read Blocks"), ("tread", "Temp Read Blocks"), ("twrite", "Temp Written Blocks")) if src in p } for ch in p.get("Plans") or []: node["children"].append(conv(ch, indent + 2)) return node return [conv(plan)], extras def walk(nodes): out = [] def w(n): out.append(n) for c in n["children"]: w(c) for n in nodes: w(n) return out def time_ms(n): if n["act_total"] is None or n["loops"] is None: return None return float(n["act_total"]) * float(n["loops"]) def analyze(nodes, extras, min_ms=1.0): findings = [] flat = walk(nodes) def add(sev, code, msg, node=None): findings.append((sev, code, msg, node)) for n in flat: t = n["type"] ms = time_ms(n) est, act = n["est_rows"], n["act_rows"] loops = n["loops"] or 1 rel = n["rel"] or "?" if t.startswith("Seq Scan") and n.get("filter") and (n.get("removed") or 0) > 1000: if (act or 0) > 1000 or (ms or 0) >= min_ms * 10: add("high", "EX01", "선택적 Filter(%s)가 있는 %s의 Seq Scan; %s 행 제거됨" % (rel, n["filter"], n.get("removed")), n) if t.startswith("Nested Loop"): for c in n["children"]: if c["type"].startswith("Seq Scan"): add("medium", "EX02", "Nested Loop에 %s의 내부/외부 Seq Scan이 있음" % (c.get("rel") or "?"), c) if t.startswith("Nested Loop"): for c in n["children"]: cl = c.get("loops") or 0 if cl >= 10000 and (time_ms(c) or 0) >= min_ms * 20: add("high", "EX03", "Nested Loop 하위 %s가 loops=%d(실제 행/루프~%s) 실행되어 약 %.1f ms 소비" % ( c["type"], cl, c.get("act_rows"), time_ms(c) or 0), c) if n.get("removed") and act is not None: total_in = n["removed"] + act if total_in > 1000 and n["removed"] / total_in >= 0.9: add("medium", "EX04", "%s %s의 Filter가 행의 %.0f%%를 제거함(%d 제거, %d 유지)" % ( t, rel, 100.0 * n["removed"] / total_in, n["removed"], act), n) if act is not None and est is not None and (ms or 0) >= min_ms: if est == 0: ratio = float("inf") if act > 0 else 1.0 else: ratio = max(act / max(est, 1), est / max(act, 1)) if ratio >= 100: add("high", "EX05", "%s %s의 추정 오차: 추정 행=%d 실제=%d(비율~%.0f배)" % (t, rel, est, act, ratio if ratio != float("inf") else 9999), n) sread = n.get("buffers", {}).get("sread") or 0 if sread >= 5000 and (ms or 0) >= min_ms: add("medium", "EX06", "%s %s 공유 읽기=%d 블록" % (t, rel, sread), n) sm = (n.get("sort_method") or "").lower() if "external" in sm: add("high", "EX07", "Sort가 디스크로 유출됨(%s) - %s" % (n["sort_method"], t), n) if n.get("hash_batches") and n["hash_batches"] > 1 and (n.get("buffers", {}).get("twrite") or 0) > 0: add("high", "EX07", "Hash 유출: Batches=%d temp 기록=%s" % (n["hash_batches"], n["buffers"].get("twrite")), n) if loops == 0 and n["cost_total"] >= 1000: add("low", "EX08", "노드 %s가 실행되지 않았지만 비용=%.0f" % (t, n["cost_total"]), n) if t.startswith("Limit"): for c in n["children"]: if c["type"].startswith("Sort") and (c.get("act_rows") or c.get("est_rows") or 0) > 1000: add("medium", "EX11", "대규모 세트(%s 행)의 Sort 위에 LIMIT" % (c.get("act_rows") or c.get("est_rows")), n) if "CTE" in t and (ms or n["cost_total"] > 1000): add("medium", "EX12", "CTE Scan %s이(가) 구체화되었을 수 있습니다. 필터가 푸시다운되어야 하면 NOT MATERIALIZED를 고려하십시오" % (n.get("rel") or ""), n) if extras.get("planning_ms") and extras.get("execution_ms"): if extras["planning_ms"] > extras["execution_ms"] and extras["planning_ms"] > 20: add("info", "EX10", "계획 시간 %.1f ms > 실행 시간 %.1f ms" % (extras["planning_ms"], extras["execution_ms"]), None) findings.sort(key=lambda x: (SEV.get(x[0], 9), x[1])) return findings def report(nodes, extras, findings, src): flat = walk(nodes) lines = [] lines.append("# Postgres EXPLAIN 분석") lines.append("") lines.append("소스: `%s`" % src) if extras.get("execution_ms") is not None: lines.append("실행 시간: %s ms" % extras["execution_ms"]) if extras.get("planning_ms") is not None: lines.append("계획 시간: %s ms" % extras["planning_ms"]) high = sum(1 for f in findings if f[0] == "high") lines.append("") if high: lines.append("**판정: 개선 필요** - 높은 심각도 발견 %d건." % high) elif findings: lines.append("**판정: 참고 사항과 함께 OK** - 발견 %d건, 높은 심각도 없음." % len(findings)) else: lines.append("**판정: OK** - 임계값 이상의 발견 없음.") lines.append("") lines.append("## 시간(또는 비용) 기준 상위 노드") lines.append("") scored = [] for n in flat: ms = time_ms(n) scored.append((ms if ms is not None else -1, n["cost_total"], n)) scored.sort(key=lambda x: (x[0] if x[0] >= 0 else -1, x[1]), reverse=True) lines.append("| 노드 | 릴레이션 | 실제 ms (x 루프) | 비용 | 추정 행 -> 실제 행 | 루프 | 공유 읽기 |") lines.append("|------|----------|---------------------|------|-----------------|-------|-------------|") for ms, cost, n in scored[:12]: am = ("%.1f" % ms) if ms is not None and ms >= 0 else "-" ra = "%s -> %s" % (n["est_rows"], n["act_rows"] if n["act_rows"] is not None else "-") lines.append("| %s | %s | %s | %.0f | %s | %s | %s |" % ( n["type"], n["rel"] or "-", am, cost, ra, n["loops"] if n["loops"] is not None else "-", n.get("buffers", {}).get("sread", "-"))) lines.append("") lines.append("## 발견 사항") lines.append("") if not findings: lines.append("없음.") else: lines.append("| 심각도 | 코드 | 세부 정보 |") lines.append("|-----|------|--------|") for sev, code, msg, _ in findings: lines.append("| %s | %s | %s |" % (sev, code, msg.replace("|", "/"))) lines.append("") lines.append("## 우선순위 수정 제안") lines.append("") fixes = [] codes = {f[1] for f in findings} if "EX01" in codes: fixes.append("Seq Scan Filter와 일치하는 btree/표현식 인덱스를 추가합니다(lower()와 같은 함수 래퍼 확인).") if "EX03" in codes or "EX02" in codes: fixes.append("Nested Loop 외부 행을 줄이거나 내부 조인 키에 인덱스를 추가합니다. ANALYZE 후 조인 순서를 다시 확인합니다.") if "EX05" in codes: fixes.append("관련 테이블에 ANALYZE를 실행합니다. 상관된 열에 대한 확장 통계를 고려합니다.") if "EX07" in codes: fixes.append("Sort/Hash 전에 행을 줄이거나, 측정 후 이 세션에 대해서만 work_mem을 올립니다.") if "EX06" in codes: fixes.append("더 나은 인덱스 또는 더 일찍 필터링하여 Nested Loop가 힙을 다시 읽지 않도록 하여 공유 읽기를 줄입니다.") if "EX11" in codes: fixes.append("LIMIT가 전체 세트를 정렬하는 대신 정렬된 인덱스 스캔에서 중지할 수 있도록 재작성합니다.") if "EX12" in codes: fixes.append("WITH ... AS NOT MATERIALIZED를 시도하거나 CTE를 인라인하여 필터가 푸시다운되도록 합니다.") if not fixes: fixes.append("임계값 기준 높은 우선순위 구조적 수정 사항 없음. pg_stat_statements 감시 하에 플랜을 유지하십시오.") for i, f in enumerate(fixes, 1): lines.append("%d. %s" % (i, f)) lines.append("") return "\n".join(lines) + "\n" def load_plan(path): raw = open(path, encoding="utf-8").read() if path.endswith(".json") or raw.lstrip().startswith(("[", "{")): try: obj = json.loads(raw) except json.JSONDecodeError as e: raise InputError("잘못된 JSON: %s" % e) return parse_json_plan(obj) return parse_text(raw) def self_test(): tests = [] def t(name, ok): tests.append((name, bool(ok))) sample = """Nested Loop (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1) Buffers: shared hit=1204 read=88440 -> Seq Scan on public.customers c (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1) Filter: (lower((email)::text) = 'ada@example.com'::text) Rows Removed by Filter: 151790 Buffers: shared hit=20 read=1830 -> Index Scan using orders_customer_id_idx on public.orders o (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210) Index Cond: (customer_id = c.id) Buffers: shared hit=1184 read=86610 Planning Time: 0.412 ms Execution Time: 912.610 ms """ nodes, extras = parse_text(sample) t("루트 중첩 루프 파싱", nodes[0]["type"] == "Nested Loop") t("두 자식", len(nodes[0]["children"]) == 2) t("실행 시간", extras["execution_ms"] == 912.610) findings = analyze(nodes, extras) codes = {f[1] for f in findings} t("EX01 선택적 순차 스캔", "EX01" in codes) t("EX03 중첩 루프 폭증", "EX03" in codes) t("EX05 추정 오차", "EX05" in codes) t("EX06 공유 읽기", "EX06" in codes) r = report(nodes, extras, findings, "t") t("보고서 판정 개선 필요", "NEEDS WORK" in r) tiny = """Seq Scan on public.t (cost=0.00..1.05 rows=5 width=4) (actual time=0.002..0.003 rows=5 loops=1) Execution Time: 0.020 ms """ nodes2, ex2 = parse_text(tiny) f2 = analyze(nodes2, ex2) t("작은 테이블 높은 심각도 없음", not any(x[0] == "high" for x in f2)) spill = """Sort (cost=1000..1100 rows=10000 width=8) (actual time=50..60 rows=10000 loops=1) Sort Method: external merge Disk: 4096kB """ ns, es = parse_text(spill) fs = analyze(ns, es) t("EX07 외부 정렬", any(x[1] == "EX07" for x in fs)) j = [{"Plan": {"Node Type": "Limit", "Startup Cost": 0, "Total Cost": 10, "Plan Rows": 10, "Plan Width": 4, "Actual Startup Time": 0.1, "Actual Total Time": 5.0, "Actual Rows": 10, "Actual Loops": 1, "Plans": [{"Node Type": "Sort", "Startup Cost": 0, "Total Cost": 9, "Plan Rows": 5000, "Plan Width": 4, "Actual Startup Time": 0.1, "Actual Total Time": 4.9, "Actual Rows": 5000, "Actual Loops": 1, "Sort Method": "quicksort"}]}, "Planning Time": 1.0, "Execution Time": 5.1}] nj, ej = parse_json_plan(j) fj = analyze(nj, ej) t("JSON limit+sort -> EX11", any(x[1] == "EX11" for x in fj)) try: parse_text("플랜 아님") ok = False except InputError: ok = True t("잘못된 입력 거부", ok) for name, ok in tests: print(("PASS " if ok else "FAIL ") + name) passed = sum(ok for ok in tests) print("%d/%d 통과" % (passed, len(tests))) return 0 if passed == len(tests) else 1 def main(argv=None): ap = argparse.ArgumentParser(description="Postgres EXPLAIN / EXPLAIN ANALYZE 플랜 분석.") ap.add_argument("plan", nargs="?") ap.add_argument("--min-ms", type=float, default=1.0) ap.add_argument("--json-out", action="store_true") ap.add_argument("--self-test", action="store_true") a = ap.parse_args(argv) if a.self_test: return self_test() if not a.plan: ap.print_usage(sys.stderr) return 2 try: nodes, extras = load_plan(a.plan) findings = analyze(nodes, extras, min_ms=a.min_ms) except (OSError, InputError) as e: print("오류: %s" % e, file=sys.stderr) return 2 if a.json_out: print(json.dumps({ "execution_ms": extras.get("execution_ms"), "planning_ms": extras.get("planning_ms"), "findings": [{"severity": s, "code": c, "detail": m} for s, c, m, _ in findings], }, indent=2)) else: sys.stdout.write(report(nodes, extras, findings, a.plan)) return 1 if any(f[0] == "high" for f in findings) else 0 if __name__ == "__main__": sys.exit(main()) FILE:templates/slow-query-review.md # 느린 쿼리 플랜 검토 - 날짜: - 데이터베이스 / Postgres 버전: - 환경(프로덕션 복제본 / 스테이징): - 쿼리 이름 또는 티켓: ## SQL(간략) ```sql ``` ## 판정 - 전체: - 주요 시간 소비원: ## 상위 노드 | 노드 | 실제 시간 / 비용 | 추정 행 -> 실제 행 | 루프 | 버퍼 | 참고 | |------|--------------------|-----------------|-------|---------|-------| | | | | | | | ## 발견 사항 | 코드 | 심각도 | 세부 정보 | |------|----------|--------| | | | | ## 우선순위 수정 1. 2. 3. ## 후속 조치 - [ ] 변경 후 `EXPLAIN (ANALYZE, BUFFERS)` 캡처 - [ ] 더 추가하기 전에 `pg_stat_user_indexes` / 사용되지 않는 인덱스 확인 - [ ] 기본 테이블의 ANALYZE가 오래되었는지 기록