Đến nội dung chính
Sổ ôn Backend

Tìm kiếm

Database & ORMMid24 phút đọc

Subquery, CTE, CTE đệ quy, window function và bảng tạm

Ôn 16 thẻ
Trong bài này

Cả bài trong một trang: ý chính, sơ đồ, điểm phỏng vấn.

Subquery, CTE, CTE đệ quy, window function và bảng tạm

1Subquery chạy lặp

Correlated subquery (tham chiếu dòng của câu ngoài) trong SELECT chạy lại mỗi dòng ngoài một lần. Viết lại bằng JOIN với bảng đã tổng hợp.

Số dòng ngoài Correlated JOIN bảng tổng hợp
2.000 194 ms 104 ms
200.000 13.812 ms 110 ms

2CTE là đặt tên một bước

CTE (WITH) giúp đọc câu dài theo bước, không phải bảng tạm. PostgreSQL 12+ nhúng CTE vào câu chính nếu dùng một lần.

WITH paid AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders WHERE status = 'PAID'
  GROUP BY customer_id
)
SELECT c.name, p.total
FROM customer c JOIN paid p
  ON p.customer_id = c.id;

3WITH RECURSIVE duyệt cây

Phần neo (dòng bắt đầu) UNION ALL phần đệ quy, lặp tới khi không sinh dòng mới. Cây 5.000 node: 8 ms, so với 778 ms khi Java gửi 5.000 câu.

flowchart TD
  A["Anchor: parent_id IS NULL"] --> R["Join category với vòng trước"]
  R --> Q{"Có dòng mới?"}
  Q -->|có| R
  Q -->|không| E["Trả kết quả"]

4Window function

Tính trên nhóm mà giữ từng dòng. OVER gồm PARTITION BY, ORDER BY và frame (dòng nào được tính cho dòng hiện tại).

  • Xếp hạng: ROW_NUMBER, RANK, DENSE_RANK
  • Dòng trước, sau: LAG, LEAD
  • Chạy sau WHERE: lọc thì bọc thêm một lớp
  • Top-N mỗi nhóm có index: LATERAL ... LIMIT nhanh hơn

5Khi nào dùng bảng tạm

Bảng tạm có dữ liệu thật và index, hợp batch và báo cáo nhiều bước. Đổi lại, nó sống theo connection.

CTE Bảng tạm
Index kết quả trung gian Không Có
Dùng lại Trong một câu Nhiều câu cùng session
Hợp với Truy vấn trong request Batch, báo cáo nặng

💡 Ghi nhớ

  • NOT IN gặp NULL trả rỗng; dùng NOT EXISTS.

  • CTE dùng hai lần bị materialize: điều kiện không đẩy vào trong.

  • PostgreSQL trước 12 luôn materialize CTE.

  • MySQL dừng CTE đệ quy ở 1.000 vòng.

⭐ Điểm phỏng vấn

Tự trả lời trước, rồi bấm từng câu để xem đáp án.

  • CTE khác subquery?

    Kết quả như nhau; CTE dễ đọc, viết được đệ quy.

  • SUM() OVER (ORDER BY day) ra số lạ?

    Frame mặc định RANGE; dùng ROWS và thêm id.

  • CTE chậm 500 lần sau refactor?

    Bị materialize; đưa điều kiện vào trong CTE.

⚠️ Lỗi thường gặp

  • Dữ liệu có vòng lặp: CTE đệ quy chạy mãi trên PostgreSQL.

  • Quên ANALYZE bảng tạm: planner đoán 2.320 dòng thay vì 50.

  • Bảng tạm rò qua connection pool; dùng ON COMMIT DROP.

Câu SQL báo cáo hay có nhiều bước: lọc, tổng hợp, xếp hạng, so với kỳ trước. Bài này so sánh bốn cách chia bước (subquery, CTE, window function, bảng tạm), cách nào đọc dễ, cách nào chạy nhanh, và các bẫy làm kết quả sai mà không báo lỗi. Số đo chạy trên PostgreSQL 16.14 và MySQL 8.4.11, bảng orders 1 triệu dòng. Nên đọc trước SQL chạy thế nào và execution plan và bài tập JOIN, GROUP BY.

Sơ đồ tóm tắt:

flowchart TD
  N{"Cần gì?"} -->|"Một giá trị, một điều kiện"| SQ["Subquery: IN, EXISTS, scalar"]
  SQ -->|"Tương quan, nhiều dòng ngoài"| J["Viết lại: JOIN bảng đã tổng hợp"]
  N -->|"Chia câu dài thành bước có tên"| CTE["CTE: WITH"]
  N -->|"Cây, đồ thị"| R["WITH RECURSIVE + chặn vòng lặp"]
  N -->|"Tính theo nhóm, giữ từng dòng"| W["Window: OVER (PARTITION BY ... ORDER BY ...)"]
  N -->|"Kết quả trung gian lớn, dùng nhiều lần"| T["Bảng tạm + index + ANALYZE"]

1. Subquery: bốn dạng và dạng nào chạy lặp

Ý chính: subquery là một SELECT lồng trong câu khác. IN/EXISTS và subquery trong FROM thường được optimizer viết lại thành join; subquery vô hướng tương quan trong SELECT thì PostgreSQL chạy lại cho từng dòng.

Dạng Ví dụ Optimizer làm gì
Scalar (trả một giá trị) WHERE amount > (SELECT avg(amount) FROM orders) tính một lần
IN / EXISTS WHERE EXISTS (SELECT 1 FROM payment p WHERE p.order_id = o.id) thành semi-join hoặc anti-join (join chỉ kiểm tra có/không)
Derived table (trong FROM) FROM (SELECT customer_id, sum(amount) ...) t thường gộp vào câu ngoài
Correlated scalar trong SELECT (SELECT max(amount) FROM orders o2 WHERE o2.customer_id = o.customer_id) PostgreSQL: SubPlan chạy mỗi dòng ngoài một lần

Đo trên PostgreSQL 16 (có index customer_id), lấy max(amount) của khách cho mỗi đơn:

Số dòng ngoài Correlated subquery JOIN với bảng tổng hợp theo khách
2.000 194 ms (SubPlan, loops=2000) 104 ms
200.000 13.812 ms (loops=200000) 110 ms

Correlated subquery không “luôn sai”. Với ít dòng ngoài và index tốt nó vẫn ổn, nhưng chi phí tăng tuyến tính theo số dòng ngoài. Cách viết lại bằng JOIN tốn gần như cố định vì chỉ tổng hợp một lần.

2. CTE: đặt tên cho một bước, không phải bảng tạm

Ý chính: CTE giúp đọc câu dài theo từng bước. Còn có chạy riêng ra một bảng trung gian (materialize) hay không là việc của optimizer, và hai database làm khác nhau.

WITH paid AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders WHERE status = 'PAID'
  GROUP BY customer_id
)
SELECT c.name, p.total
FROM customer c JOIN paid p ON p.customer_id = c.id
WHERE p.total > 1000;

PostgreSQL 12+ nhúng CTE vào câu chính (như một subquery) khi thoả cả ba điều kiện: không đệ quy, không có tác dụng phụ (không INSERT/UPDATE/DELETE, không hàm VOLATILE), và chỉ được dùng một lần. Ngược lại thì materialize: chạy hết CTE ra bộ nhớ hoặc đĩa rồi mới đọc. Bạn ép được bằng AS MATERIALIZED hoặc AS NOT MATERIALIZED.

Câu WITH x AS (SELECT * FROM orders) SELECT * FROM x WHERE id = 5 Plan Thời gian
Mặc định (nhúng) Index Scan theo PK 0,17 ms
AS MATERIALIZED Seq Scan 1 triệu dòng, rồi CTE Scan lọc 85 ms

MySQL 8 cũng có hai lựa chọn: gộp CTE vào câu ngoài, hoặc materialize thành bảng tạm nội bộ. Khi materialize, nó có thể tự tạo index tạm để join (Index lookup on b using <auto_key0> trong EXPLAIN FORMAT=TREE).

3. CTE đệ quy: duyệt cây trong một câu

Ý chính: WITH RECURSIVE gồm phần neo (anchor, dòng bắt đầu) UNION ALL phần đệ quy (join bảng với kết quả vòng trước). Nó lặp tới khi một vòng không sinh thêm dòng nào.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, name::text AS path

  FROM category WHERE parent_id IS NULL                      -- anchor
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || ' > ' || c.name
  FROM category c JOIN tree t ON c.parent_id = t.id          -- recursive term
)
SELECT id, depth, path FROM tree ORDER BY path;
--  1 | 1 | Sách
--  2 | 2 | Sách > CNTT
--  4 | 3 | Sách > CNTT > Database
--  3 | 3 | Sách > CNTT > Java
--  6 | 4 | Sách > CNTT > Java > Spring

Dùng cho: cây danh mục, sơ đồ tổ chức, bình luận lồng nhau, BOM (định mức vật tư), sinh dãy ngày. Lợi ích chính là một round-trip: lấy cây con 5.000 node mất 8 ms, còn Java gửi một câu cho mỗi node mất 778 ms trên cùng máy (Bài 1).

UNION hay UNION ALL, và các cách lưu cây khác
  • UNION (khử trùng) cũng dừng được vòng lặp, nhưng chỉ khi dòng lặp lại giống hệt dòng cũ. Thêm cột depth hay path là mỗi dòng mới khác nhau, UNION không còn tác dụng. Câu đếm cây con chỉ có cột id dùng UNION thì dừng đúng ở 6 dòng.
  • Đọc cây rất nhiều mà ít sửa thì có các cách lưu khác:
    • Materialized path: lưu chuỗi 1/2/3/, tìm cây con bằng LIKE '1/2/%' có index.
    • Kiểu ltree của PostgreSQL: như materialized path nhưng có toán tử và index GiST.
    • Closure table: bảng (ancestor, descendant, depth) lưu mọi cặp tổ tiên–con cháu, đọc rất nhanh nhưng ghi nhiều.
  • Adjacency list (parent_id) cộng WITH RECURSIVE là lựa chọn mặc định hợp lý: ghi đơn giản, đọc đủ nhanh với cây vài chục nghìn node có index trên parent_id.

4. Window function: tính theo nhóm mà giữ từng dòng

Ý chính: GROUP BY gộp nhiều dòng thành một; window function (... OVER (...)) tính trên một “cửa sổ” các dòng liên quan nhưng vẫn trả về từng dòng.

OVER có ba phần:

  • PARTITION BY: chia nhóm (như GROUP BY nhưng không gộp).
  • ORDER BY: thứ tự trong nhóm, cần cho xếp hạng, luỹ kế, LAG/LEAD.
  • Frame (khung): những dòng nào trong nhóm được tính cho dòng hiện tại, ví dụ ROWS BETWEEN 6 PRECEDING AND CURRENT ROW cho trung bình 7 ngày.

Nhóm hàm hay dùng: xếp hạng ROW_NUMBER, RANK, DENSE_RANK (so sánh ở bài tập JOIN, GROUP BY); giá trị dòng trước/sau LAG, LEAD; luỹ kế SUM(...) OVER (ORDER BY ...); FIRST_VALUE, LAST_VALUE.

Window function được tính sau WHERE, GROUP BY, HAVING và trước ORDER BY, LIMIT. Vì vậy không viết được WHERE row_number() OVER (...) <= 3; phải bọc trong subquery hoặc CTE rồi lọc bên ngoài.

Top-N mỗi nhóm. “3 đơn lớn nhất của mỗi khách” có hai cách. Đo trên 1 triệu đơn, 10.001 khách, index (customer_id, amount DESC):

Cách Thời gian
row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC) rồi lọc rn <= 3 404 ms (đọc hết 1 triệu dòng)
customer CROSS JOIN LATERAL (SELECT ... WHERE customer_id = c.id ORDER BY amount DESC LIMIT 3) 122 ms (mỗi khách đọc 3 dòng index)

LATERAL (MySQL 8.0.14+ cũng có) thắng khi có bảng nhóm riêng và index đúng thứ tự. Khi cần xếp hạng trên toàn bộ dữ liệu, hoặc nhóm rất ít, window function đơn giản hơn.

Xoá dòng trùng bằng ROW_NUMBER trên PostgreSQL và MySQL

Giữ bản có id nhỏ nhất cho mỗi email:

DELETE FROM app_user WHERE id IN (
  SELECT id FROM (
    SELECT id, row_number() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM app_user
  ) t
  WHERE rn > 1
);
  • Chạy được trên cả PostgreSQL 16 và MySQL 8.4 (mình thử: xoá 2 trong 5 dòng).
  • WITH r AS (...) DELETE FROM r WHERE rn > 1 chỉ chạy trên SQL Server. PostgreSQL và MySQL không cho xoá qua CTE.
  • MySQL cấm subquery đọc chính bảng đang xoá: DELETE ... WHERE id IN (SELECT id FROM app_user WHERE ...) báo ERROR 1093. Bọc thêm một derived table như trên thì MySQL materialize nó trước nên chạy được.
  • Xoá xong nhớ thêm UNIQUE (email), không thì tuần sau lại trùng.

5. Bảng tạm: khi nào nên tách bước ra

Ý chính: bảng tạm (CREATE TEMP TABLE) lưu kết quả trung gian thật, có thể đánh index và có thống kê thật. Đổi lại, nó sống theo session (connection), và PostgreSQL không tự thu thống kê cho nó.

Dùng khi kết quả trung gian lớn, được dùng nhiều lần, và optimizer đoán sai số dòng của bước trung gian. Ví dụ: job báo cáo, đối soát, ETL nhiều bước. Không dùng trên đường xử lý request OLTP: mỗi lần tạo bảng tạm PostgreSQL ghi vào system catalog (pg_class, pg_attribute), tạo hàng nghìn lần mỗi phút làm catalog phình to.

Bài tập

Bài 1: Lấy cây con bằng Java và bằng WITH RECURSIVE

Đề: bảng node(id, parent_id) có 5.000 node. Viết hai cách lấy mọi node dưới gốc: (a) Java duyệt BFS, mỗi node một câu SELECT id FROM node WHERE parent_id = ?; (b) một câu WITH RECURSIVE. Đếm số câu SQL, đo thời gian, rồi tạo vòng lặp trong dữ liệu và chứng minh cả hai cách đều dừng.

Lời giải Bài 1: JDK 21, pgjdbc 42.7.7, PostgreSQL 16
/** Mỗi node một câu: cách duyệt cây kiểu "N+1". */
static List<Long> loopInJava(Connection c, long root, int[] queries) throws SQLException {
    List<Long> out = new ArrayList<>();
    Deque<Long> todo = new ArrayDeque<>(List.of(root));
    Set<Long> seen = new HashSet<>();
    try (PreparedStatement ps = c.prepareStatement("SELECT id FROM node WHERE parent_id = ?")) {
        while (!todo.isEmpty()) {
            long id = todo.poll();
            if (!seen.add(id)) continue;                   // chặn vòng lặp
            out.add(id);
            ps.setLong(1, id);
            queries[0]++;
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) todo.add(rs.getLong(1));
            }
        }
    }
    return out;
}

/** Một round-trip; mảng path chặn vòng lặp, giới hạn độ sâu chặn dữ liệu hỏng. */
static List<Long> recursiveCte(Connection c, long root) throws SQLException {
    String sql = """
        WITH RECURSIVE sub AS (
          SELECT id, 1 AS depth, ARRAY[id] AS path FROM node WHERE id = ?
          UNION ALL
          SELECT n.id, s.depth + 1, s.path || n.id
          FROM node n JOIN sub s ON n.parent_id = s.id
          WHERE n.id <> ALL (s.path) AND s.depth < 100
        )
        SELECT id FROM sub
        """;
    List<Long> out = new ArrayList<>();
    try (PreparedStatement ps = c.prepareStatement(sql)) {
        ps.setLong(1, root);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) out.add(rs.getLong(1));
        }
    }
    return out;
}

Dữ liệu: INSERT INTO node SELECT g, ... g / 4 ... FROM generate_series(1, 5000) g, index trên parent_id, chạy ba vòng để JIT và cache ấm rồi lấy vòng cuối:

Java loop : 5000 nodes, 5000 queries, 778 ms
WITH RECURSIVE: 5000 nodes, 1 query, 8 ms
same result: true
with cycle, guarded CTE returns: 5000 nodes

Database và Java chạy cùng máy, nên 778 ms gần như chỉ là chi phí gửi và nhận từng câu. Qua mạng thật (0,5 đến 1 ms mỗi round-trip) cách (a) mất vài giây. Sau UPDATE node SET parent_id = 5000 WHERE id = 1 (gốc thành con của một lá), cả hai cách vẫn dừng nhờ seen và path. Bỏ điều kiện <> ALL (s.path) thì câu CTE chạy tới khi bị timeout.

Bài 2: Báo cáo doanh thu bằng window function

Đề: bảng ord(id, customer_id, day, amount) và app_user(id, email). Viết: (a) doanh thu từng ngày, luỹ kế, chênh lệch so với hôm trước; (b) 2 đơn lớn nhất của mỗi khách, đồng tiền thì lấy id nhỏ hơn; (c) xoá email trùng, giữ id nhỏ nhất. Dữ liệu có hai đơn cùng ngày cùng số tiền để bắt lỗi frame.

Lời giải Bài 2: ba câu SQL và kết quả trên PostgreSQL 16
-- dữ liệu: (1,1,'11-01',100) (2,2,'11-01',40) (3,1,'11-02',70) (4,1,'11-02',70) (5,2,'11-03',90) (6,1,'11-03',10)

-- (a) tổng hợp theo ngày trước, rồi mới chạy window trên kết quả đã gộp
WITH daily AS (SELECT day, sum(amount) AS revenue FROM ord GROUP BY day)
SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) AS running,
       revenue - lag(revenue) OVER (ORDER BY day)               AS vs_prev_day
FROM daily ORDER BY day;
--  2026-11-01 | 140 | 140 |
--  2026-11-02 | 140 | 280 |   0
--  2026-11-03 | 100 | 380 | -40

-- (b) ROW_NUMBER + cột phụ id để kết quả xác định khi đồng tiền
SELECT customer_id, id, amount FROM (
  SELECT customer_id, id, amount,
         row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
  FROM ord
) t WHERE rn <= 2 ORDER BY customer_id, rn;
--  1 | 1 | 100
--  1 | 3 |  70      (đơn 4 cũng 70 nhưng id lớn hơn)
--  2 | 5 |  90
--  2 | 2 |  40

-- (c) xoá trùng (chạy được trên PostgreSQL và MySQL 8)
DELETE FROM app_user WHERE id IN (
  SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY email ORDER BY id) AS rn FROM app_user) t
  WHERE rn > 1);
-- DELETE 2; còn lại id 1, 2, 4

Ghi chú:

  • (a) gộp theo ngày trước nên mỗi ngày đúng một dòng, frame không còn bẫy đồng hạng. ROWS UNBOUNDED PRECEDING vẫn nên ghi rõ để người sau sửa câu không vô tình làm sai.
  • (b) dùng ROW_NUMBER vì đề nói “đúng 2 đơn”. Muốn “giữ cả đơn đồng hạng” thì dùng RANK/DENSE_RANK, và nói rõ cách hiểu với người đặt đề.
  • lag() dòng đầu trả NULL. Báo cáo cần số 0 thì dùng lag(revenue, 1, 0) hoặc COALESCE.

Ghi nhớ

  • Giải thích được vì sao correlated subquery chậm dần theo số dòng ngoài, và viết lại bằng JOIN với bảng đã tổng hợp.
  • Biết khi nào PostgreSQL 12+ nhúng CTE, khi nào materialize, và cách ép bằng MATERIALIZED/NOT MATERIALIZED.
  • Viết được WITH RECURSIVE có độ sâu, path và cách chặn vòng lặp; biết giới hạn 1.000 của MySQL.
  • Nhận ra bẫy frame RANGE mặc định và last_value; biết khi nào LATERAL thắng ROW_NUMBER cho top-N.
  • Biết khi nào dùng bảng tạm, vì sao phải ANALYZE, và vì sao nó nguy hiểm với connection pool.

Câu hỏi phỏng vấn

Junior

CTE khác subquery thế nào?

Về kết quả thì như nhau; CTE đặt tên cho một bước và đặt trước câu chính nên dễ đọc, dùng lại được trong cùng câu, và là cách duy nhất để viết truy vấn đệ quy.

Về hiệu năng, PostgreSQL 12+ và MySQL 8 thường nhúng CTE dùng một lần vào câu chính, nên plan giống subquery. CTE dùng nhiều lần, hoặc ghi MATERIALIZED, thì được tính riêng một lần. Điều đó có thể nhanh hơn hoặc chậm hơn tuỳ điều kiện lọc bên ngoài.

Window function khác GROUP BY thế nào?

GROUP BY gộp mỗi nhóm thành một dòng; window function tính trên nhóm nhưng vẫn trả về từng dòng gốc.

Ví dụ: muốn hiện từng đơn kèm tổng tiền của khách đó thì dùng sum(amount) OVER (PARTITION BY customer_id). Dùng GROUP BY thì mất chi tiết từng đơn, phải join ngược lại. Window function chạy sau WHERE/GROUP BY, nên muốn lọc theo kết quả của nó phải bọc thêm một lớp.

Mid

Lấy 3 đơn mới nhất của mỗi khách hàng thế nào?

row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) trong subquery rồi lọc rn <= 3; hoặc LATERAL ... ORDER BY ... LIMIT 3 cho mỗi khách khi có index (customer_id, created_at DESC).

Window function đọc toàn bộ đơn rồi đánh số: mình đo 404 ms trên 1 triệu đơn. LATERAL chỉ đọc 3 dòng index cho mỗi khách: 122 ms với 10.000 khách. Cần thêm cột phụ (id) vào ORDER BY để thứ tự xác định, và chọn RANK nếu muốn giữ các đơn đồng hạng.

Tổng luỹ kế bằng SUM() OVER (ORDER BY day) ra số lạ. Vì sao?

Vì có ORDER BY mà không ghi frame thì frame mặc định là RANGE ... CURRENT ROW: mọi dòng cùng day được cộng chung, nên hai đơn cùng ngày cùng hiện tổng của cả ngày.

Sửa bằng ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW và thêm cột phân biệt vào ORDER BY (day, id), hoặc gộp theo ngày trước rồi mới tính luỹ kế. last_value() cũng dính cùng bẫy: frame mặc định dừng ở dòng hiện tại nên nó trả về chính dòng đó.

Lưu cây danh mục trong database và lấy cả cây con thế nào?

Mặc định: bảng có parent_id (adjacency list), index parent_id, đọc cây con bằng WITH RECURSIVE trong một câu.

Một câu thay cho một câu mỗi node: mình đo 8 ms so với 778 ms cho 5.000 node. Phải chặn vòng lặp (mảng path, CYCLE của PostgreSQL 14+, giới hạn độ sâu) vì PostgreSQL không tự dừng. Đọc rất nhiều, ít sửa thì cân nhắc materialized path, ltree hoặc closure table.

Senior

Một CTE làm truy vấn chậm 500 lần sau khi nâng cấp hoặc refactor. Bạn nghĩ tới gì?

CTE bị materialize, nên điều kiện lọc bên ngoài không được đẩy vào trong: database chạy hết CTE trên toàn bảng rồi mới lọc.

Nguyên nhân hay gặp: CTE được dùng hai lần, có hàm VOLATILE, có ghi MATERIALIZED, hoặc đang chạy PostgreSQL < 12 (luôn materialize). Mình đo: nhúng 0,17 ms, materialize 85 ms cho cùng câu lấy một dòng theo PK. Kiểm tra bằng EXPLAIN: thấy CTE Scan kèm Rows Removed by Filter lớn. Sửa: đưa điều kiện vào trong CTE, NOT MATERIALIZED, hoặc tách CTE dùng hai lần thành hai bước rõ ràng.

Khi nào bạn chia báo cáo thành nhiều bước với bảng tạm thay vì một câu lớn?

Khi kết quả trung gian lớn, được dùng nhiều lần, và optimizer ước lượng sai số dòng của bước trung gian khiến cả câu chọn plan tệ.

Bảng tạm cho dữ liệu thật, index và thống kê, nhưng phải tự ANALYZE: mình thấy planner đoán 2.320 dòng trước ANALYZE, 50 dòng sau. Cái giá: ghi dữ liệu, ghi catalog, và nó sống theo connection, nên trong ứng dụng có pool phải dùng ON COMMIT DROP. Mình chỉ làm vậy cho batch và báo cáo; trên đường request thì sửa câu, thống kê hoặc index trước.

Correlated subquery có luôn chậm không? Bạn quyết định viết lại thế nào?

Không luôn chậm: nó chạy lại cho mỗi dòng ngoài, nên ổn khi ít dòng ngoài và có index, nhưng chi phí tăng tuyến tính theo số dòng ngoài.

IN/EXISTS thường được viết lại thành semi-join, nên ít đáng lo. Scalar subquery có aggregate trong SELECT thì PostgreSQL chạy thành SubPlan từng dòng: mình đo 194 ms với 2.000 dòng ngoài nhưng 13,8 giây với 200.000 dòng. Bản JOIN với bảng tổng hợp gần như cố định ở khoảng 110 ms. Quyết định dựa trên loops= trong EXPLAIN ANALYZE và số dòng ngoài dự kiến khi dữ liệu lớn lên.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
common table expression /ˈkɑː.mən ˈteɪ.bəl ɪkˈspreʃ.ən/ CTE, bước có tên trong câu WITH “I use common table expressions to break a long report into named steps.”
correlated subquery /ˈkɔːr.ə.leɪ.t̬ɪd ˈsʌbˌkwɪr.i/ subquery tham chiếu dòng của câu ngoài “The correlated subquery ran once per outer row, so it scaled linearly.”
materialize /məˈtɪr.i.ə.laɪz/ tính ra kết quả trung gian thật “Because the CTE was referenced twice, Postgres materialized it.”
inline /ˈɪn.laɪn/ nhúng vào câu chính “Since version 12, a CTE used once is inlined into the outer query.”
anchor member /ˈæŋ.kɚ ˈmem.bɚ/ phần neo của CTE đệ quy “The anchor member selects the root; the recursive member walks down.”
cycle detection /ˈsaɪ.kəl dɪˈtek.ʃən/ phát hiện vòng lặp “Without cycle detection, one bad parent_id makes the query run forever.”
window frame /ˈwɪn.doʊ freɪm/ khung dòng được tính cho dòng hiện tại “The default window frame is RANGE, so ties were summed together.”
running total /ˈrʌn.ɪŋ ˈtoʊ.t̬əl/ tổng luỹ kế “A running total needs ROWS and a unique tiebreaker in ORDER BY.”
top-N per group /tɑːp en pɚ ɡruːp/ N dòng đầu mỗi nhóm “For top-N per group, LATERAL with LIMIT beat ROW_NUMBER three to one.”
temporary table /ˈtem.pə.rer.i ˈteɪ.bəl/ bảng tạm theo session “Temporary tables aren’t analyzed by autovacuum, so I run ANALYZE myself.”

Ghi chép tự tổng hợp từ tài liệu chính thức và kinh nghiệm thực tế.