Có index mà query vẫn chậm — đọc EXPLAIN ANALYZE cho đúng

Thứ Ba tuần trước mình nhận một ticket: endpoint tra đơn hàng theo mã khách hàng chạy mất 4 giây. Bảng orders có 2 triệu dòng, và người viết trước đã thêm index cho cột customer_code đàng hoàng. Vậy mà mở EXPLAIN ANALYZE ra, Postgres vẫn đang Seq Scan — index nằm đó nhưng planner không thèm đụng tới.

Đây là kiểu lỗi mình gặp hoài khi làm backend: index không thiếu, chỉ là điều kiện viết sai chỗ. Dưới đây là cách mình đọc EXPLAIN và mấy nguyên nhân khiến index bị bỏ qua.

EXPLAIN ANALYZE — nhìn gì trước?

Đừng đọc cả cây plan từ trên xuống. Mình thường nhìn 4 thứ theo thứ tự:

• Node Type: Seq Scan là quét cả bảng, Index Scan là đi qua index. Thấy Seq Scan trên bảng lớn là có mùi.
• rows estimate vs actual: dòng đầu plan ghi rows=50 nhưng actual rows=180000 là dấu hiệu statistic cũ hoặc điều kiện quá mơ hồ.
• loops: một node chạy loops=1500 gần như chắc chắn là N+1 hoặc join thiếu index.
• Filter vs Index Cond: Filter: nghĩa là dòng đã đọc lên rồi mới lọc — index không tham gia.

Helper nhỏ mình hay nhét vào repo để không phải gõ tay:

import { pool } from "./db";

type PlanNode = {
  "Node Type": string;
  "Actual Rows": number;
  "Actual Total Time": number;
  "Index Name"?: string;
  Filter?: string;
};

export async function explain(sql: string, params: unknown[] = []): Promise<PlanNode> {
  const { rows } = await pool.query(
    `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql}`,
    params,
  );
  const plan: PlanNode = rows[0]["QUERY PLAN"][0].Plan;
  console.log(
    `[${plan["Node Type"]}]${plan["Index Name"] ? " " + plan["Index Name"] : ""}`,
    `rows=${plan["Actual Rows"]}`,
    `time=${plan["Actual Total Time"].toFixed(2)}ms`,
  );
  return plan;
}

Bốn lý do index bị bỏ qua

1. Bọc hàm lên cột

Query gốc là WHERE lower(customer_code) = lower($1). Postgres không biết lower(customer_code) là gì cho tới khi đọc từng dòng, nên index trên customer_code thành vô dụng. Cách chữa: hoặc lưu sẵn dạng chuẩn hoá, hoặc tạo functional index đúng biểu thức:

CREATE INDEX orders_customer_lower_idx ON orders (lower(customer_code));

2. Lệch kiểu dữ liệu

customer_code là text, nhưng code JS truyền xuống kiểu khác hoặc tham số bị cast ngầm khiến index không khớp. Với Drizzle thì type đã ép từ schema, mình chỉ cần chắc là cột và tham số cùng kiểu:

import { pgTable, text, timestamp, uuid, index } from "drizzle-orm/pg-core";

export const orders = pgTable(
  "orders",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    customerCode: text("customer_code").notNull(),
    status: text("status").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    // cột đẳng thức trước, cột range sau — đúng thứ tự mới dùng được
    index("orders_customer_status_created_idx").on(
      t.customerCode,
      t.status,
      t.createdAt.desc(),
    ),
  ],
);

3. Composite index sai thứ tự cột

Đây là lỗi tốn tiền nhất. Index (created_at, customer_code) không giúp gì cho WHERE customer_code = $1 vì nguyên tắc leftmost prefix: chỉ dùng được phần bên trái liên tục. Mình đặt cột so sánh bằng (customer_code, status) lên trước, cột range (created_at) xuống cuối, rồi ORDER BY created_at sát luôn được index đọc theo thứ tự có sẵn, khỏi sort.

4. Planner thấy Seq Scan rẻ hơn — và nó đúng

Nếu điều kiện lấy ra 40% bảng thì quét tuần tự rẻ hơn nhảy index rồi về heap. Bảng nhỏ, hoặc statistic vừa ANALYZE xong cũng cho kết quả tương tự. Gặp case này thì đừng ép index, hãy hỏi lại: query có thật sự cần lấy nhiều dòng vậy không?

Kết quả thật

Sau khi tạo functional index và đổi thứ tự cột composite:

• Query tra theo mã khách hàng: 4.1s xuống 12ms.
• Query theo khách hàng + trạng thái, sắp theo thời gian: từ Sort 180k dòng sang Index Scan 40 dòng.
• Thêm ANALYZE orders định kỳ, độ lệch estimate/actual từ 20x còn dưới 1.5x.
• Tổng thêm 38MB index — đổi bằng 2.4s latency, rất đáng.

Một điều nữa: mình đặt luôn test so plan trong CI. Cách này không bắt được mọi thứ nhưng đủ để ai đó thêm lower() vào cột là pipeline đỏ ngay, chứ không đợi tới lúc production chậm rồi mới mò.

Kết

Đọc EXPLAIN ANALYZE không khó, khó là bỏ thói quen "thấy chậm thì thêm index". Index chỉ là một nửa câu chuyện, nửa còn lại là query có viết đúng để planner dùng được nó hay không.

Bạn có đang giữ plan trong CI không, hay vẫn để production làm người review? Mình tò mò coi team khác xử lý chuyện này thế nào.

📋 Phụ lục thuật ngữ