EXPLAIN และ Query Plan

เมื่อ query ช้า การเดาเป็นการเสียเวลา PostgreSQL บอกคุณได้เป๊ะว่ามันตั้งใจรัน query อย่างไร และรันจริงอย่างไร เครื่องมือคือ EXPLAIN และการอ่านผลของมันเป็นทักษะด้านประสิทธิภาพที่มีประโยชน์ที่สุดอย่างเดียวที่คุณเรียนได้

ทุกอย่างด้านล่างรันบน PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop บน Mac, กันยายน 2026) ทุก plan คัดลอกมาจาก psql ไม่ได้พิมพ์เอง และตัวเลขเวลามาจาก Mac เครื่องนี้ (รันครั้งเดียว ความต่างเล็ก ๆ คือสัญญาณรบกวน)

planner เลือก plan

คุณเขียนว่า ต้องการอะไร; query planner ตัดสินว่า ทำอย่างไร สำหรับ query หนึ่งมี plan ที่เป็นไปได้มากมาย (scan ทั้ง table, ใช้ index นี้, ใช้ index นั้น, รวมสองอัน) และ planner ประมาณ cost ของแต่ละอัน แล้วเลือกอันที่ถูกที่สุด

flowchart LR
  Q["Your SQL query"] --> P["Query planner"]
  P --> E1["Estimate: Seq Scan cost"]
  P --> E2["Estimate: Index Scan cost"]
  P --> E3["Estimate: Bitmap Scan cost"]
  E1 --> C["Pick the cheapest plan"]
  E2 --> C
  E3 --> C
  C --> X["Execute chosen plan"]

planner เลือก plan อย่างไร

การประมาณ cost เหล่านั้นขึ้นกับสถิติของข้อมูลคุณ: table มีกี่ row, column มีกี่ค่าที่ไม่ซ้ำ, แต่ละค่าพบบ่อยแค่ไหน ถ้าสถิติเก่าหรือหยาบเกินไป planner เดาผิด ทั้งสองกรณีปรากฏด้านล่าง

ชุดข้อมูล

plan มีความหมายเฉพาะบนปริมาณข้อมูลที่สมจริง บทนี้จึงสร้าง table orders 500 000 row: ลูกค้า 10 000 คน สามสถานะ (เอียงโดยตั้งใจ) หนึ่ง order ทุก 90 วินาทีตั้งแต่ 1 มกราคม 2024 และ city ที่กำหนด country เสมอ มี index บน customer_id, created_at และ status

-- sql/setup.sql
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL,
  amount      numeric(10, 2) NOT NULL,
  city        int NOT NULL,
  country     int NOT NULL
);
INSERT INTO orders (customer_id, status, created_at, amount, city, country)
SELECT 1 + (g::bigint * 7919) % 10000,
       CASE WHEN g % 200 = 0 THEN 'refunded' WHEN g % 12 = 0 THEN 'pending' ELSE 'paid' END,
       timestamptz '2024-01-01 00:00:00+00' + g * interval '90 seconds',
       (g % 500) + 0.99,
       g % 100,
       (g % 100) % 10
FROM generate_series(1, 500000) AS g;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx  ON orders (created_at);
CREATE INDEX orders_status_idx      ON orders (status);
ANALYZE orders;
SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;

CREATE TABLE
INSERT 0 500000
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE
  status  | count
----------+--------
 paid     | 456667
 pending  |  40833
 refunded |   2500
(3 rows)

EXPLAIN: plan ที่ประมาณการ

EXPLAIN ธรรมดาวาง plan แต่ไม่รัน query จึงทันทีและปลอดภัยกับทุกอย่าง:

-- sql/explain-estimate.sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..189.92 rows=50 width=39)
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0)
         Index Cond: (customer_id = 42)
(4 rows)

อ่าน node แบบนี้: cost=4.81..189.92 คือ cost ที่ประมาณสำหรับคืน row แรก แล้วคืนทุก row ในหน่วยที่กำหนดเอง (การอ่าน page แบบ sequential หนึ่งครั้งคือ 1.0 จาก seq_page_cost); rows=50 คือจำนวน row ที่ planner คาดว่าจะออกมา; width=39 คือขนาดเฉลี่ยของ row เป็น byte plan เป็นต้นไม้ อ่านจากด้านในออกมา: node ที่เยื้องลึกที่สุด (Bitmap Index Scan) ทำงานก่อนและป้อนให้ parent (Bitmap Heap Scan)

EXPLAIN ANALYZE: เกิดอะไรขึ้นจริง

EXPLAIN ANALYZE รัน query จริงและเพิ่มตัวเลขที่วัดได้ข้างค่าประมาณ ตั้งแต่ PostgreSQL 18 มันแสดงการใช้ buffer เป็นค่าเริ่มต้น เวอร์ชันเก่าต้องเขียน BUFFERS เอง (release notes):

psql

-- sql/explain-analyze.sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

Node.js

const res = await pool.query(
  'EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42',
);
const plan = res.rows.map((r) => r['QUERY PLAN']);

Python

cur.execute(
    "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42"
)
plan = [row[0] for row in cur.fetchall()]

Go

rows, err := conn.Query(ctx,
    "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42")
defer rows.Close()
var plan []string
for rows.Next() {
  var line string
  rows.Scan(&line)
  plan = append(plan, line)
}

Rust

let rows = sqlx::query_scalar::<_, String>(
    "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42",
)
.fetch_all(&pool)
.await?;
let plan = rows;

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..189.92 rows=50 width=39) (actual time=0.054..0.267 rows=50.00 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=50
   Buffers: shared hit=50 read=3
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.041..0.041 rows=50.00 loops=1)
         Index Cond: (customer_id = 42)
         Index Searches: 1
         Buffers: shared read=3
 Planning:
   Buffers: shared hit=135
 Planning Time: 0.284 ms
 Execution Time: 0.304 ms
(12 rows)

ทีละบรรทัด:

• actual time=0.054..0.267: มิลลิวินาทีจนถึง row แรกและจนถึง row สุดท้าย ต่อ loop rows=50.00 คือ row จริง ต่อ loop แสดงทศนิยมตั้งแต่ PostgreSQL 18 (ค่าเฉลี่ยเมื่อ node วน loop) ค่าประมาณคือ rows=50 plan นี้ประมาณได้ดี
• loops=1: node นี้รันกี่ครั้ง ใน nested loop ตัวเลขของ node ด้านในเป็น ต่อ loop จึงต้องคูณ
• Recheck Cond และ Heap Blocks: exact=50: bitmap บอกว่า page ไหนมี row ที่ตรง executor ใช้เงื่อนไขซ้ำบน 50 page นั้น
• Buffers: shared hit=50 read=3: block ขนาด 8 kB ที่พบใน shared_buffers (hit) เทียบกับที่ดึงจากระบบปฏิบัติการหรือดิสก์ (read) เป็นตัววัด I/O ที่ตรงไปตรงมา
• Index Searches: 1: PG 18 แสดงว่ามีการลงไปใน index กี่ครั้ง
• Planning: Buffers และ Planning Time: ต้นทุนของการสร้าง plan เอง แยกจาก Execution Time

การส่ง statement เดียวกันจากโค้ดแอปคืน plan เป็น row ธรรมดา หนึ่งบรรทัดข้อความต่อหนึ่ง row แต่ละ driver รันหนึ่งครั้งกับชุดข้อมูลและรายงานจำนวนบรรทัดของ plan, ต้นบรรทัดแรก และบรรทัดสุดท้าย:

$ (each driver runs the EXPLAIN once and reports: number of plan lines, start of the first line, the last line)
Node.js (pg 8.23.0):    12 Bitmap Heap Scan on orders  (cost=4.81..189.92 rows=50 width ... Execution Time: 0.529 ms
Python (psycopg 3.3.6): 12 Bitmap Heap Scan on orders  (cost=4.81..189.92 rows=50 width ... Execution Time: 0.527 ms
Go (pgx v5.11.0):       12 Bitmap Heap Scan on orders  (cost=4.81..189.92 rows=50 width ... Execution Time: 0.287 ms
Rust (sqlx 0.9.0):      12 Bitmap Heap Scan on orders  (cost=4.81..189.92 rows=50 width ... Execution Time: 0.260 ms

ตัวเลือกอื่น

EXPLAIN (COSTS OFF) ตัดตัวเลข cost เมื่อสนใจแค่รูปร่าง VERBOSE เพิ่มรายการ column ที่ออกและชื่อที่มี schema และ SETTINGS แสดง setting ของ planner ที่ต่างจากค่าเริ่มต้น ทำให้ plan ที่แปะไว้ทำซ้ำได้ ตรงนี้ random_page_cost ที่ลดลง (ค่าที่มักแนะนำสำหรับ SSD ดูการตั้งค่า planner) เปลี่ยน query เดิมจาก bitmap scan เป็น index scan ธรรมดา:

-- sql/explain-options.sql
BEGIN;
SET LOCAL random_page_cost = 1.1;
EXPLAIN (COSTS OFF, SETTINGS, VERBOSE) SELECT id, amount FROM orders WHERE customer_id = 42;
ROLLBACK;

BEGIN
SET
                        QUERY PLAN
----------------------------------------------------------
 Index Scan using orders_customer_id_idx on public.orders
   Output: id, amount
   Index Cond: (orders.customer_id = 42)
 Settings: random_page_cost = '1.1'
(4 rows)

ROLLBACK

EXPLAIN ยังพิมพ์บล็อก JIT: ได้เมื่อ server คอมไพล์ expression เป็น machine code ซึ่งทำเฉพาะ query ที่ cost ที่ประมาณสูงกว่า jit_above_cost (ค่าเริ่มต้น 100000) image นี้มี JIT ใช้ได้ เมื่อบังคับ threshold เป็น 0 บน query ที่ถูก บล็อกจะปรากฏและเวลาคอมไพล์เป็นส่วนของยอดรวม:

-- sql/jit-available.sql
SHOW jit;
SELECT pg_jit_available();
-- teaching switch: pretend the query is expensive enough to be compiled
BEGIN;
SET LOCAL jit_above_cost = 0;
EXPLAIN (ANALYZE, BUFFERS OFF) SELECT sum(amount) FROM orders WHERE status = 'paid';
ROLLBACK;
EXPLAIN (ANALYZE, BUFFERS OFF) SELECT sum(amount) FROM orders WHERE status = 'paid';

 jit
-----
 on
(1 row)

 pg_jit_available
------------------
 t
(1 row)

BEGIN
SET
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=8755.30..8755.31 rows=1 width=32) (actual time=35.988..38.974 rows=1.00 loops=1)
   ->  Gather  (cost=8755.08..8755.29 rows=2 width=32) (actual time=35.892..38.963 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=7755.08..7755.09 rows=1 width=32) (actual time=26.891..26.891 rows=1.00 loops=3)
               ->  Parallel Seq Scan on orders  (cost=0.00..7278.72 rows=190541 width=6) (actual time=3.231..16.431 rows=152222.33 loops=3)
                     Filter: (status = 'paid'::text)
                     Rows Removed by Filter: 14444
 Planning Time: 0.315 ms
 JIT:
   Functions: 17
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.635 ms (Deform 0.217 ms), Inlining 0.000 ms, Optimization 0.508 ms, Emission 9.160 ms, Total 10.303 ms
 Execution Time: 40.122 ms
(14 rows)

ROLLBACK
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=8755.30..8755.31 rows=1 width=32) (actual time=29.617..32.130 rows=1.00 loops=1)
   ->  Gather  (cost=8755.08..8755.29 rows=2 width=32) (actual time=29.552..32.123 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=7755.08..7755.09 rows=1 width=32) (actual time=21.125..21.125 rows=1.00 loops=3)
               ->  Parallel Seq Scan on orders  (cost=0.00..7278.72 rows=190541 width=6) (actual time=0.009..11.380 rows=152222.33 loops=3)
                     Filter: (status = 'paid'::text)
                     Rows Removed by Filter: 14444
 Planning Time: 0.077 ms
 Execution Time: 32.152 ms
(10 rows)

การรันแบบ JIT ใช้ 40.1 ms เทียบกับ 32.2 ms เมื่อไม่มี (10.3 ms คือเวลาคอมไพล์): สำหรับ query สั้น JIT คือ overhead ล้วน ๆ จึงเป็นเหตุที่ threshold ค่าเริ่มต้นสูงมาก

แยกชนิดของ scan

table เดียวกันให้ node scan ต่างกันเมื่อ query เปลี่ยน สี่ชนิดที่ต้องจำได้:

Seq Scan อ่านทั้ง table แล้วกรอง ชนะเมื่อ row ส่วนใหญ่เข้าเงื่อนไข Index Scan เดิน index และดึงแต่ละ row ที่ตรงจาก table Bitmap Heap Scan รวบรวมตำแหน่ง row ที่ตรงทั้งหมดจาก index ก่อน แล้วดึง page ตามลำดับทางกายภาพ: จุดกึ่งกลางสำหรับหลักร้อยถึงหลักพัน row Index Only Scan ตอบจาก index อย่างเดียว เมื่อ query ต้องการเฉพาะ column ที่ index

order หนึ่งชั่วโมงและหนึ่งวันใช้ Index Scan ทั้งคู่ และน่าแปลกที่ 175 680 row (หกเดือน 35 % ของ table) ก็ใช้ใน 15 ms:

-- sql/scan-types.sql
-- the same table, three time windows: 1 hour, 1 day, about half of the table
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT * FROM orders WHERE created_at >= '2024-06-01 00:00+00' AND created_at < '2024-06-01 01:00+00';
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT * FROM orders WHERE created_at >= '2024-06-01 00:00+00' AND created_at < '2024-06-02 00:00+00';
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT * FROM orders WHERE created_at >= '2024-06-01 00:00+00' AND created_at < '2024-12-01 00:00+00';

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_created_at_idx on orders (actual rows=40.00 loops=1)
   Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-06-01 01:00:00+00'::timestamp with time zone))
   Index Searches: 1
 Planning Time: 0.276 ms
 Execution Time: 0.091 ms
(5 rows)

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_created_at_idx on orders (actual rows=960.00 loops=1)
   Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-06-02 00:00:00+00'::timestamp with time zone))
   Index Searches: 1
 Planning Time: 0.034 ms
 Execution Time: 0.107 ms
(5 rows)

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_created_at_idx on orders (actual rows=175680.00 loops=1)
   Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-12-01 00:00:00+00'::timestamp with time zone))
   Index Searches: 1
 Planning Time: 0.021 ms
 Execution Time: 15.029 ms
(5 rows)

ทำไม scan หนึ่งในสามของ table ยังเลือก index? เพราะ created_at สัมพันธ์กับลำดับ row ทางกายภาพอย่างสมบูรณ์ (table ถูกเติมตามลำดับเวลา) index scan จึงอ่าน heap page ต่อกันไป planner รู้เรื่องนี้จากสถิติ:

-- sql/correlation.sql
SELECT attname, round(correlation::numeric, 3) AS correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('created_at', 'customer_id', 'status')
ORDER BY attname;

   attname   | correlation
-------------+-------------
 created_at  |       1.000
 customer_id |       0.000
 status      |       0.842
(3 rows)

created_at มี correlation 1.000; customer_id เป็น 0.000 คือสุ่ม สำหรับ column สุ่ม หนึ่งพัน row ที่ตรงกระจายอยู่ในหนึ่งพัน page และ planner เลือก Bitmap Heap Scan (2.4 ms ด้านล่าง) จนกระทั่ง VACUUM สร้าง visibility map และ query เดียวกันกลายเป็น Index Only Scan ที่ Heap Fetches: 0 (0.044 ms):

-- sql/index-only-scan.sql
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT customer_id FROM orders WHERE customer_id BETWEEN 1 AND 20;
VACUUM orders;
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT customer_id FROM orders WHERE customer_id BETWEEN 1 AND 20;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on orders (actual rows=1000.00 loops=1)
   Recheck Cond: ((customer_id >= 1) AND (customer_id <= 20))
   Heap Blocks: exact=1000
   ->  Bitmap Index Scan on orders_customer_id_idx (actual rows=1000.00 loops=1)
         Index Cond: ((customer_id >= 1) AND (customer_id <= 20))
         Index Searches: 1
 Planning Time: 0.321 ms
 Execution Time: 2.381 ms
(8 rows)

VACUUM
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Index Only Scan using orders_customer_id_idx on orders (actual rows=1000.00 loops=1)
   Index Cond: ((customer_id >= 1) AND (customer_id <= 20))
   Heap Fetches: 0
   Index Searches: 1
 Planning Time: 0.080 ms
 Execution Time: 0.044 ms
(6 rows)

Index Only Scan ต้องมี visibility map: ก่อน VACUUM planner ไม่เลือกมัน เพราะทุก entry ต้องไปที่ heap เพื่อตรวจว่า row มองเห็นได้ Heap Fetches นับการไปที่ heap เหล่านั้น

ทำไม planner บางครั้งละเลย index

ความประหลาดใจที่พบบ่อย: คุณสร้าง index แต่ EXPLAIN ยังแสดง Seq Scan มักเป็นกรณีที่ planner ถูก ไม่ใช่ผิด column status เดียวกันที่มี index ให้ทั้งสองคำตอบ:

-- sql/selectivity.sql
-- 0.5 % of the rows versus 91 % of the rows
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE status = 'refunded';
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF) SELECT * FROM orders WHERE status = 'paid';

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using orders_status_idx on orders (actual rows=2500.00 loops=1)
   Index Cond: (status = 'refunded'::text)
   Index Searches: 1
 Planning Time: 0.257 ms
 Execution Time: 3.280 ms
(5 rows)

                     QUERY PLAN
----------------------------------------------------
 Seq Scan on orders (actual rows=456667.00 loops=1)
   Filter: (status = 'paid'::text)
   Rows Removed by Filter: 43333
 Planning Time: 0.059 ms
 Execution Time: 22.528 ms
(5 rows)

• query ไม่ selective refunded คือ 0.5 % ของ table: Index Scan, 3.3 ms paid คือ 91 %: Seq Scan, 22.5 ms เพื่อพิสูจน์ว่า planner ถูก ห้าม seq scan และ bitmap scan สำหรับ paid plan แบบ index อ่าน buffer มากกว่าและช้ากว่า (51.4 ms เทียบกับ 43.7 ms ของตัวเลือกค่าเริ่มต้น รันครั้งเดียว):

-- sql/forced-index.sql
-- diagnosis only: forbid the seq scan and see what the planner was avoiding
BEGIN;
SET LOCAL enable_seqscan = off;
SET LOCAL enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'paid';
ROLLBACK;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'paid';

BEGIN
SET
SET
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_status_idx on orders  (cost=0.42..18036.68 rows=457517 width=39) (actual time=0.037..37.370 rows=456667.00 loops=1)
   Index Cond: (status = 'paid'::text)
   Index Searches: 1
   Buffers: shared hit=4674 read=386
 Planning:
   Buffers: shared hit=135
 Planning Time: 0.278 ms
 Execution Time: 51.363 ms
(8 rows)

ROLLBACK
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..10923.00 rows=457517 width=39) (actual time=0.015..29.868 rows=456667.00 loops=1)
   Filter: (status = 'paid'::text)
   Rows Removed by Filter: 43333
   Buffers: shared hit=4673
 Planning Time: 0.071 ms
 Execution Time: 43.737 ms
(6 rows)

SET enable_seqscan = off เป็น เครื่องมือวินิจฉัย ไม่ใช่วิธีแก้: มันเปลี่ยน planner ทั้ง session และที่นี่มันแค่พิสูจน์ว่าค่าเริ่มต้นถูกแล้ว

• table เล็กมาก บน 100 row planner ไม่ใช้ index ของ primary key สำหรับ id = 7 เพราะการอ่าน page เดียวถูกกว่า:

-- sql/tiny-table.sql
CREATE TABLE colors (id int PRIMARY KEY, name text);
INSERT INTO colors SELECT g, 'color ' || g FROM generate_series(1, 100) g;
ANALYZE colors;
EXPLAIN SELECT * FROM colors WHERE id = 7;

CREATE TABLE
INSERT 0 100
ANALYZE
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on colors  (cost=0.00..2.25 rows=1 width=12)
   Filter: (id = 7)
(2 rows)

• สถิติเก่า หลัง bulk load planner ยังเชื่อสิ่งที่ ANALYZE เห็นก่อนหน้า ตรงนี้ row new 200 000 แถวลงใน table ที่สถิติบอกว่ามี 1 000 row ทั้งหมดเป็น old planner ประมาณ rows=1 (จริง 200 000) และเลือก Index Scan; หลัง ANALYZE มันประมาณ rows=199975 และเปลี่ยนเป็น Seq Scan ช่องว่างของการประมาณคือสัญญาณ; ความต่างของเวลา (21 ms เทียบกับ 17 ms) ไม่มากที่นี่แต่โตตามจำนวน row:

-- sql/stale-statistics.sql
CREATE TABLE jobs (id int, kind text);
CREATE INDEX jobs_kind_idx ON jobs (kind);
INSERT INTO jobs SELECT g, 'old' FROM generate_series(1, 1000) g;
ANALYZE jobs;
-- a bulk load lands before autovacuum has had a chance to re-analyze
INSERT INTO jobs SELECT g, 'new' FROM generate_series(1, 200000) g;
EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF) SELECT * FROM jobs WHERE kind = 'new';
ANALYZE jobs;
EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF) SELECT * FROM jobs WHERE kind = 'new';

CREATE TABLE
CREATE INDEX
INSERT 0 1000
ANALYZE
INSERT 0 200000
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using jobs_kind_idx on jobs  (cost=0.29..4.31 rows=1 width=8) (actual time=0.009..16.086 rows=200000.00 loops=1)
   Index Cond: (kind = 'new'::text)
   Index Searches: 1
 Planning Time: 0.079 ms
 Execution Time: 21.091 ms
(5 rows)

ANALYZE
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on jobs  (cost=0.00..3402.50 rows=199975 width=8) (actual time=0.047..12.370 rows=200000.00 loops=1)
   Filter: (kind = 'new'::text)
   Rows Removed by Filter: 1000
 Planning Time: 0.077 ms
 Execution Time: 17.370 ms
(5 rows)

• เงื่อนไขใช้ index ไม่ได้ ฟังก์ชันหรือ cast ครอบ column, LIKE ที่ขึ้นต้นด้วย wildcard หรือชนิดไม่ตรงกัน กรณีนั้นคือหัวข้อ Reading AI-generated code ด้านล่าง

ใน pgAdmin: ปุ่ม Explain รัน EXPLAIN และเมนู Explain มีตัวเลือก Analyze สำหรับ EXPLAIN ANALYZE ผลลัพธ์ถูกวาดเป็นต้นไม้กราฟิกที่แต่ละ node แสดงชนิด scan และ cost ลูกศรที่หนากว่าแปลว่ามี row มากกว่า: วิธีที่เร็วในการหา node ที่แพง (pgAdmin ไม่ได้อยู่ในการรันของบทเรียนนี้)

เมื่อค่าประมาณผิด: column ที่สัมพันธ์กัน

planner คูณ selectivity ของเงื่อนไขแยกกันราวกับ column เป็นอิสระต่อกัน ใน orders city = 7 บ่งบอก country = 7 เสมอ แต่ planner ไม่รู้ มันประมาณ 487 row จริงคือ 5 000 มากกว่าสิบเท่า CREATE STATISTICS แบบ dependencies บอกมัน และค่าประมาณกลายเป็น 4 617:

-- sql/correlated-columns.sql
-- city 7 always lies in country 7, but the planner multiplies the two selectivities
EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF, TIMING OFF)
SELECT * FROM orders WHERE city = 7 AND country = 7;
CREATE STATISTICS orders_city_country (dependencies) ON city, country FROM orders;
ANALYZE orders;
EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF, TIMING OFF)
SELECT * FROM orders WHERE city = 7 AND country = 7;

                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8846.70 rows=487 width=39) (actual rows=5000.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..7798.00 rows=203 width=39) (actual rows=1666.67 loops=3)
         Filter: ((city = 7) AND (country = 7))
         Rows Removed by Filter: 165000
 Planning Time: 0.240 ms
 Execution Time: 10.584 ms
(8 rows)

CREATE STATISTICS
ANALYZE
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..9259.70 rows=4617 width=39) (actual rows=5000.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..7798.00 rows=1924 width=39) (actual rows=1666.67 loops=3)
         Filter: ((city = 7) AND (country = 7))
         Rows Removed by Filter: 165000
 Planning Time: 0.113 ms
 Execution Time: 10.469 ms
(8 rows)

ที่นี่ทั้งสอง plan เป็น parallel sequential scan และรันราว 10 ms เท่ากัน ค่าประมาณที่ผิดจึงไม่เสียอะไร ใน query ที่ใหญ่กว่า ค่าประมาณผิดตัดสินลำดับ join หรืออัลกอริทึม join และ นั่น คือที่ที่ตัวคูณสิบเปลี่ยนมิลลิวินาทีเป็นนาทีได้ (docs เรื่อง extended statistics)

EXPLAIN บนการเขียน

EXPLAIN ANALYZE รัน UPDATE หรือ DELETE จริง เพื่ออ่าน plan โดยไม่เก็บการเปลี่ยนแปลง ให้ห่อด้วย transaction แล้ว rollback ผลรวมด้านล่างพิสูจน์: 16999.50 ก่อน, 17049.50 ใน transaction (50 row เปลี่ยน +1), 16999.50 หลัง rollback:

-- sql/explain-update.sql
SELECT sum(amount) AS total_before FROM orders WHERE customer_id = 42;
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET amount = amount + 1 WHERE customer_id = 42;
SELECT sum(amount) AS total_inside_transaction FROM orders WHERE customer_id = 42;
ROLLBACK;
SELECT sum(amount) AS total_after_rollback FROM orders WHERE customer_id = 42;

 total_before
--------------
     16999.50
(1 row)

BEGIN
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Update on orders  (cost=4.81..190.17 rows=0 width=0) (actual time=1.111..1.111 rows=0.00 loops=1)
   Buffers: shared hit=879 read=38 dirtied=53 written=1
   ->  Bitmap Heap Scan on orders  (cost=4.81..190.17 rows=50 width=22) (actual time=0.017..0.052 rows=50.00 loops=1)
         Recheck Cond: (customer_id = 42)
         Heap Blocks: exact=50
         Buffers: shared hit=53
         ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.007..0.007 rows=50.00 loops=1)
               Index Cond: (customer_id = 42)
               Index Searches: 1
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.040 ms
 Execution Time: 1.154 ms
(14 rows)

 total_inside_transaction
--------------------------
                 17049.50
(1 row)

ROLLBACK
 total_after_rollback
----------------------
             16999.50
(1 row)

Reading AI-generated code

ผู้ช่วย AI ถูกขอจำนวน order ที่สั่งเมื่อ 1 มีนาคม 2024 และเขียนสิ่งที่อ่านง่าย พร้อมคอมเมนต์ว่าใช้ index บน created_at:

🐛 หา bug ในโค้ดนี้

-- AI comment: "orders placed on March 1st - uses the index on created_at"
SELECT count(*) FROM orders WHERE created_at::date = '2024-03-01';

เฉลย

ผลถูกต้องและ index ถูกละเลย created_at::date ครอบ column ที่ index ด้วย expression และ index ธรรมดาอยู่บน column เอง planner จึงใช้ไม่ได้ มัน scan ทั้ง 500 000 row (Rows Removed by Filter: 166347 ในแต่ละ worker จากสาม worker) 15.9 ms:

-- sql/stb-wrong.sql
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT count(*) FROM orders WHERE created_at::date = '2024-03-01';

                                 QUERY PLAN
----------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual rows=1.00 loops=3)
               ->  Parallel Seq Scan on orders (actual rows=320.00 loops=3)
                     Filter: ((created_at)::date = '2024-03-01'::date)
                     Rows Removed by Filter: 166347
 Planning Time: 0.271 ms
 Execution Time: 15.871 ms
(10 rows)

วิธีแก้คือเทียบ column เปล่ากับช่วงแบบ half-open ซึ่งคือความหมายของ “ในวันนั้น” การนับเดียวกันตอนนี้เป็น Index Only Scan: 0.69 ms เร็วขึ้น 23 เท่า (และช่องว่างโตตาม table):

-- sql/stb-fixed.sql
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF, TIMING OFF)
SELECT count(*) FROM orders
WHERE created_at >= '2024-03-01 00:00+00' AND created_at < '2024-03-02 00:00+00';

                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Index Only Scan using orders_created_at_idx on orders (actual rows=960.00 loops=1)
         Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-02 00:00:00+00'::timestamp with time zone))
         Heap Fetches: 0
         Index Searches: 1
 Planning Time: 0.465 ms
 Execution Time: 0.688 ms
(7 rows)

ถ้า query ต้องใช้ cast ให้ index ที่ expression (CREATE INDEX ON orders ((created_at::date))) ซึ่งบทถัดไปครอบคลุม ระวังว่า ::date บน timestamptz ขึ้นกับ TimeZone ของ session

Mental Model

EXPLAIN คือ plan ของ planner; EXPLAIN ANALYZE เพิ่มสิ่งที่ executor เห็น ระยะห่างระหว่างสองตัวเลข rows= คือสุขภาพของสถิติ query ช้าเกือบทุกตัวเป็นหนึ่งในสามอย่าง: scan ที่อ่านมากกว่าที่คืนมาก, ค่าประมาณผิดที่เลือกกลยุทธ์ผิด หรือเงื่อนไขที่ซ่อน column จาก index plan บอกว่าเป็นอันไหน ถ้าคุณอ่านจากด้านในออกมาและเทียบค่าประมาณกับค่าจริง

เคล็ดลับและข้อควรระวัง

• EXPLAIN ANALYZE รัน query จริง สำหรับ SELECT ไม่เป็นไร แต่สำหรับ UPDATE หรือ DELETE มันเปลี่ยนข้อมูลคุณ: ห่อใน transaction ที่ ROLLBACK
• plan มีความหมายเฉพาะบนข้อมูลที่สมจริง query ที่ seq-scan table ทดสอบ 100 row อาจ index-scan table เดียวกันเมื่อมีล้าน row ทดสอบกับปริมาณที่เป็นตัวแทน
• เทียบ rows ที่ประมาณกับ rows จริงเสมอ ความต่างมากชี้ไปที่สถิติเก่า (ANALYZE) หรือ column ที่สัมพันธ์กัน (CREATE STATISTICS)
• อ่าน plan จากด้านในออกมา และคูณตัวเลขต่อ loop ด้วย loops
• SET enable_seqscan = off (และพวกเดียวกัน) พิสูจน์สิ่งที่ planner หลีกเลี่ยง; อย่าเอาไปใช้จริง
• อย่าเทียบตัวเลข cost= ข้าม query ต่างกัน: มีความหมายเฉพาะการเลือกระหว่าง plan ของ query เดียวกัน

🐘 ให้ EXPLAIN แทนที่การเดา

ก่อนเพิ่ม index หรือเขียน query ใหม่ ให้รัน EXPLAIN (ANALYZE, BUFFERS) (บน PostgreSQL 18 EXPLAIN ANALYZE รวม buffer อยู่แล้ว) และอ่านสิ่งที่ PostgreSQL ทำจริง plan บอกชนิด scan, เวลาหมดไปที่ไหน และค่าประมาณตรงกับความจริงหรือไม่ เปลี่ยนงานด้านประสิทธิภาพจากการเดาเป็นการวัด

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
EXPLAIN	ทันที ปลอดภัยกับทุก query เพราะไม่มีอะไรรัน	ได้แค่ค่าประมาณจากสถิติ ไม่มีตัวเลขจริง
EXPLAIN ANALYZE	เวลาจริง จำนวน row จริง การใช้ buffer (ค่าเริ่มต้นใน PG 18)	รัน query จริง: UPDATE/DELETE เปลี่ยนข้อมูลจริง
EXPLAIN (ANALYZE, BUFFERS OFF)	output น้อยลงเมื่อสนใจแค่เวลา	เสียข้อมูล cache-เทียบ-ดิสก์
สวิตช์ enable_*	แสดง plan ทางเลือกที่ planner ปฏิเสธ	มีผลทั้ง session; ซ่อนปัญหาจริงถ้าทิ้งไว้
CREATE STATISTICS	แก้ค่าประมาณสำหรับ column ที่สัมพันธ์กัน	งานเพิ่มตอน ANALYZE; ช่วยเฉพาะ query ที่กรอง column เหล่านั้นด้วยกัน

Common Misconceptions

• “index ถูกใช้เสมอถ้ามีอยู่” paid (91 % ของ row) ละเลย index อย่างถูกต้อง: การบังคับใช้ช้ากว่า (51.4 ms เทียบกับ 43.7 ms)
• “seq scan แปลว่าขาด index” บางครั้งมันคือ plan ที่ถูกที่สุด (table เล็ก เงื่อนไขไม่ selective)
• “index scan เร็วเสมอ” 175 680 row ด้วย index scan ใช้ 15 ms ถูกหรือไม่ขึ้นกับ correlation ระหว่างลำดับ index และลำดับ heap
• “WHERE col::date = ... ใช้ index บน col” ไม่ใช้ ช่วงบน column เปล่าใช้ (15.9 ms เทียบกับ 0.69 ms)
• “EXPLAIN ANALYZE บน UPDATE คือการลองแห้ง” มัน update จริง มีแต่ rollback ที่คืนข้อมูล

EXPLAIN กับ EXPLAIN ANALYZE ต่างกันอย่างไร?

ไม่ต่างกัน EXPLAIN ประมาณการอย่างเดียว; EXPLAIN ANALYZE รัน query และแสดงเวลาจริงกับจำนวน row จริง EXPLAIN ANALYZE ประมาณการอย่างเดียว EXPLAIN เร็วกว่าเพราะข้ามการวาง plan

ใน Bitmap Heap Scan ... (actual time=0.054..0.267 rows=50.00 loops=1) rows=50.00 หมายถึงอะไร?

planner ประมาณ 50 row node คืน 50 row ต่อ loop จริง (PostgreSQL 18 แสดงทศนิยม) table มี 50 row index มี 50 entry

order 91 % เป็น paid และมี index บน status ทำไม WHERE status = 'paid' ใช้ Seq Scan?

index เสีย การดึงเกือบทั้ง table ผ่าน index แพงกว่าการอ่านตรง ๆ; การบังคับใช้ index ช้ากว่า (51 ms เทียบกับ 44 ms) PostgreSQL ไม่เคยใช้ index บน text Seq Scan เร็วที่สุดเสมอ

อะไรทำให้ plan ของ WHERE created_at::date = '2024-03-01' เป็น Seq Scan?

created_at ไม่มี index cast ครอบ column ที่ index จึงใช้ index ธรรมดาไม่ได้; ช่วงบน column เปล่าได้ Index Only Scan ไม่สามารถ index date ได้ ไม่ได้รัน ANALYZE

planner ประมาณ rows=1 แต่ได้ 200 000 row หลัง bulk load วิธีแก้ที่น่าจะเป็นที่สุดคืออะไร?

เพิ่ม RAM รัน ANALYZE ให้สถิติสะท้อนข้อมูลใหม่ เขียน query ใหม่ด้วย CTE drop index

Submit

Mark as complete