EXPLAIN ANALYZE คืออะไร และอ่านยังไง
EXPLAIN แสดง "แผนการทำงาน" ที่ Planner เลือก ส่วน ANALYZE จะรัน query จริงแล้ววัดเวลา/จำนวนแถวจริงมาเทียบกับที่ประมาณการไว้
🔎 วิธีอ่านตัวเลขในแต่ละบรรทัด
Seq Scan on orders (cost=0.00..2181.00 rows=100000 width=97) (actual time=0.010..15.234 rows=100000 loops=1) Filter: (status = 'paid'::text) Rows Removed by Filter: 5000 Buffers: shared hit=1081 read=320 Planning Time: 0.120 ms Execution Time: 16.502 ms
| ส่วน | ความหมาย |
|---|---|
| cost=0.00..2181.00 | ต้นทุนโดยประมาณ (startup..total) หน่วยไม่ใช่เวลา แต่เป็นหน่วยเปรียบเทียบ |
| rows=100000 (สีเหลือง) | จำนวนแถวที่ คาดการณ์ จาก statistics |
| actual rows=100000 (สีเขียว) | จำนวนแถวที่ เกิดขึ้นจริง — ถ้าต่างจากค่าประมาณมาก แปลว่า statistics ล้าสมัย ควร ANALYZE ตาราง |
| loops=1 | node นี้ถูกเรียกกี่รอบ (สำคัญมากใน Nested Loop — ต้องคูณ actual time ด้วย loops) |
| Buffers: hit / read (สีแดง) | hit = เจอใน cache แล้ว, read = ต้องอ่านจาก disk — read เยอะ = cache ไม่พอ |
| Rows Removed by Filter | อ่านมาแล้วทิ้งเพราะไม่ตรงเงื่อนไข — ถ้าเยอะมาก บ่งบอกว่าควรมี index ช่วยกรองตั้งแต่ต้น |
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...ชนิดของ Scan Node (คลิกเล่นอนิเมชัน)
🟨 Seq Scan (Sequential Scan)
อ่านทุกหน้าของตารางเรียงตามลำดับกายภาพ ตั้งแต่ต้นจนจบ เหมาะกับตารางเล็ก หรือ query ที่ต้องอ่านเกือบทั้งตารางอยู่แล้ว
Seq Scan on orders (cost=0.00..2181.00 rows=100000 ..) (actual rows=100000 loops=1)
🟦 Index Scan
เดินลง B-tree จาก root → branch → leaf เพื่อหาตำแหน่งแถว แล้ว "กระโดด" (random I/O) เข้าไปอ่านข้อมูลจริงที่ heap ทีละแถว
⬇ จากนั้นกระโดดเข้า Heap (สุ่มตำแหน่ง ไม่เรียงลำดับ)
Index Scan using orders_id_idx on orders (cost=0.42..8.44 rows=1) (actual rows=1 loops=1) Index Cond: (id = 42)
🟩 Index Only Scan
เหมือน Index Scan แต่ ไม่ต้องเข้า heap เลย เพราะข้อมูลที่ต้องการมีครบใน index อยู่แล้ว (covering index) และ Visibility Map บอกว่าแถวนั้น "all-visible"
Index Only Scan using orders_id_idx on orders (cost=0.42..4.44 rows=1) (actual rows=1 loops=1)
Heap Fetches: 0 ← ยิ่งเลขนี้เป็น 0 ยิ่งดี
🟧 Bitmap Heap Scan / Bitmap Index Scan
2 เฟส: (1) เดิน index สร้าง "bitmap" ของตำแหน่งที่ตรงเงื่อนไขทั้งหมดก่อน (2) เรียง bitmap ตามลำดับกายภาพแล้วค่อยไปอ่าน heap — ลด random I/O เมื่อ match หลายแถว
Bitmap Heap Scan on orders (cost=12.5..456.2 rows=800) Recheck Cond: (customer_id = 88) -> Bitmap Index Scan on orders_customer_idx (cost=0.00..12.3 rows=800)
🔁 Nested Loop Join
สำหรับแต่ละแถวใน outer table จะ "loop" ไปหาแถวที่ match ใน inner table — เหมาะกับข้อมูลจำนวนน้อย/มี index ฝั่ง inner แต่ถ้า outer เยอะและ inner ไม่มี index จะช้ามาก (loops คูณกันได้มหาศาล)
Nested Loop (cost=0.42..1200 rows=40) (actual time=.. rows=40 loops=1)
-> Seq Scan on customers (rows=4)
-> Index Scan on orders (rows=10) (loops=4) ← รันซ้ำ 4 รอบ!
🪣 Hash Join
สร้าง hash table จากตารางเล็กก่อน (Build) แล้วเอาตารางใหญ่มา "probe" หา bucket เดียวกัน — เร็วกว่า Nested Loop เมื่อทั้งสองตารางใหญ่และไม่มี index ที่เหมาะ
Hash Join (cost=45..980 rows=500)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o
-> Hash (cost=20..20 rows=200)
-> Seq Scan on customers c
🔀 Merge Join
ทั้งสองฝั่งต้องเรียงลำดับอยู่แล้ว (จาก index หรือ Sort node) ใช้ 2 pointer เดินคู่ขนานเทียบค่าเรื่อยๆ — เหมาะกับข้อมูลใหญ่ที่เรียงลำดับมาแล้ว
Merge Join (cost=210..340 rows=2) Merge Cond: (a.id = b.id)
📊 Sort — และปัญหา "spill to disk"
ถ้าข้อมูลที่ต้อง sort ใหญ่กว่า work_mem Postgres จะต้องเขียนไฟล์ชั่วคราวลง disk (external merge) ซึ่งช้ากว่ามาก
Sort (cost=..) (actual rows=100000 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 25kB
⚡ Parallel Seq Scan + Gather
แบ่งตารางให้ worker หลายตัวช่วยกันสแกนพร้อมกัน แล้วรวมผลด้วย Gather / Gather Merge — ใช้ได้เมื่อตารางใหญ่พอและ max_parallel_workers_per_gather อนุญาต
Gather (cost=.. rows=100000) Workers Planned: 3 Workers Launched: 3 -> Parallel Seq Scan on orders (rows=33333 loops=3)
🚩 สัญญาณอันตรายที่ต้องระวังใน EXPLAIN
- Seq Scan บนตารางที่มีแถวหลักแสน/ล้าน โดยไม่มีเหตุผล (ไม่ได้ต้องการเกือบทุกแถว)
- estimated rows กับ actual rows ต่างกันหลายเท่า → statistics ล้าสมัย ต้อง
ANALYZE - Nested Loop ที่มี loops สูงมากและ inner ไม่มี index รองรับ
Sort Method: external merge Disk: ...→ ต้องเพิ่ม work_mem- Buffers read สูงกว่า hit มาก (cache miss เยอะ) เกิดขึ้นซ้ำๆ ในเวลาทำงานปกติ
- Rows Removed by Filter สูงมาก → filter ทำงานหลัง scan ทั้งตาราง ควรมี index ที่ตรงเงื่อนไข
- Heap Fetches มากใน Index Only Scan → ตารางมี dead tuples เยอะ ต้อง VACUUM
Index Algorithm แบบต่างๆ ต่างกันยังไง
Postgres มี index หลายชนิด แต่ละแบบถูกออกแบบมาให้เหมาะกับรูปแบบข้อมูล/query ต่างกัน เลือกผิดแบบ = index มีแต่ไม่ถูกใช้ หรือใช้แล้วไม่คุ้ม
🌳 B-tree (ค่า default, ใช้บ่อยที่สุด)
โครงสร้างต้นไม้สมดุล (balanced tree) รองรับ = < > <= >= BETWEEN, ORDER BY, LIKE 'prefix%' ค้นหา O(log n)
#️⃣ Hash Index
ใช้ hash function แปลงค่าเป็น bucket แล้วเข้าตรงจุดเลย — เร็วมากแต่รองรับ เฉพาะ = เท่านั้น (ไม่รองรับ range/ORDER BY) เหมาะกับ equality lookup ที่ key ยาวมากๆ
📚 GIN (Generalized Inverted Index)
เก็บแบบ "inverted index": 1 คีย์ → posting list ของหลายแถว เหมาะกับข้อมูลที่แต่ละแถวมีหลายค่า เช่น jsonb, array, full-text search (tsvector) เขียนช้ากว่าอ่านเยอะ
🗺️ GiST (Generalized Search Tree)
ต้นไม้ที่ใช้ bounding box ครอบข้อมูล เหมาะกับข้อมูลเชิงพื้นที่ (geometry, PostGIS), full-text, exclusion constraints และ nearest-neighbor search (<->)
🧩 SP-GiST (Space-Partitioned GiST)
ต้นไม้แบบไม่สมดุล แบ่งพื้นที่เป็นส่วนๆ (เช่น quad-tree/radix tree) เหมาะกับข้อมูลที่มีโครงสร้างไม่สม่ำเสมอ เช่น IP address (inet), เบอร์โทร, ข้อความที่มี prefix ร่วมกัน
📦 BRIN (Block Range Index)
เก็บแค่ min/max ของแต่ละช่วง "block" (เช่น 128 หน้า/ช่วง) เล็กมาก (เล็กกว่า B-tree หลายร้อยเท่า) เหมาะกับตารางใหญ่มากที่ข้อมูลเรียงตามธรรมชาติอยู่แล้ว เช่น created_at ที่ insert ตามเวลา
📋 สรุปเปรียบเทียบ
| Index | รองรับ Range/Sort | เหมาะกับ | ขนาด | ต้นทุนตอนเขียน |
|---|---|---|---|---|
| B-tree | ✅ | ทั่วไป, equality + range | กลาง | กลาง |
| Hash | ❌ (= เท่านั้น) | equality lookup, key ยาว | เล็ก-กลาง | ต่ำ |
| GIN | ❌ | jsonb, array, full-text | ใหญ่ | สูง (ใช้ fastupdate ช่วยได้) |
| GiST | บางส่วน | geometry, KNN, exclusion | กลาง-ใหญ่ | กลาง |
| SP-GiST | บางส่วน | IP, phone, prefix data | เล็ก-กลาง | ต่ำ-กลาง |
| BRIN | ✅ (แบบหยาบ) | ตารางใหญ่ที่ข้อมูลเรียงตามธรรมชาติ | เล็กมาก | ต่ำมาก |
กลไก Database Page: ที่เก็บข้อมูลจริงเพื่อ Scan แถว
Postgres ไม่ได้เก็บข้อมูลเป็น "แถว" ลอยๆ แต่เก็บเป็นไฟล์แบ่งเป็นหน้า (page) ขนาดคงที่ 8KB เรียงต่อกัน ทุกการ Scan ไม่ว่าจะ Seq Scan หรือ Index Scan สุดท้ายต้องอ่านผ่านหน้าเหล่านี้เสมอ
📄 โครงสร้างภายในของ 1 Page (8KB)
แต่ละหน้าแบ่งเป็น 4 ส่วน: Page Header (metadata) → Line Pointer Array (ที่อยู่ของแต่ละแถว เติบโตจากบนลงล่าง) → Free Space (พื้นที่ว่างตรงกลาง) → Tuple Data (ข้อมูลแถวจริง เติบโตจากล่างขึ้นบน)
-- แต่ละแถวมี "line pointer" (item id) ชี้ไปตำแหน่ง offset ของ tuple จริงในหน้าเดียวกัน -- เมื่อ Free Space หมด → Postgres ต้องเปิด page ใหม่มาเก็บแถวถัดไป (นี่คือเหตุผลที่ตารางโตเป็นไฟล์หลาย page)
🧬 Tuple Header กับ MVCC (xmin / xmax)
ทุก tuple มี header เก็บ xmin (transaction ที่สร้างแถวนี้) และ xmax (transaction ที่ทำให้แถวนี้ตาย ถ้ายังไม่ตายจะเป็น NULL) — Postgres ไม่เคยแก้ข้อมูลทับที่เดิม แต่เขียนแถวใหม่เสมอ (append-only)
🔥 HOT Update (Heap-Only Tuple)
ถ้าแถวใหม่ยังอยู่ "หน้าเดียวกัน" กับแถวเดิม (มีที่ว่างพอ) และไม่ได้แก้คอลัมน์ที่มี index เลย Postgres จะทำ HOT update — แก้แค่ลิงก์ภายในหน้า โดย ไม่ต้องแตะ index เลยสักตัว เร็วกว่ามาก
🗺️ Free Space Map (FSM)
Postgres เก็บแผนที่แยกต่างหากไว้บอกว่าแต่ละหน้ามีที่ว่างเหลือเท่าไหร่ เวลา INSERT จะเปิด FSM มาหาหน้าที่ "พอมีที่" ก่อน แทนที่จะไปต่อท้ายไฟล์เสมอ
👁️ Visibility Map (VM) — หัวใจของ Index Only Scan
แต่ละหน้าจะมี 1 บิตบอกว่า "ทุกแถวในหน้านี้มองเห็นได้จากทุก transaction แล้ว" (all-visible) ถ้าใช่ Postgres จะข้ามการเข้า heapได้เลยตอน Index Only Scan และ VACUUM ก็ข้ามหน้านี้ได้เช่นกัน (ประหยัดงานมหาศาล)
🧠 Buffer Pool (shared_buffers) — ก่อน Scan ต้องผ่าน RAM เสมอ
ไม่ว่าจะ scan แบบไหน Postgres ต้องขอ "pin" หน้านั้นเข้ามาใน buffer pool (RAM) ก่อนเสมอ ถ้าหน้านั้นเคยถูกใช้แล้วจะเจอใน cache (hit) แต่ถ้าไม่เคยต้องไปอ่านจาก disk (miss) — นี่คือที่มาของ Buffers: hit/read ใน EXPLAIN
📍 ctid — ที่อยู่กายภาพของแถว (Page, Offset)
ทุกแถวมี ctid ซ่อนอยู่ เช่น (0,5) หมายถึง "หน้าเลข 0 ช่อง line pointer ที่ 5" — นี่คือสิ่งที่ Index เก็บไว้ชี้กลับมาที่ heap เวลาทำ Index Scan (ตรงกับอนิเมชัน "กระโดดเข้า Heap" ในแท็บ EXPLAIN)
🧹 VACUUM — เก็บกวาด Dead Tuples คืนพื้นที่
เมื่อ UPDATE/DELETE ทำให้เกิด dead tuple ค้างอยู่ VACUUM จะไล่ mark line pointer เป็น "unused" คืนพื้นที่กลับเข้า FSM และอัปเดต Visibility Map ให้ Index Only Scan เร็วขึ้น (ถ้าไม่ VACUUM เป็นประจำ ตารางจะบวม/"bloat" และ scan ช้าลงเรื่อยๆ)
ตัวอย่าง Query แย่ vs ดี (จับคู่เทียบกัน)
SELECT * FROM orders WHERE id = 1;
SELECT id, status, total FROM orders WHERE id = 1;
SELECT * FROM users WHERE lower(email) = 'a@x.com';
-- สร้าง expression index CREATE INDEX ON users (lower(email)); SELECT * FROM users WHERE lower(email) = 'a@x.com';
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
SELECT * FROM products WHERE name LIKE '%phone%';
CREATE EXTENSION pg_trgm; CREATE INDEX ON products USING gin (name gin_trgm_ops); SELECT * FROM products WHERE name LIKE '%phone%';
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;
SELECT * FROM posts WHERE id > :last_id ORDER BY id LIMIT 20;
-- ใน loop ของแอป SELECT * FROM comments WHERE post_id = ?; -- เรียก N ครั้ง
SELECT * FROM comments WHERE post_id = ANY($1::int[]); -- ครั้งเดียว
-- phone เป็น varchar แต่ query ส่ง int SELECT * FROM users WHERE phone = 0812345678;
SELECT * FROM users WHERE phone = '0812345678';
SELECT COUNT(*) FROM big_events;
SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'big_events';
CREATE INDEX ON orders (created_at, customer_id); -- แต่ query กรองด้วย customer_id อย่างเดียว SELECT * FROM orders WHERE customer_id = 5;
CREATE INDEX ON orders (customer_id, created_at); SELECT * FROM orders WHERE customer_id = 5 ORDER BY created_at DESC;
SELECT * FROM orders WHERE customer_id = 5 OR product_id = 9;
SELECT * FROM orders WHERE customer_id = 5 UNION SELECT * FROM orders WHERE product_id = 9;