SQL Index Under the Hood (Part 5 – Bonus)

Đây là 12 case thường gặp nhất khi JOIN và Index "đánh nhau" trên bảng hàng chục-hàng trăm triệu dòng — mỗi case đều có thể tái hiện bằng EXPLAIN (ANALYZE, BUFFERS).

Trước khi vào case đầu tiên, có 4 khái niệm sẽ xuất hiện xuyên suốt bài. Nắm trước sẽ đỡ phải dừng lại giữa chừng.

Trước khi bắt đầu: 4 khái niệm nền

1. Nested Loop — thuật toán JOIN đơn giản nhất

Hai vòng lặp lồng nhau:

for row_a in outer_table:        # outer loop
    for row_b in inner_table:    # inner loop
        if row_a.key == row_b.key:
            emit(row_a, row_b)

Tổng thao tác: outer_rows × inner_rows. Outer = 100, inner = 100M → 10 tỷ phép so sánh. Đây chính là gốc rễ của Case 1.

Nhưng có Index thì khác hẳn:

for row_a in outer_table:                     # 100 rows
    row_b = index_lookup(inner, row_a.key)     # O(log n) mỗi lần
    emit(row_a, row_b)

100 rows × O(log 100M) ≈ 100 × 27 = 2.700 thao tác. Từ 10 tỷ xuống 2.700 — Nested Loop + Index nhanh kinh khủng khi outer table nhỏ.

Ba biến thể trong EXPLAIN:

-- Tốt: Nested Loop + Index Scan
Nested Loop
  → Seq Scan on customers          -- outer: 100 rows
  → Index Scan on orders            -- inner: index lookup, mỗi lần O(log n)

-- Tốt nhất: Nested Loop + Index Only Scan
Nested Loop
  → Seq Scan on customers
  → Index Only Scan on orders       -- mọi cột cần đều nằm trong index

-- Tệ nhất: Nested Loop + Seq Scan
Nested Loop
  → Seq Scan on customers           -- 100 rows
  → Seq Scan on orders              -- 100M × 100 = 💀 (đây là Case 1)

Khi nào tốt, khi nào tệ:

OUTER NHỎ (vài chục–vài trăm) + Inner có Index
  → Nested Loop nhanh nhất trong mọi JOIN strategy

OUTER LỚN (hàng nghìn trở lên) + Inner có Index
  → Nested Loop thua Hash Join (100K lần random I/O > 1 lần sequential scan)

INNER KHÔNG CÓ INDEX
  → Nested Loop gần như luôn tệ (mỗi lần loop = full scan inner)

Cách kiểm soát khi Planner chọn sai:

SET LOCAL enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_nestloop;

Nhưng đừng disable globally — Nested Loop vẫn là chiến lược tốt nhất cho rất nhiều query nhỏ, point-lookup, OLTP workload thông thường.

2. Bitmap Scan — con đường giữa Seq Scan và Index Scan

Cách hoạt động — ví dụ:

SELECT * FROM orders WHERE customer_id IN (100, 200, 300, 400, 500);

Bước 1 — Bitmap Index Scan: duyệt index, ghi nhận page number vào bitmap, chưa chạm heap:

Page 5: ✓   Page 12: ✓   Page 13: ✓   Page 27: ✓   Page 28: ✓   Page 41: ✓ ...

Bước 2 — Bitmap Heap Scan: đọc các page đã đánh dấu theo thứ tự page number (5→12→13→27→28→41) — gần như sequential I/O dù chỉ đọc đúng page cần.

Khi nào Planner chọn:

Ít dòng (vài chục)          → Index Scan
Nhiều dòng (vài nghìn–%)    → Bitmap Scan
Rất nhiều dòng (>10-20%)    → Seq Scan

-- 1 customer ≈ 1.000 rows → Index Scan
SELECT * FROM orders WHERE customer_id = 50000;

-- 500 customers ≈ 500.000 rows → Bitmap Scan
SELECT * FROM orders WHERE customer_id BETWEEN 10000 AND 10500;

-- 70% bảng → Seq Scan
SELECT * FROM orders WHERE status = 'PAID';

Khả năng đặc biệt — BitmapAnd/BitmapOr: kết hợp nhiều index trong cùng một query, điều Index Scan thuần không làm được:

SELECT * FROM orders WHERE customer_id = 50000 AND status = 'PENDING';

BitmapAnd
  → Bitmap Index Scan on idx_customer  → bitmap A (1.000 pages)
  → Bitmap Index Scan on idx_status    → bitmap B (10M pages)
  → AND hai bitmap lại                 → ~100 pages
  → Bitmap Heap Scan                   → đọc ~100 pages

Đây là lý do đôi khi bạn không cần composite index — 2 index đơn + BitmapAnd có thể đủ tốt. Nhưng khi cần ORDER BY/LIMIT, composite index vẫn thắng vì Bitmap Scan luôn phải đọc hết rồi mới sort.

Khi Bitmap Scan trở thành vấn đề — "lossy" bitmap: nếu số dòng khớp quá nhiều, bitmap không đủ memory track từng dòng → chuyển sang track theo page, buộc phải đọc cả page rồi recheck từng dòng:

Bitmap Heap Scan on orders
  Recheck Cond: (customer_id = 50000)
  Rows Removed by Index Recheck: 8402      ← dấu hiệu lossy

Thấy dòng Rows Removed by Index Recheck cao → bitmap đang lossy, hiệu quả giảm. Solution: tăng work_mem cho query đó, hoặc dùng composite index để giảm số dòng match ngay từ đầu:

SET work_mem = '256MB';  -- session-level

3. Hash Index — nhanh cho =, vô dụng cho phần còn lại

Dễ nhầm với Hash Join (thuật toán JOIN), nhưng đây là chuyện khác — Hash Index là một loại cấu trúc index, đứng cạnh B-Tree:

CREATE INDEX idx_orders_status_hash ON orders USING HASH (status);

Hash function băm giá trị cột → trỏ thẳng tới đúng bucket. Nhanh cho equality, nhưng không hỗ trợ gì khác:

-- Equality: Hash nhanh hơn B-Tree một chút
WHERE status = 'PAID'
-- B-Tree: O(log n) — vài lần đọc node
-- Hash:   O(1) — 1 lần hash, nhảy thẳng tới bucket

-- Mọi thứ khác, Hash không làm được
WHERE status != 'PAID'              -- ❌
WHERE status IN ('PAID','PENDING')  -- ❌ phải hash từng giá trị
WHERE created_at > '2024-01-01'     -- ❌ Hash không hiểu thứ tự
ORDER BY status                     -- ❌ Hash table không có thứ tự

Vì sao gần như không bao giờ dùng trong PostgreSQL hiện đại: trước bản 10, Hash Index không WAL-logged — crash là mất index, không ai dám dùng production. Từ bản 10+ đã WAL-safe và nhỏ hơn B-Tree cho cột giá trị dài, nhưng B-Tree vẫn đủ nhanh cho equality, lại hỗ trợ thêm range và sort.

Case duy nhất Hash Index có lợi thế rõ ràng — cột dài, chỉ dùng equality, bảng cực lớn:

-- session_token varchar(128), chỉ dùng cho equality lookup
CREATE INDEX idx_sessions_token_hash ON sessions USING HASH (session_token);

-- B-Tree: lưu full 128 bytes mỗi entry
-- Hash:   lưu ~4-8 bytes hash mỗi entry
-- Trên 100M rows, chênh nhau hàng GB

So sánh size thật:

CREATE INDEX idx_token_btree ON sessions(session_token);
CREATE INDEX idx_token_hash ON sessions USING HASH (session_token);

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes WHERE tablename = 'sessions' AND indexname LIKE 'idx_token%';

Quy tắc thực tế: mặc định dùng B-Tree. Chỉ xét Hash Index khi cột giá trị dài + chỉ equality lookup + cần tiết kiệm disk. Không case nào trong 12 case dưới đây cần tới nó.

4. Partial Index — index chỉ phủ một phần bảng

-- Full index: 100M entries
CREATE INDEX idx_full ON orders(customer_id, created_at DESC);

-- Partial index: chỉ ~10M entries (10% bảng)
CREATE INDEX idx_partial ON orders(customer_id, created_at DESC)
WHERE status = 'PENDING';

Cách hoạt động khi ghi dữ liệu:

INSERT (status = 'PAID')    → không thỏa WHERE → KHÔNG thêm vào index
INSERT (status = 'PENDING') → thỏa WHERE → thêm vào index
UPDATE PENDING → PAID       → tự động xóa khỏi partial index

PostgreSQL chỉ tự dùng khi WHERE của query khớp hoặc bao hàm WHERE của index:

-- Index có WHERE status = 'PENDING'

-- ✅ Khớp
SELECT * FROM orders WHERE status = 'PENDING' AND customer_id = 50000;

-- ✅ Vẫn khớp — thêm điều kiện không phá match
SELECT * FROM orders
WHERE status = 'PENDING' AND customer_id = 50000 AND created_at >= '2024-01-01';

-- ❌ Không khớp
SELECT * FROM orders WHERE status = 'PAID' AND customer_id = 50000;
SELECT * FROM orders WHERE customer_id = 50000;   -- thiếu điều kiện status

-- ⚠️ OR phá match
SELECT * FROM orders
WHERE (status = 'PENDING' OR status = 'FAILED') AND customer_id = 50000;
-- Index chỉ cover PENDING, không cover FAILED → không dùng được

4 pattern hay dùng:

Active/Inactive records:

CREATE INDEX idx_orders_active
ON orders(customer_id, created_at DESC)
WHERE status IN ('PENDING', 'FAILED');
-- idx_full (100M rows):   ~3.2 GB
-- idx_active (5% bảng):   ~160 MB   ← nhỏ hơn 20 lần

Soft delete:

CREATE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;

Unique constraint có điều kiện — mỗi customer chỉ được 1 đơn PENDING tại một thời điểm:

CREATE UNIQUE INDEX idx_one_pending_per_customer
ON orders(customer_id) WHERE status = 'PENDING';

Một customer có 1000 đơn PAID vẫn không vi phạm gì, nhưng có 2 đơn PENDING cùng lúc sẽ bị reject.

Gần đây mới quan trọng — cẩn thận với NOW():

-- ⚠️ NOW() evaluate lúc CREATE INDEX → sau 30 ngày sẽ stale
CREATE INDEX idx_orders_recent
ON orders(customer_id, created_at DESC)
WHERE created_at >= NOW() - INTERVAL '30 days';

-- ✅ Dùng mốc cố định, recreate định kỳ (DROP + CREATE CONCURRENTLY)
CREATE INDEX idx_orders_2024
ON orders(customer_id, created_at DESC)
WHERE created_at >= '2024-01-01';

Kết hợp tối đa — Partial + Composite + Covering:

-- API: 20 đơn PENDING gần nhất của customer, chỉ cần id + total + created_at
CREATE INDEX idx_ultimate
ON orders(customer_id, created_at DESC)
INCLUDE (total)
WHERE status = 'PENDING';

• Partial (WHERE status = 'PENDING'): chỉ 10% bảng → index nhỏ 10 lần

• Composite (customer_id, created_at DESC): lọc + sort sẵn, không cần bước Sort

• Covering (INCLUDE total): Index Only Scan, không chạm heap

Plan: Index Only Scan → Limit. Trên 100M rows, dưới 1ms.

Tổng hợp 4 khái niệm

Concept Khi nào tỏa sáng Khi nào cẩn thận Nested Loop Outer table rất nhỏ + inner có index Outer table lớn → random I/O chồng chất, thua Hash Join Bitmap Scan Số dòng khớp vừa phải, nhiều index kết hợp qua BitmapAnd/Or Lossy bitmap khi work_mem nhỏ, không hỗ trợ ORDER BY tốt Hash Index Cột giá trị dài + chỉ equality + cần tiết kiệm disk Không range, không sort — B-Tree gần như luôn đủ Partial Index Chỉ query một phần nhỏ bảng, soft delete, unique có điều kiện WHERE query phải match WHERE index; cần quản lý nếu dùng mốc thời gian

Đây không phải là 4 thứ để nhớ rồi áp dụng máy móc — chúng là những khả năng mà Query Planner có thể chọn. Khi đọc EXPLAIN ANALYZE, bạn sẽ thấy chúng xuất hiện; hiểu cách chúng hoạt động thì mới biết plan đang hợp lý hay cần can thiệp.

Case 1: Nested Loop + Index trên bảng lớn

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'VN';

country = 'VN' khớp 400.000 customers → đây chính là "biến thể tệ nhất" của Nested Loop ở phần trên: outer table (400K customers) không còn nhỏ nữa, nên mỗi index lookup trên orders × 400.000 lần → random I/O chết máy.

Solution — ép Hash Join, index đúng bảng lọc:

CREATE INDEX idx_customers_country ON customers(country);

Plan mong muốn:

Hash Join
  → Index Scan on customers (country = 'VN')   -- 400K rows, lọc nhanh
  → Seq Scan on orders                          -- scan 1 lần, build hash

Nếu Planner vẫn cứng đầu chọn Nested Loop:

SET LOCAL enable_nestloop = off;
EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'VN';
RESET enable_nestloop;

Muốn giữ index cho query khác nhưng ép Hash Join riêng cho query này:

WITH vn_customers AS MATERIALIZED (
    SELECT id FROM customers WHERE country = 'VN'
)
SELECT o.*
FROM orders o
JOIN vn_customers c ON c.id = o.customer_id;

MATERIALIZED ép PostgreSQL tạo temp result trước → Hash Join tự nhiên được chọn.

Case 2: JOIN trên cột low-cardinality

SELECT *
FROM orders o
JOIN order_status s ON s.code = o.status;

status chỉ có 3 giá trị — index trên orders.status match hàng chục triệu dòng mỗi giá trị, gần như vô dụng.

Solution — không index, để Seq Scan + Hash Join làm việc:

DROP INDEX IF EXISTS idx_orders_status;

Plan tối ưu tự nhiên:

Hash Join
  → Seq Scan on order_status   -- 3 rows, build hash gần như instant
  → Seq Scan on orders         -- 100M rows, 1 lần sequential read

Đây là trường hợp Seq Scan đúng là plan tốt nhất — đừng cố ép index.

Nếu cần lọc thêm:

SELECT o.id, o.total, o.created_at
FROM orders o
JOIN order_status s ON s.code = o.status
WHERE o.customer_id = 50000
  AND s.is_active = true;

Index đặt ở cột lọc mạnh nhất — customer_id, không phải status:

CREATE INDEX idx_orders_customer ON orders(customer_id);

Nguyên tắc: Index trên cột giảm số dòng mạnh nhất, không phải trên cột JOIN.

Case 3: Write-heavy table bị chồng index

INSERT INTO order_audit (order_id, action, created_at)
SELECT o.id, 'REVIEWED', NOW()
FROM orders o
JOIN review_queue r ON r.order_id = o.id;

order_audit có 5-6 index → mỗi INSERT phải maintain hết, ghi chậm hẳn.

Solution — audit và giữ tối thiểu:

-- 1. Xem tất cả index + size
SELECT indexname, indexdef,
       pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes WHERE tablename = 'order_audit';

-- 2. Xem index nào thực sự được dùng
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelname::regclass)) as size
FROM pg_stat_user_indexes
WHERE relname = 'order_audit'
ORDER BY idx_scan ASC;   -- idx_scan thấp/= 0 → ứng viên xóa

-- 3. Xóa index không dùng (CONCURRENTLY để không lock bảng)
DROP INDEX CONCURRENTLY IF EXISTS idx_audit_unused_1;
DROP INDEX CONCURRENTLY IF EXISTS idx_audit_unused_2;

Nếu cần bulk insert lớn — tắt index tạm bằng staging table:

CREATE UNLOGGED TABLE audit_staging (LIKE order_audit INCLUDING DEFAULTS);

INSERT INTO audit_staging (order_id, action, created_at)
SELECT o.id, 'REVIEWED', NOW()
FROM orders o
JOIN review_queue r ON r.order_id = o.id;

INSERT INTO order_audit SELECT * FROM audit_staging;
DROP TABLE audit_staging;

Quy tắc: Bảng write-heavy tối đa 2-3 index, mỗi cái phải justify bằng query pattern thực tế.

Case 4: Multi-table JOIN, Planner chọn sai thứ tự

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
JOIN warehouses w ON w.id = p.warehouse_id
WHERE w.region = 'APAC';

Chỉ 5% warehouse thuộc APAC, nhưng Planner bắt đầu quét từ orders (100M dòng) vì bị index "hấp dẫn".

Solution — giúp Planner lọc sớm:

-- Bước 1: đảm bảo thống kê đúng, thường Planner sai vì stats cũ
ANALYZE warehouses; ANALYZE products; ANALYZE orders; ANALYZE customers;

Nếu vẫn sai, ép thứ tự bằng CTE:

WITH apac_products AS MATERIALIZED (
    SELECT p.id AS product_id
    FROM warehouses w
    JOIN products p ON p.warehouse_id = w.id
    WHERE w.region = 'APAC'
)
SELECT o.*, c.*
FROM apac_products ap
JOIN orders o ON o.product_id = ap.product_id
JOIN customers c ON c.id = o.customer_id;

Index cần:

CREATE INDEX idx_warehouses_region ON warehouses(region);
CREATE INDEX idx_products_warehouse ON products(warehouse_id);
CREATE INDEX idx_orders_product ON orders(product_id);

Với query 10+ bảng, tăng giới hạn tìm kiếm thứ tự JOIN:

SET LOCAL join_collapse_limit = 12;

Nguyên tắc: Index ở bảng lọc đầu tiên (nhỏ nhất sau WHERE), không phải bảng lớn nhất.

Case 5: Covering Index quá nặng

CREATE INDEX idx_fat ON orders(customer_id, created_at DESC)
INCLUDE (status, total, note, product_id, shipping_id);

Nhồi quá nhiều cột vào INCLUDE → mỗi B-Tree entry phình to → index bloat, scan range chậm.

Solution — tách gọn, chỉ INCLUDE cột thực sự cần:

-- Kiểm tra size hiện tại
SELECT pg_size_pretty(pg_relation_size('idx_fat')) as index_size;
-- VD: 12 GB cho 100M rows — quá nặng

-- API chỉ thực sự cần:
SELECT id, total, created_at FROM orders
WHERE customer_id = 50000
ORDER BY created_at DESC
LIMIT 20;

-- Xóa cái cũ, thay bằng covering index tối giản
DROP INDEX CONCURRENTLY idx_fat;
CREATE INDEX idx_customer_cover
ON orders(customer_id, created_at DESC)
INCLUDE (total);
-- Size mới: ~4 GB — nhỏ hơn 3 lần

Nếu nhiều API cần cột khác nhau, đừng nhồi hết vào một index — tách riêng theo pattern:

CREATE INDEX idx_cover_history
ON orders(customer_id, created_at DESC) INCLUDE (total);

CREATE INDEX idx_cover_dashboard
ON orders(customer_id, status);

Nguyên tắc: INCLUDE chỉ cột mà query SELECT — không phải mọi cột mà query có thể cần.

Case 6: Composite Index sai thứ tự cột → vô dụng

SELECT * FROM orders
WHERE customer_id = 50000 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

Index sai:

CREATE INDEX idx_wrong ON orders(status, customer_id, created_at DESC);

status = 'PAID' chiếm 70% bảng → Index Scan bắt đầu từ 70 triệu dòng rồi mới lọc customer_id — gần như vô nghĩa.

Solution — equality selectivity cao nhất đặt trước:

CREATE INDEX idx_right ON orders(customer_id, status, created_at DESC);

customer_id = 50000    → 100M xuống ~1.000 rows (selectivity cao nhất)
status = 'PAID'        → lọc tiếp xuống ~700 rows
created_at DESC        → đã sắp sẵn, lấy 20 dòng đầu, dừng

Plan: Index Scan → Limit, không Sort, dưới 2ms trên 100M rows.

Quy tắc: Equality columns xếp theo selectivity giảm dần → Range/ORDER BY column cuối cùng.

Case 7: Function trên cột JOIN giết index

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE LOWER(c.email) = 'hung@example.com';

LOWER(c.email) wrap hàm lên cột → index trên email vô dụng → Seq Scan toàn bộ customers.

Solution — Expression Index, hoặc fix tận gốc:

CREATE INDEX idx_customers_email_lower ON customers(LOWER(email));

Tốt hơn nữa — lưu email lowercase sẵn ở application layer, query trực tiếp WHERE c.email = 'hung@example.com'.

Các trap tương tự:

-- ❌ Wrap function → index chết
WHERE DATE(o.created_at) = '2024-01-15'
WHERE CAST(o.customer_id AS TEXT) = '50000'
WHERE o.total + tax > 1000

-- ✅ Viết lại để index sống
WHERE o.created_at >= '2024-01-15' AND o.created_at < '2024-01-16'
WHERE o.customer_id = 50000
WHERE o.total > 1000 - tax

Nguyên tắc: Bất cứ thứ gì bọc quanh cột trong WHERE/ON — function, cast, phép tính — đều giết index trên cột đó.

Case 8: Implicit type cast trong JOIN

-- customers.id là BIGINT, orders.customer_id là INTEGER (hoặc VARCHAR do tạo sai)
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id;

PostgreSQL tự cast ngầm để so sánh → hoạt động giống function wrap → index trên orders.customer_id bị bỏ qua → Seq Scan.

Solution — fix kiểu dữ liệu:

SELECT c.column_name, c.data_type, c.table_name
FROM information_schema.columns c
WHERE c.table_name IN ('orders', 'customers')
  AND c.column_name IN ('id', 'customer_id');

ALTER TABLE orders ALTER COLUMN customer_id TYPE BIGINT;

Cách phát hiện: EXPLAIN cho Seq Scan trên bảng rõ ràng có index → kiểm tra type mismatch đầu tiên.

Case 9: JOIN + GROUP BY — Composite Index cho cả lọc lẫn aggregate

SELECT o.customer_id,
       DATE_TRUNC('month', o.created_at) AS month,
       SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.segment = 'ENTERPRISE'
  AND o.created_at >= '2024-01-01'
GROUP BY o.customer_id, DATE_TRUNC('month', o.created_at);

Không có index phù hợp → Seq Scan 100M dòng, Hash Join, Sort + GroupAggregate — rất chậm.

Solution:

CREATE INDEX idx_customers_segment ON customers(segment);
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at);

Nếu query chỉ cần một khoảng thời gian cố định, đây chính là Pattern "Gần đây mới quan trọng" của Partial Index đã nói ở đầu bài:

CREATE INDEX idx_orders_2024
ON orders(customer_id, created_at)
WHERE created_at >= '2024-01-01';

Index nhỏ hơn vài lần, scan nhanh tương ứng, chỉ phục vụ đúng pattern cần.

Case 10: OR trong JOIN condition

SELECT * FROM orders o
JOIN products p
  ON p.id = o.product_id
  OR p.alt_code = o.product_code;

OR trong ON phá mọi index strategy → Nested Loop quét toàn bảng, hoặc Seq Scan + filter.

Solution — tách UNION hoặc LEFT JOIN phân tầng:

SELECT o.*, p.*
FROM orders o JOIN products p ON p.id = o.product_id
UNION
SELECT o.*, p.*
FROM orders o JOIN products p ON p.alt_code = o.product_code
WHERE o.product_id IS NULL OR o.product_id != p.id;

Gọn hơn:

SELECT o.*, COALESCE(p1.name, p2.name) AS product_name
FROM orders o
LEFT JOIN products p1 ON p1.id = o.product_id
LEFT JOIN products p2 ON p2.alt_code = o.product_code
    AND p1.id IS NULL;

Index cần:

CREATE INDEX idx_products_alt_code ON products(alt_code);
-- p.id đã có PK index sẵn

Case 11: N+1 ẩn trong subquery correlate

SELECT c.id, c.name,
    (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order,
    (SELECT SUM(o.total) FROM orders o
     WHERE o.customer_id = c.id AND o.created_at >= '2024-01-01') AS revenue_2024
FROM customers c
WHERE c.segment = 'ENTERPRISE';

Mỗi customer chạy 2 subquery → 10.000 enterprise customers = 20.000 lần index lookup.

Solution — chuyển thành JOIN + aggregate một lần:

SELECT c.id, c.name,
    MAX(o.created_at) AS last_order,
    SUM(CASE WHEN o.created_at >= '2024-01-01' THEN o.total ELSE 0 END) AS revenue_2024
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.segment = 'ENTERPRISE'
GROUP BY c.id, c.name;

Scan orders một lần thay vì 20.000 lần. Index hỗ trợ:

CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

Case 12: Partial Index cho JOIN có điều kiện cố định

SELECT c.name, o.id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'PENDING'
  AND o.created_at >= NOW() - INTERVAL '7 days';

Trên 100M orders, PENDING chỉ chiếm 10%, 7 ngày gần nhất còn ít hơn nữa. Full index vẫn phải chứa cả 100M entries dù query chỉ cần vài trăm nghìn.

CREATE INDEX idx_orders_pending_recent
ON orders(customer_id, created_at DESC)
WHERE status = 'PENDING';

Index chỉ chứa ~10M entries thay vì 100M — nhỏ hơn 10 lần, nhanh hơn tương ứng. Kiểm tra lại:

SELECT pg_size_pretty(pg_relation_size('idx_orders_pending_recent'));

Nếu một customer có nhiều đơn PENDING trong 7 ngày, EXPLAIN có thể hiện Bitmap Index Scan → Bitmap Heap Scan thay vì Index Scan thuần — vẫn là Partial Index đang hoạt động, chỉ là Planner chọn chiến lược đọc theo đúng phần "Bitmap Scan" ở đầu bài. Nếu thấy Rows Removed by Index Recheck cao ở bước này, đó là dấu hiệu bitmap đang lossy — tăng work_mem sẽ giúp.

Cheat sheet tổng hợp — 12 case

Tất cả đều kiểm chứng được bằng cùng một lệnh: EXPLAIN (ANALYZE, BUFFERS) — chạy trước và sau mỗi thay đổi, rồi so sánh.