Query Optimization & Execution Plan

1. Pipeline DB xử lý 1 query

Parser/Analyzer/Rewriter cố định, ít chi phí. Planner là phần "thông minh", tốn 90% nỗ lực và là nguồn gốc của mọi vấn đề performance.

2. Cost-based Optimizer

Postgres dùng cost-based optimizer (CBO) — không phải rule-based. Nghĩa là planner ước tính chi phí ước lượng của mỗi plan và chọn plan rẻ nhất.

"Cost" ≠ thời gian thật, mà là đơn vị tương đối:

2.1. Vì sao "Seq Scan" đôi khi nhanh hơn Index Scan?

Khi query trả ~5–20% bảng:

• Seq Scan = đọc N page tuần tự (cost = N × seq_page_cost).
• Index Scan = đọc M page index + M page heap NGẪU NHIÊN (cost = M × random_page_cost cao hơn nhiều).

Trên SSD, random thực ra không chậm hơn nhiều seq → set random_page_cost = 1.1 để planner ưu tiên index hơn.

3. Statistics — bộ não của planner

Planner ước tính cost dựa trên statistics được thu thập bởi ANALYZE (autovacuum cũng tự chạy).

3.1. Xem statistics

SELECT
  attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'users' AND attname = 'country';

Output ví dụ:

attname   = 'country'
n_distinct = 50          -- ước lượng 50 country khác nhau
most_common_vals = {VN,US,UK,JP,SG}
most_common_freqs = {0.30, 0.20, 0.15, 0.10, 0.05}
histogram_bounds = {AR, AU, BR, ...}     -- 100 bucket cho range estimation
correlation = 0.05       -- thấp = không sorted

3.2. Khi statistics outdated

Sau bulk INSERT/UPDATE lớn, autovacuum chưa kịp ANALYZE → planner ước lượng sai → chọn plan tệ.

-- Manual:
ANALYZE users;
ANALYZE users (country);

-- Tăng độ chính xác (default = 100):
ALTER TABLE users ALTER COLUMN country SET STATISTICS 1000;
ANALYZE users;

3.3. Multivariate statistics (Postgres 10+)

Khi 2 cột correlated (vd: city và country), planner default tính độc lập → ước lượng sai. Tạo extended statistic:

CREATE STATISTICS users_city_country (dependencies)
  ON city, country FROM users;
ANALYZE users;

4. EXPLAIN — cú pháp và options

-- Cơ bản (không chạy thật):
EXPLAIN SELECT * FROM users WHERE country = 'VN';

-- Chạy thật + đo:
EXPLAIN ANALYZE SELECT * FROM users WHERE country = 'VN';

-- Đầy đủ output:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT * FROM users WHERE country = 'VN';

5. Plan Nodes — các loại

5.1. Scan nodes

• Seq Scan — đọc tuần tự toàn bảng. Tốt khi cần > ~10% bảng hoặc bảng nhỏ.
• Index Scan — duyệt B-tree, follow heap pointer. Tốt cho selective query.
• Index Only Scan — không cần đọc heap, lấy đủ từ index (covering). Cực nhanh.
• Bitmap Index Scan + Bitmap Heap Scan — khi index không quá selective hoặc cần combine nhiều index.
• TID Scan — đọc tuple theo TID (system column).

5.2. Modifier nodes

• Sort — sắp xếp. Có thể disk-spill nếu vượt work_mem.
• Limit — cắt N dòng đầu.
• Aggregate / HashAggregate / GroupAggregate — gom nhóm.
• Hash — build hash table cho join.
• Materialize — cache subquery vào RAM.
• WindowAgg — window function.
• Unique — DISTINCT.

5.3. Set ops

• Append — UNION ALL.
• Merge Append — UNION ALL với sorted inputs.

6. Join Algorithms — 3 kiểu

6.1. Nested Loop Join

Cho mỗi row bên ngoài, scan bảng trong tìm match.

For each row in outer:
  For each row in inner:
    if match: emit

Cost: O(N × M). Nhưng nếu inner có index trên cột join → mỗi lookup là O(log M) → tốt cho outer nhỏ + inner index.

6.2. Hash Join

Build hash table từ bảng nhỏ hơn (build side), probe bằng bảng lớn (probe side).

1. Build hash table on smaller table by join key
2. For each row in larger table: hash lookup → match

Cost: O(N + M). Tốt cho join lớn, không có index. Cần đủ work_mem để hold hash table.

6.3. Merge Join

Hai input đều sorted, "zipper" qua nhau.

Cost: O(N + M) sau sort. Tốt khi hai input đã sorted (nhờ index hoặc ORDER BY).

6.4. So sánh khi nào

7. Đọc EXPLAIN ANALYZE — ví dụ thực tế

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.country = 'VN'
GROUP BY u.email
ORDER BY order_count DESC
LIMIT 10;

                                  QUERY PLAN
─────────────────────────────────────────────────────────────────────────
 Limit  (cost=12450.32..12450.35 rows=10 width=42)
        (actual time=85.12..85.15 rows=10 loops=1)
   Buffers: shared hit=8240 read=120
   ->  Sort  (cost=12450.32..12500.40 rows=20030 width=42)
             (actual time=85.10..85.12 rows=10 loops=1)
         Sort Key: (count(o.id)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate (cost=11800.10..12000.40 rows=20030 width=42)
                          (actual time=80.20..82.40 rows=20030 loops=1)
               Group Key: u.email
               ->  Hash Right Join  (cost=850.00..11000.00 rows=160000)
                                   (actual time=10.45..70.10 rows=160000)
                     Hash Cond: (o.user_id = u.id)
                     ->  Seq Scan on orders o
                                       (cost=0..6000 rows=400000 actual=0.02..30.10)
                     ->  Hash
                         ->  Index Scan using idx_users_country on users u
                              Index Cond: (country='VN')
                              actual time=0.05..2.40 rows=20030 loops=1
 Planning Time: 0.5 ms
 Execution Time: 85.20 ms

Đọc từ trong ra ngoài (innermost child first):

1. Index Scan trên users theo country = 'VN' → 20k user.
2. Build hash table từ user → Hash node.
3. Seq Scan trên orders (400k dòng) → probe hash → Hash Right Join, ra 160k row.
4. HashAggregate gom theo email → 20k group.
5. Sort top-10 (heapsort).
6. Limit 10.

7.1. Các thông số then chốt

• cost=A..B — A = startup cost (đến khi sẵn row đầu), B = total cost.
• rows=N — ước lượng. So sánh với actual rows: lệch quá → statistics outdated.
• actual time=A..B — ms để bắt đầu trả row đầu / trả hết. Nhân với loops!
• loops=N — số lần node chạy (trong nested loop).
• Buffers: shared hit=X read=Y — X page từ cache, Y phải đọc từ disk. Read cao = cache miss.

7.2. Lệch ước lượng (estimation error)

->  Seq Scan on orders  (cost=0..6000 rows=400 actual=0.02..30.10 rows=400000)
                                          ^estimate            ^actual
                                          400 vs 400000 = lệch 1000×

Lệch 10× chấp nhận được, lệch 100× hoặc 1000× → dấu hiệu cần ANALYZE hoặc tạo extended statistics.

8. Slow query smells — patterns nguy hiểm

8.1. Seq Scan trên bảng lớn với selectivity thấp

Có thể index thiếu, function trên cột, type mismatch. Cách kiểm tra: kiểm tra mệnh đề WHERE, xem có cột nào "ép" planner skip index không.

8.2. Nested Loop với inner Seq Scan loops=N rất lớn

Inner cần index. Hoặc outer trả quá nhiều row.

8.3. Sort Method: external merge Disk: ...

Sort spill ra disk khi vượt work_mem (default 4MB). Tăng work_mem cho session:

SET work_mem = '64MB';

8.4. Hash Batches > 1

Hash table không vừa work_mem → spill ra disk multiple batches. Tăng work_mem.

8.5. Rows estimate sai 100×+

Statistics outdated. Hoặc cần multivariate stats. Hoặc query có biểu thức phức tạp planner không estimate được.

8.6. OFFSET lớn

-- ❌ Chậm trên page sâu:
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;

-- ✓ Keyset pagination:
SELECT * FROM posts WHERE id > $last_seen_id ORDER BY id LIMIT 20;

8.7. N+1 query

App pattern: lấy 100 user, rồi mỗi user query 1 lần lấy email/order → 101 query thay vì 1.

// ❌ N+1:
const users = await db.query('SELECT * FROM users LIMIT 100');
for (const u of users) {
  u.orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [u.id]);
}

// ✓ JOIN hoặc IN:
const users = await db.query('SELECT * FROM users LIMIT 100');
const orders = await db.query(
  'SELECT * FROM orders WHERE user_id = ANY($1)',
  [users.map(u => u.id)]
);

9. Tuning checklist — 10 bước

1. Reproduce query với EXPLAIN (ANALYZE, BUFFERS).
2. So sánh estimate vs actual rows. Lệch quá → ANALYZE.
3. Tìm Seq Scan trên bảng lớn → kiểm tra index, function trên cột.
4. Kiểm tra index có được dùng không. Nếu không, EXPLAIN có nói "Seq Scan" thay vì "Index Scan".
5. Đọc Buffers — nếu read cao, cache miss. Cân nhắc tăng shared_buffers.
6. Sort spill disk → tăng work_mem hoặc tránh ORDER BY (dùng index sorted).
7. Hash Batches > 1 → tăng work_mem.
8. Loops cao trong Nested Loop → cần index trên inner.
9. OFFSET lớn → keyset pagination.
10. Loại N+1 ở app — JOIN hoặc IN list.

9.1. Quan sát query thực tế qua pg_stat_statements

-- Cài extension (1 lần):
CREATE EXTENSION pg_stat_statements;

-- Top 10 query tốn thời gian nhất:
SELECT
  ROUND(total_exec_time / 1000) AS total_sec,
  calls,
  ROUND(mean_exec_time, 2) AS mean_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 1) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

10. SET parameter & Hint

Postgres KHÔNG có hint kiểu Oracle (/*+ INDEX(t idx) */). Thay vào đó, dùng GUC để "thuyết phục" planner:

-- Tắt seq scan (force index nếu có):
SET enable_seqscan = OFF;

-- Tắt nested loop:
SET enable_nestloop = OFF;

-- Cho 1 transaction thôi:
BEGIN;
SET LOCAL enable_seqscan = OFF;
SELECT ...;
COMMIT;

10.1. Quan trọng nhất: tune cho SSD

-- postgresql.conf:
random_page_cost = 1.1     # default 4.0, SSD nên ~1.1
effective_cache_size = '8GB'  # ước lượng cache OS + Postgres
shared_buffers = '2GB'    # 25% RAM
work_mem = '32MB'         # per operation per connection
maintenance_work_mem = '1GB'  # cho VACUUM, CREATE INDEX

11. Walkthrough thực tế — tối ưu từ 5s xuống 5ms

Query gốc:

SELECT u.email, COUNT(*) AS recent_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '7 days'
  AND u.country = 'VN'
GROUP BY u.email
ORDER BY recent_orders DESC
LIMIT 20;

EXPLAIN cho thấy: Seq Scan trên 100M dòng orders, lọc rồi mới JOIN. Lệch estimate. Mất 5s.

Bước 1: tạo composite index trên orders:

CREATE INDEX CONCURRENTLY idx_orders_created_user
  ON orders(created_at DESC, user_id);

Plan đổi sang Index Scan trên orders → 200ms.

Bước 2: tạo index trên users:

CREATE INDEX CONCURRENTLY idx_users_country ON users(country);

200ms → 80ms.

Bước 3: covering index để Index Only Scan:

CREATE INDEX CONCURRENTLY idx_users_country_inc
  ON users(country) INCLUDE (id, email);

80ms → 30ms.

Bước 4: partial index — vì query luôn lọc 7 ngày, không cần index toàn bộ orders:

-- Postgres không hỗ trợ NOW() trong WHERE của partial index (immutable).
-- Workaround: monthly partition + index.
CREATE INDEX idx_orders_recent ON orders(user_id)
  WHERE created_at > '2026-05-01';
-- Recreate hàng tuần (script).

30ms → 5ms.

Đó là tinh thần tuning: từng bước, đo từng lần, áp đúng index cho đúng query.

12. Bài tập

1. Cho query: SELECT * FROM logs WHERE level='ERROR' AND created_at > NOW() - INTERVAL '1h' ORDER BY created_at DESC LIMIT 100; trên bảng 1 tỉ dòng. Đề xuất index + giải thích.
2. Đọc EXPLAIN sau và xác định bottleneck: Seq Scan on orders (cost=0..50000 actual time=0.5..3500 rows=100000 loops=1). Đề xuất tối ưu.
3. Vì sao OFFSET 100000 chậm? Cách thay thế?
4. Khi nào planner chọn Hash Join thay vì Nested Loop? Demo bằng EXPLAIN trên bảng test.
5. Bảng pg_stat_statements dùng để làm gì? Cài extension và dùng tìm top 5 query chậm nhất trong DB của bạn.
6. Phân biệt shared_buffers và work_mem. Nếu RAM 16GB, đặt 2 thông số này thế nào?

13. Quiz