☰ Categories

Categories

Narrow down why a query is slow

Reads the execution plan to locate where time actually goes, before reaching for an index.

CategoryDevelopment › Data & databases
TagsAnalyzingCodeDeveloper
Prompt
Diagnose this slow query.

Work from the execution plan, in this order:
1. Find the node where actual time is concentrated. Quote it. *Start from measured cost, not from what looks suspicious in the SQL.*
2. Compare estimated rows to actual rows at each node. A large gap means the planner is working from bad statistics, and no index will fix that.
3. Classify the bottleneck: scan that should be a seek / wrong join order / join method mismatch / sort or hash spilling to disk / row count genuinely large.
4. Say where the fix belongs — **index, query rewrite, schema, or statistics** — and why not the others.
5. Give the change, with what to measure after.

Also check:
- Functions or casts applied to an indexed column in a predicate, which silently disable the index
- Leading-column mismatch against a composite index
- `SELECT *` pulling columns that force a lookup instead of an index-only read
- `OR` conditions that prevent index use
- Correlated subqueries running per row

Rules:
- *Do not recommend an index before step 4.* Most proposed indexes address a node that was not the bottleneck.
- If the plan does not explain the time, say so and name what to capture next.
- Note if the query is fast and the problem is that it runs too often — that is a different fix entirely.
After pasting, fill in the fields at the bottom (Query · Execution plan · Tables and indexes)

What this prompt does

The reflex is to add an index, which often is not the problem. This finds the expensive plan node first and decides whether the fix belongs in the index, the query, or the schema.

Model comparison

ChatGPT is the most accurate and measured diagnosis. Gemini is directionally sound but makes unsupported claims, while [C] provides no answer.

ChatGPTTop overall
48/ 50

＋ It ties measured plan costs to the bottleneck and index fix.

－ The sort timestamps could be mistaken for exclusive sort time.

Gemini
43/ 50

＋ It covers every requested diagnostic step and extra check.

－ It overclaims stats freshness, latency, and buffer reductions.

CriterionChatGPTGeminiLeader
Instruction following1010Tie
Accuracy97ChatGPT +29%
Specificity109ChatGPT +11%
Structure109ChatGPT +11%
Right length98ChatGPT +13%

Scored 1–10 by gpt-5.6-sol with model names hidden (2026-09-25). This is an AI review, not a measurement.

Read full answers

We gave three models the same input and copied their answers unedited. Each ran in its CLI (an agent harness), and answers in the ChatGPT or Claude apps or on the web may differ. Outputs are in Korean.

Input used
Query: SELECT o.order_id, o.created_at, o.status, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-08-01'
  AND o.created_at < '2026-09-01'
ORDER BY o.created_at DESC
LIMIT 50;

Execution plan: Limit  (cost=15234.12..15234.24 rows=50 width=96) (actual time=842.311..842.355 rows=50 loops=1)
  ->  Sort  (cost=15234.12..15298.45 rows=25732 width=96) (actual time=842.309..842.331 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (cost=1123.00..14532.88 rows=25732 width=96) (actual time=12.442..818.204 rows=26890 loops=1)
              Hash Cond: (o.customer_id = c.customer_id)
              ->  Seq Scan on orders o  (cost=0.00..13102.00 rows=25732 width=24) (actual time=0.015..782.601 rows=26890 loops=1)
                    Filter: ((status = 'PAID'::text) AND (created_at >= '2026-08-01 00:00:00'::timestamp) AND (created_at < '2026-09-01 00:00:00'::timestamp))
                    Rows Removed by Filter: 812340
              ->  Hash  (cost=850.00..850.00 rows=20000 width=80) (actual time=12.201..12.202 rows=20000 loops=1)
                    ->  Seq Scan on customers c  (cost=0.00..850.00 rows=20000 width=80) (actual time=0.005..5.332 rows=20000 loops=1)
Planning Time: 0.512 ms
Execution Time: 842.601 ms

Tables and indexes: orders (약 84만 행, 최근 6개월 데이터)
  order_id       bigint PK
  customer_id    bigint  -- FK customers.customer_id
  status         text    -- 'PAID','CANCELLED','PENDING','REFUNDED'
  created_at     timestamp
  total_amount   numeric

인덱스:
  orders_pkey (order_id)
  idx_orders_customer_id (customer_id)

customers (약 2만 행)
  customer_id  bigint PK
  name         text
  email        text

인덱스:
  customers_pkey (customer_id)
gpt-5.6-luna (codex CLI · 에이전트 하네스) · Run on 2026-09-24
  1. 시간 집중 노드

가장 오래 걸린 노드는 orders 전체 순차 스캔입니다.

Seq Scan on orders o ... (actual time=0.015..782.601 ms rows=26890)
Rows Removed by Filter: 812340

전체 약 84만 행을 읽어 26,890건만 남겼습니다. 이후 Hash Join까지 누적 818ms가 걸렸지만, 실질적인 원인은 이 순차 스캔입니다.

Sort는 병목이 아닙니다.

Sort Method: top-N heapsort Memory: 32kB
actual time=842.309..842.331

정렬은 32KB 메모리에서 수행됐고 디스크 spill도 없습니다.

  1. 예상 행과 실제 행 비교
노드 예상 행 실제 행 판단
orders 필터 25,732 26,890 약 4.5% 차이, 통계는 양호
customers 스캔 20,000 20,000 정확
Hash Join 25,732 26,890 약 4.5% 차이
Sort 입력 25,732 26,890 약 4.5% 차이
Limit 50 50 정확

큰 추정 오류가 없습니다. 따라서 이 문제는 잘못된 통계 때문에 생긴 것이 아니며, 통계 갱신만으로 해결되지 않습니다.

  1. 병목 분류

orders에 대해 seek할 수 있는데 전체를 scan하고 있는 문제입니다.

  • orders 84만 행을 순차 읽음
  • 필터 통과 행은 26,890건
  • 고객 테이블의 2만 행 Hash는 약 12ms뿐
  • top-N 정렬은 메모리 내에서 수행
  • 함수, 캐스팅, OR, 상관 서브쿼리는 없음
  • 기존 idx_orders_customer_id(customer_id)는 필터의 선두 조건과 맞지 않아 이 조회에 유용하지 않음

SELECT *는 아니며 필요한 컬럼만 선택하고 있습니다. 다만 새 인덱스가 order_id, customer_id까지 포함하면 orders 쪽 추가 테이블 조회를 줄일 수 있습니다. customers의 name, email은 고객 PK로 최대 50건만 lookup하면 됩니다.

  1. 수정 위치와 변경안

수정 위치는 인덱스입니다.

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at DESC)
INCLUDE (order_id, customer_id);

이 인덱스는 다음을 지원합니다.

  • status = 'PAID'로 선두 범위 탐색
  • created_at 날짜 범위 조건
  • ORDER BY created_at DESC
  • order_id, customer_id를 인덱스에서 제공

그러면 전체 orders scan 대신 조건에 맞는 최신 주문부터 읽고, LIMIT 50에서 조기 종료할 가능성이 큽니다. 고객 정보는 customers_pkey를 이용해 최대 50건만 조회하면 됩니다.

통계가 주원인이 아닌 이유는 예상 행과 실제 행이 이미 가깝기 때문입니다. 쿼리 rewrite도 필요하지 않고, 스키마 변경도 과합니다. 현재 문제는 데이터가 26,890건이라서가 아니라 84만 건을 먼저 읽는 데 있습니다.

  1. 변경 후 측정

인덱스 생성 후 다음을 비교하십시오.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

확인할 항목은 다음입니다.

  • Seq Scan on orders가 Index Scan 또는 Index Only Scan으로 바뀌었는지
  • Rows Removed by Filter가 크게 줄었는지
  • orders의 shared read 블록 수
  • 전체 실행 시간이 약 842ms에서 크게 감소했는지
  • 실제로 LIMIT 50 근처에서 읽기를 멈추는지

이 계획은 한 번 실행하는 것 자체가 느린 경우를 명확히 설명합니다. 반복 실행 빈도가 문제라면 별도로 호출 빈도, 캐시, 애플리케이션 batching을 조사해야 하지만, 제공된 계획만 보면 우선 원인은 실행당 과도한 순차 스캔입니다.

More in this category

Advanced Text Converter for Large Datasets
AI-powered data extraction and organization tool
AI2sql SQL Model — Query Generator
Backend Architect
base-R