Tối ưu SQL query với PostgreSQL: đọc EXPLAIN ANALYZE, chọn index đúng và tránh N+1

Bảng orders lúc mới launch có vài chục nghìn dòng, endpoint GET /customers/:id/orders trả về trong 30ms. Một năm sau bảng lên 4 triệu dòng, p95 của endpoint đó là 1,8 giây, và ai đó trong team đã "fix" bằng cách thêm ba cái index ngẫu nhiên. Không cái nào được dùng. Write chậm đi, read vẫn chậm.

Vấn đề không nằm ở việc thiếu index, mà ở việc đoán. Bài này là quy trình mình dùng mỗi khi một query PostgreSQL chậm: tìm đúng query tốn nhất, đọc plan để biết database thực sự làm gì, chọn loại index khớp với query, rồi tạo index trên production mà không khóa bảng. Ví dụ dùng PostgreSQL 16–18, phần nào khác giữa các version mình sẽ ghi rõ.

TL;DR:

Bắt đầu từ pg_stat_statements, sắp xếp theo total_exec_time, không phải theo cảm giác.
Đọc EXPLAIN (ANALYZE, BUFFERS): so sánh rows ước lượng với rows thực tế, nhân actual time với loops.
Composite index: cột so sánh bằng (=) trước, cột sort/range sau. Partial, covering và expression index giải quyết những case mà index thường bỏ qua.
N+1 và OFFSET lớn là hai lỗi phổ biến nhất trong app dùng ORM, cả hai đều sửa được bằng thay đổi nhỏ.
Trên production luôn dùng CREATE INDEX CONCURRENTLY, đặt lock_timeout, và định kỳ dọn index không dùng.

Bước 1: Tìm query chậm bằng pg_stat_statements

pg_stat_statements là extension đi kèm PostgreSQL, gom các query giống nhau (khác tham số) thành một dòng và cộng dồn thời gian chạy. Nó trả lời câu hỏi quan trọng nhất: query nào đang ăn nhiều thời gian của database nhất.

-- postgresql.conf (cần restart). Trên RDS/Cloud SQL/Supabase/Neon thường đã bật sẵn
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 0)            AS total_ms,
  round(mean_exec_time::numeric, 2)             AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
  rows / NULLIF(calls, 0)                       AS rows_per_call,
  shared_blks_read                              AS blocks_from_disk,
  left(query, 120)                              AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Sắp xếp theo total_exec_time chứ không phải mean_exec_time. Một query 2ms nhưng chạy 3 triệu lần mỗi ngày tốn nhiều tài nguyên hơn một báo cáo 4 giây chạy 10 lần. Cột pct cho bạn biết sửa query đó thì tiết kiệm được bao nhiêu phần trăm tổng thời gian.

Hai cột còn lại đáng để ý. rows_per_call lớn bất thường (ví dụ 5.000 dòng cho một trang danh sách) thường là dấu hiệu thiếu LIMIT hoặc over-fetch. calls lớn bất thường so với lượng request là dấu hiệu N+1, mình quay lại ở phần sau.

Sau khi deploy một bản sửa, reset để đo lại từ đầu:

SELECT pg_stat_statements_reset();

Edge case: pg_stat_statements chỉ thấy query đã chạy xong. Query bị kill vì statement_timeout không có trong đó. Để bắt cả những query đó, bật thêm log_min_duration_statement = '500ms' để log mọi query chậm hơn 500ms, hoặc dùng module auto_explain (auto_explain.log_min_duration) để log luôn execution plan của query chậm. auto_explain với log_analyze = on có overhead đo thời gian trên mọi query, nên trên production mình chỉ bật kèm auto_explain.sample_rate thấp.

Bước 2: Đọc EXPLAIN (ANALYZE, BUFFERS)

Có query rồi, chạy nó với EXPLAIN (ANALYZE, BUFFERS). ANALYZE thực sự chạy query và đo thời gian từng node, BUFFERS cho biết bao nhiêu page đọc từ shared buffers (hit) và bao nhiêu phải đọc từ disk hoặc OS cache (read). Từ PostgreSQL 18, EXPLAIN ANALYZE tự kèm BUFFERS, các version trước phải ghi rõ.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Output minh họa (số liệu trên máy của bạn sẽ khác):

Limit  (cost=48210.55..48212.88 rows=20 width=24) (actual time=812.402..815.977 rows=20 loops=1)
  Buffers: shared hit=1532 read=29871
  ->  Gather Merge  (cost=48210.55..48297.91 rows=749 width=24) (actual time=812.399..815.969 rows=20 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=47210.53..47211.47 rows=374 width=24) (actual time=798.120..798.124 rows=14 loops=3)
              Sort Key: created_at DESC
              Sort Method: top-N heapsort  Memory: 26kB
              ->  Parallel Seq Scan on orders  (cost=0.00..47200.58 rows=374 width=24) (actual time=3.811..797.902 rows=412 loops=3)
                    Filter: ((customer_id = 4821) AND (status = 'paid'::text))
                    Rows Removed by Filter: 1333021
                    Buffers: shared hit=1532 read=29871
Planning Time: 0.214 ms
Execution Time: 816.051 ms

Đọc plan từ trong ra ngoài (node thụt sâu nhất chạy trước). Những gì cần nhìn:

Seq Scan + Rows Removed by Filter lớn: database đọc cả bảng (khoảng 4 triệu dòng, 3 worker mỗi worker 1,33 triệu) để giữ lại 1.236 dòng. Đây là dấu hiệu rõ nhất của thiếu index phù hợp.
loops: actual time và rows là giá trị trung bình cho mỗi lần chạy node. rows=412 loops=3 nghĩa là tổng 1.236 dòng. Với Nested Loop, node bên trong có thể có loops=50000; khi đó actual time=0.020 nhân lên thành 1 giây.
Ước lượng vs thực tế: rows=374 (ước lượng) so với rows=412 (thực tế) là khớp. Khi lệch 100 lần trở lên, planner đang chọn plan dựa trên số liệu sai, thường do statistics cũ (bước 7).
read=29871: gần 30.000 page (8KB mỗi page, khoảng 233MB) phải đọc ngoài shared buffers. Chạy lại lần hai thấy hit tăng, read giảm là do cache ấm lên, đừng nhầm đó là query nhanh hơn.

Ba kiểu scan bạn sẽ gặp nhiều nhất:

Node	Khi nào planner chọn	Ý nghĩa
Seq Scan	Bảng nhỏ, hoặc điều kiện khớp phần lớn bảng, hoặc không có index dùng được	Đọc tuần tự mọi page
Index Scan	Điều kiện chọn lọc cao, cần ít dòng, hoặc index cho sẵn thứ tự `ORDER BY`	Đi theo index rồi nhảy vào heap lấy từng dòng
Bitmap Index Scan + Bitmap Heap Scan	Số dòng khớp trung bình (vài nghìn đến vài trăm nghìn)	Gom vị trí các dòng thành bitmap, rồi đọc heap theo thứ tự vật lý
Index Only Scan	Mọi cột cần đều nằm trong index và page đã được đánh dấu all-visible	Không cần đọc heap (xem `Heap Fetches`)

Bitmap Heap Scan có dòng Recheck Cond và Heap Blocks: exact=... lossy=.... Khi bitmap quá lớn so với work_mem, nó chuyển sang lossy (chỉ nhớ page, không nhớ dòng) và phải kiểm tra lại mọi dòng trong page. Thấy lossy lớn thì cân nhắc tăng work_mem cho session đó hoặc làm điều kiện chọn lọc hơn.

Cảnh báo quan trọng: EXPLAIN ANALYZE chạy thật. Với UPDATE hoặc DELETE, bọc trong transaction rồi rollback:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'refunded' WHERE id = 991;
ROLLBACK;

Bước 3: Chọn index đúng cho query

Composite index: cột bằng trước, cột sort/range sau

Query ở trên lọc customer_id = ? và status = ?, sort theo created_at DESC, lấy 20 dòng. Index khớp với nó:

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

Plan sau khi có index (minh họa):

Limit  (cost=0.56..72.31 rows=20 width=24) (actual time=0.041..0.118 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_status_created_idx on orders  (cost=0.56..4025.10 rows=1122 width=24) (actual time=0.040..0.112 rows=20 loops=1)
        Index Cond: ((customer_id = 4821) AND (status = 'paid'::text))
        Buffers: shared hit=24
Execution Time: 0.141 ms

Không còn node Sort: index đã lưu dữ liệu theo đúng thứ tự created_at DESC bên trong mỗi cặp (customer_id, status), nên database chỉ cần đọc 20 entry đầu tiên rồi dừng. 24 buffer thay vì 31.000.

Quy tắc sắp cột: B-tree sắp theo cột đầu, rồi cột thứ hai trong mỗi giá trị của cột đầu, và cứ thế. Các cột so sánh bằng (=, IN ngắn) đặt trước, cột dùng cho ORDER BY hoặc range (>, <, BETWEEN) đặt sau cùng. Nếu đặt created_at ở giữa, (customer_id, created_at, status), thì điều kiện range trên created_at khiến status phía sau không còn dùng để thu hẹp phạm vi quét được nữa, chỉ còn dùng để lọc từng entry.

Về việc index (a, b) có dùng được cho query chỉ lọc b không: trước PostgreSQL 18 gần như không (trừ khi planner chọn quét toàn bộ index). PostgreSQL 18 thêm skip scan cho B-tree, cho phép dùng index nhiều cột khi cột đầu không có điều kiện, nhưng nó chỉ hiệu quả khi cột đầu có ít giá trị khác nhau. Đừng thiết kế dựa vào skip scan; nó là lưới an toàn, không phải chiến lược.

Covering index với INCLUDE

Nếu một query rất nóng chỉ cần vài cột, đưa các cột đó vào index bằng INCLUDE để có Index Only Scan, không phải nhảy vào heap:

CREATE INDEX CONCURRENTLY orders_customer_created_cover_idx
  ON orders (customer_id, created_at DESC)
  INCLUDE (status, total_cents);

Cột trong INCLUDE không tham gia sắp xếp, chỉ được lưu kèm ở leaf. Cái giá là index to hơn và mỗi lần update total_cents phải ghi thêm vào index. Edge case: Index Only Scan chỉ bỏ qua heap cho những page đã được VACUUM đánh dấu all-visible. Trên bảng write nhiều mà autovacuum chạy không kịp, plan vẫn ghi Index Only Scan nhưng Heap Fetches gần bằng số dòng, tức là không lợi gì.

Partial index cho soft delete

App dùng soft delete thì gần như mọi query đều có deleted_at IS NULL. Partial index chỉ chứa những dòng thỏa điều kiện, nhỏ hơn và nhanh hơn:

CREATE INDEX CONCURRENTLY orders_active_customer_idx
  ON orders (customer_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- Dùng được index: query chứa đúng predicate
SELECT id FROM orders
WHERE customer_id = 4821 AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;

Planner chỉ dùng partial index khi nó chứng minh được điều kiện của query suy ra điều kiện của index. Query thiếu deleted_at IS NULL sẽ không dùng index này. Cẩn thận với predicate có tham số: index WHERE status = 'pending' không dùng được cho prepared statement WHERE status = $1 khi planner dùng generic plan, vì lúc lập plan nó chưa biết $1 là gì.

Expression index cho lower(email)

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_key
  ON users (lower(email));

-- Dùng được: biểu thức giống hệt
SELECT id FROM users WHERE lower(email) = lower('Leo@Example.com');

Biểu thức trong query phải giống hệt biểu thức trong index. WHERE email ILIKE 'leo@example.com' không dùng được index trên. Bản UNIQUE còn chặn luôn trường hợp hai tài khoản Leo@x.com và leo@x.com cùng tồn tại, thứ mà index thường trên email không làm được.

Bước 4: Vì sao index bị bỏ qua (function, LIKE '%...', pg_trgm)

Index B-tree lưu giá trị cột theo thứ tự. Khi bạn bọc cột trong một hàm hoặc phép tính, giá trị đem so sánh không còn là giá trị đã lưu, nên index vô dụng:

-- Sai: cast cột sang date, index trên created_at bị bỏ qua
SELECT count(*) FROM orders WHERE created_at::date = '2026-10-01';

-- Đúng: viết thành range trên chính cột đó
SELECT count(*) FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

Tương tự với WHERE date_trunc('month', created_at) = ..., WHERE amount * 100 = ..., hay WHERE coalesce(region, 'VN') = 'VN'. Hoặc viết lại thành điều kiện trên cột gốc, hoặc tạo expression index khớp với biểu thức.

LIKE cũng có hai mặt. Prefix search LIKE 'INV-2026%' dùng được B-tree, nhưng với collation khác C (phần lớn database dùng en_US.UTF-8 hoặc tương tự) bạn cần index với operator class text_pattern_ops. Còn LIKE '%nguyen%' hoặc ILIKE '%nguyen%' có wildcard ở đầu thì B-tree không giúp được gì, vì không có "điểm bắt đầu" để tìm trong cây.

Giải pháp cho search kiểu "chứa chuỗi" là pg_trgm với GIN index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Prefix search với collation không phải C
CREATE INDEX CONCURRENTLY invoices_number_prefix_idx
  ON invoices (number text_pattern_ops);

-- Contains search, không phân biệt hoa thường
CREATE INDEX CONCURRENTLY customers_name_trgm_idx
  ON customers USING gin (name gin_trgm_ops);

SELECT id, name FROM customers WHERE name ILIKE '%nguyen%' LIMIT 20;

pg_trgm tách chuỗi thành các bộ ba ký tự, GIN index map mỗi bộ ba tới các dòng chứa nó. Edge case: từ khóa dưới 3 ký tự không tạo ra trigram đầy đủ, nên ILIKE '%an%' vẫn chậm. Ở tầng app, mình bắt từ khóa tối thiểu 3 ký tự. GIN cũng làm write chậm hơn đáng kể so với B-tree; với bảng ghi rất nhiều, cân nhắc full-text search (tsvector) hoặc một search engine riêng.

Một nguyên nhân hay bị bỏ sót: kiểu dữ liệu không khớp. So sánh cột bigint với tham số numeric, hoặc cột varchar với tham số kiểu khác mà phải cast cột, có thể khiến planner không dùng index. Khi plan có Filter thay vì Index Cond cho điều kiện mà bạn chắc chắn đã có index, hãy nhìn kỹ các phép cast trong dòng đó.

Bước 5: N+1 query từ ORM

N+1 là khi bạn chạy 1 query lấy danh sách, rồi N query nữa để lấy dữ liệu liên quan cho từng phần tử. Mỗi query đều nhanh, nên không cái nào hiện trong log query chậm, nhưng một request tốn 51 round trip tới database.

import { prisma } from './db'

// Sai: 1 query lấy orders + 50 query lấy customer
export async function listPaidOrdersSlow() {
  const orders = await prisma.order.findMany({
    where: { status: 'PAID' },
    orderBy: { createdAt: 'desc' },
    take: 50,
  })
  return Promise.all(
    orders.map(async (order) => ({
      ...order,
      customer: await prisma.customer.findUnique({ where: { id: order.customerId } }),
    }))
  )
}

// Đúng: để ORM lấy relation một lần
export async function listPaidOrders() {
  return prisma.order.findMany({
    where: { status: 'PAID' },
    orderBy: { createdAt: 'desc' },
    take: 50,
    select: {
      id: true,
      totalCents: true,
      createdAt: true,
      customer: { select: { id: true, name: true } },
    },
  })
}

Với chiến lược mặc định, Prisma chạy query thứ hai dạng WHERE id IN (...) cho tất cả customer, tức là 2 query thay vì 51. Nếu muốn một query duy nhất với JOIN, Prisma có option relationLoadStrategy: 'join' (đang là preview feature), mình phân tích chi tiết ở bài Prisma ORM trong production.

Ngoài ORM, viết thẳng SQL với JOIN cũng là lựa chọn tốt khi bạn cần aggregate:

SELECT o.id, o.total_cents, o.created_at, c.id AS customer_id, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

Cách phát hiện N+1: trong pg_stat_statements, một query SELECT ... FROM customers WHERE id = $1 có calls gấp hàng chục lần số request vào endpoint. Ở tầng app, bật log query của ORM trong môi trường dev và đếm số query mỗi request; mình hay thêm một test đơn giản fail khi một endpoint chạy quá 5 query.

Bước 6: Keyset pagination thay cho OFFSET lớn

OFFSET 100000 LIMIT 20 không "nhảy" tới dòng 100.000. Database vẫn phải đọc và bỏ đi 100.000 dòng trước đó, nên trang càng sâu càng chậm. Thêm nữa, nếu có dòng mới được insert giữa hai lần load, user sẽ thấy dòng bị lặp hoặc bị sót.

Keyset pagination (còn gọi là cursor pagination) dùng giá trị của dòng cuối trang trước làm điểm bắt đầu:

CREATE INDEX CONCURRENTLY orders_customer_created_id_idx
  ON orders (customer_id, created_at DESC, id DESC);

-- Trang đầu
SELECT id, total_cents, created_at FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Trang sau: $2, $3 là created_at và id của dòng cuối trang trước
SELECT id, total_cents, created_at FROM orders
WHERE customer_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

id được thêm vào để làm tie-breaker: hai đơn có cùng created_at vẫn có thứ tự xác định. Phép so sánh row value (created_at, id) < ($2, $3) được PostgreSQL map thẳng vào index, nên trang thứ 5.000 nhanh như trang đầu.

Phía API, đóng gói cursor thành một chuỗi opaque để client không phụ thuộc vào cấu trúc bên trong:

type OrderCursor = { createdAt: string; id: number }

export function encodeCursor(c: OrderCursor): string {
  return Buffer.from(JSON.stringify(c)).toString('base64url')
}

export function decodeCursor(raw: string | null): OrderCursor | null {
  if (!raw) return null
  try {
    const parsed = JSON.parse(Buffer.from(raw, 'base64url').toString('utf8'))
    if (typeof parsed.createdAt !== 'string' || !Number.isInteger(parsed.id)) return null
    return parsed as OrderCursor
  } catch {
    return null
  }
}

Đánh đổi: keyset không cho nhảy thẳng tới "trang 37" và không cho biết tổng số trang. Với feed, lịch sử đơn hàng, log thì đó không phải vấn đề. Với bảng admin cần số trang, giữ OFFSET cho vài trang đầu và giới hạn độ sâu tối đa. Một bẫy nhỏ: created_at kiểu timestamptz có độ chính xác microsecond, còn JavaScript Date chỉ có millisecond. Nếu cursor đi qua Date, bạn có thể bỏ sót dòng; giữ nguyên chuỗi timestamp từ database hoặc dùng cột id tăng dần làm khóa sort duy nhất.

Bước 7: Statistics, autovacuum, transaction dài và lock

Planner chọn plan dựa trên statistics: số dòng, phân bố giá trị, tỉ lệ NULL của từng cột. Statistics được cập nhật bởi ANALYZE (autovacuum tự chạy định kỳ). Sau khi import một lô lớn hoặc xóa nửa bảng, statistics cũ khiến planner ước lượng sai và chọn Nested Loop cho một join cần Hash Join.

-- Bảng nào nhiều dead tuple, lần cuối được vacuum/analyze khi nào
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Chạy ngay sau bulk import, đừng đợi autovacuum
ANALYZE orders;

-- Bảng lớn, write nhiều: cho autovacuum chạy sớm hơn mặc định
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

-- Hai cột tương quan mạnh (city phụ thuộc country): dạy planner biết điều đó
CREATE STATISTICS customers_country_city_stats (dependencies, ndistinct)
  ON country_code, city FROM customers;
ANALYZE customers;

Mặc định autovacuum chỉ chạy khi khoảng 20% bảng là dead tuple (scale factor 0.2). Với bảng 100 triệu dòng, đó là 20 triệu dòng rác trước khi được dọn. Hạ scale factor cho riêng các bảng lớn là thay đổi rẻ nhất mà mình từng thấy có hiệu quả rõ.

Kẻ thù của autovacuum là transaction mở quá lâu. VACUUM không được dọn những phiên bản dòng mà một transaction còn mở có thể cần đọc, nên một transaction quên commit từ sáng khiến bảng phình ra cả ngày. Transaction dài cũng giữ lock:

-- Transaction nào đang mở lâu nhất
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

-- Ai đang chặn ai
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Chặn từ gốc cho role của app
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET statement_timeout = '15s';

Kịch bản sập production kinh điển: một SELECT báo cáo chạy 10 phút, migration chạy ALTER TABLE orders ADD COLUMN ... cần lock ACCESS EXCLUSIVE nên phải xếp hàng chờ. Mọi query mới vào bảng orders, kể cả SELECT đơn giản, lại xếp hàng sau migration. Trong vài giây connection pool cạn, app trả 500. Cách chặn: mọi migration đặt SET lock_timeout = '3s' ở đầu, chờ lâu hơn thì fail và retry sau, thay vì kéo cả hệ thống xuống.

Bước 8: CREATE INDEX CONCURRENTLY và dọn index không dùng

CREATE INDEX thường giữ lock chặn mọi write vào bảng trong suốt thời gian build, với bảng lớn có thể là vài phút đến vài chục phút. Trên production luôn dùng bản CONCURRENTLY:

SET lock_timeout = '3s';

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

-- Nếu lệnh trên fail giữa chừng, nó để lại index INVALID: tìm và xóa rồi tạo lại
SELECT c.relname AS invalid_index
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

DROP INDEX CONCURRENTLY IF EXISTS orders_customer_status_created_idx;

Những điều cần biết về CONCURRENTLY: nó chậm hơn (quét bảng hai lần), nó không chạy được trong transaction block, và nó vẫn phải chờ các transaction đang mở kết thúc ở một số giai đoạn. Điểm thứ hai hay làm hỏng migration: nhiều công cụ migration gửi cả file SQL trong một lần, và nhiều statement gửi chung một lần sẽ chạy trong một implicit transaction. Cách an toàn là để mỗi lệnh CREATE INDEX CONCURRENTLY trong một migration riêng chỉ chứa đúng lệnh đó, và kiểm tra lại với tài liệu của công cụ bạn dùng.

Index không phải miễn phí: mỗi INSERT và mỗi UPDATE chạm cột được index đều phải cập nhật index, và index chiếm RAM trong shared buffers. Định kỳ tìm index không ai dùng:

SELECT
  s.relname       AS table_name,
  s.indexrelname  AS index_name,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Trước khi xóa, kiểm tra ba thứ. Một, statistics được tính từ lần reset gần nhất (xem stats_reset trong pg_stat_database); nếu vừa reset hôm qua thì idx_scan = 0 chưa nói lên gì. Hai, số liệu này là của từng server: index chỉ được dùng bởi query chạy trên read replica sẽ hiện idx_scan = 0 ở primary. Ba, index phục vụ unique constraint hoặc foreign key lookup không được xóa dù ít scan. Từ PostgreSQL 16 có thêm cột last_idx_scan cho biết lần cuối index được dùng, tiện hơn nhiều so với chỉ nhìn bộ đếm.

Những lỗi thường gặp

Thêm index cho từng cột riêng lẻ. Ba index (customer_id), (status), (created_at) không thay được một index (customer_id, status, created_at DESC). Planner có thể kết hợp chúng bằng BitmapAnd, nhưng vẫn phải sort và không dừng sớm được ở LIMIT 20.

Đặt cột range lên trước cột bằng.

-- Sai: range trên created_at đứng trước, status chỉ còn dùng để lọc từng entry
CREATE INDEX ON orders (created_at, status);
-- Đúng cho WHERE status = 'paid' AND created_at >= now() - interval '7 days'
CREATE INDEX ON orders (status, created_at);

Bọc cột trong hàm. WHERE lower(email) = $1 không dùng index trên email, WHERE created_at::date = $1 không dùng index trên created_at. Hoặc viết lại thành range, hoặc tạo expression index khớp đúng biểu thức.

Đo bằng lần chạy thứ hai. Lần chạy đầu đọc từ disk, lần thứ hai đọc từ cache. So sánh trước và sau khi tối ưu phải cùng điều kiện cache, và nhìn Buffers chứ không chỉ nhìn Execution Time.

Chạy EXPLAIN ANALYZE với câu lệnh ghi trên production mà không rollback. Nó chạy thật. Luôn bọc trong BEGIN; ... ROLLBACK;.

OFFSET sâu cho infinite scroll.

-- Sai: trang 500 đọc 10.000 dòng rồi bỏ đi
SELECT * FROM orders ORDER BY created_at DESC OFFSET 9980 LIMIT 20;
-- Đúng
SELECT * FROM orders WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;

CREATE INDEX không có CONCURRENTLY trong giờ cao điểm. Write vào bảng bị chặn cho tới khi build xong. Nếu lỡ fail giữa chừng với CONCURRENTLY, đừng quên dọn index INVALID, vì nó vẫn bị cập nhật mỗi lần write mà không bao giờ được dùng để đọc.