Query optimizer & index nâng cao
Planner dựa trên chi phí và thống kê, các thuật toán join, index đặc biệt (GIN, GiST, BRIN, expression, covering), CTE, generic plan, partition pruning, phân trang sâu, full-text search.
Ở mức nâng cao, câu hỏi không còn là 'thêm index gì' mà là 'vì sao planner chọn plan tệ và làm sao sửa bền vững'. Người phỏng vấn muốn thấy bạn hiểu planner ra quyết định dựa trên gì (thống kê, chi phí ước lượng), đọc được vì sao ước lượng sai, và biết các công cụ ngoài B-tree cho từng loại dữ liệu.
📋 Danh sách câu hỏi
- 11Query planner dựa trên chi phí (cost-based) chọn plan thế nào? Vì sao nó đôi khi chọn plan rất tệ?●●●○ Khó
- 12Giải thích ba thuật toán join: Nested Loop, Hash Join, Merge Join. Khi nào planner chọn mỗi loại?●●●○ Khó
- 13Khi nào dùng GIN, GiST, BRIN, expression index, covering index (INCLUDE)? Cho ví dụ cụ thể.●●●○ Khó
- 14CTE (WITH) có ảnh hưởng tới hiệu năng không? Subquery tương quan và LATERAL dùng khi nào?●●○○ Trung bình
- 15Prepared statement trong Postgres dùng 'generic plan' là gì? Vì sao một truy vấn đôi khi chậm chỉ với vài giá trị tham số?●●●● Cực khó
- 16Partitioning bảng trong Postgres hoạt động thế nào? Khi nào nên và không nên partition?●●●○ Khó
- 17Phân trang sâu (trang 10.000) và đếm tổng số dòng trên bảng lớn — bạn giải quyết thế nào?●●●○ Khó
- 18Bạn cần tìm kiếm toàn văn (full-text search) tiếng Việt cho sản phẩm. Dùng Postgres hay Elasticsearch? Đánh đổi là gì?●●●○ Khó
- 19Index có thể làm hại thế nào? Bạn quản lý 'sức khoẻ index' trên một database production lớn ra sao?●●●○ Khó
- 20Truy vấn báo cáo (OLAP) chạy trên database OLTP làm chậm cả hệ thống. Bạn tách tải phân tích thế nào?●●●● Cực khó
Xem câu trả lời & giải thích
Planner liệt kê các cách thực thi (scan nào, join thứ tự nào, thuật toán join nào), ước lượng số dòng ở từng bước từ thống kê (histogram, giá trị phổ biến, số giá trị khác nhau), quy đổi ra chi phí I/O và CPU, rồi chọn plan rẻ nhất. Plan tệ gần như luôn bắt nguồn từ ước lượng số dòng sai: thống kê lỗi thời, cột tương quan (city và country), dữ liệu lệch, hàm/biểu thức không có thống kê, giá trị tham số khác thường.
- Thống kê thu bằng
ANALYZE(autovacuum tự chạy) trên mẫu dữ liệu;default_statistics_targetquyết định độ chi tiết histogram (tăng cho cột lệch). - Planner giả định các điều kiện độc lập:
city='Hà Nội' AND country='VN'bị ước lượng quá thấp. Sửa bằngCREATE STATISTICS ... (dependencies, ndistinct, mcv). - Sai số nhân lên qua các join: lệch 10 lần ở bước đầu thành lệch 1000 lần sau 3 join → chọn nested loop cho hàng triệu dòng.
- Tham số chi phí:
random_page_costmặc định 4 giả định đĩa quay; trên SSD đặt ~1.1-1.5 để planner bớt e ngại index scan.
- Quy trình: so sánh
rows=ước lượng vớiactual rowsở từng node trong EXPLAIN ANALYZE — node đầu tiên lệch lớn là nơi cần sửa. - Công cụ đọc plan trực quan: explain.dalibo.com, explain.depesz.com.
- Tránh "ép" plan bằng tắt tính năng (
enable_nestloop=off) trên toàn hệ thống; nếu cần, dùng có giới hạn (pg_hint_plan) và ghi lý do.
Tình huống: "Một truy vấn đang chạy 50ms đột ngột chậm 30 giây sau một đợt import dữ liệu lớn." → Thống kê chưa
cập nhật sau import, planner vẫn nghĩ bảng nhỏ. Chạy ANALYZE ngay sau import hàng loạt (đưa vào script import).
Không biết planner dựa trên thống kê; sửa bằng cách thêm index ngẫu nhiên.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Nested Loop: với mỗi dòng bên ngoài, tìm dòng khớp bên trong (lý tưởng khi bên ngoài ít dòng và bên trong có index) — tệ khi cả hai lớn. Hash Join: dựng bảng băm từ bảng nhỏ hơn trong bộ nhớ, quét bảng kia để dò — tốt cho tập lớn với điều kiện bằng, cần work_mem. Merge Join: hai đầu vào đã sắp xếp theo khoá join, đi song song — tốt khi đã có thứ tự sẵn (index) hoặc tập rất lớn.
| Thuật toán | Độ phức tạp | Hợp khi | Rủi ro |
|---|---|---|---|
| Nested Loop | O(n × chi phí tra cứu) | Bên ngoài nhỏ, bên trong có index; LIMIT nhỏ | Ước lượng sai → hàng triệu lần tra |
| Hash Join | O(n + m) | Tập lớn, điều kiện = | Bảng băm tràn work_mem → chia batch ra đĩa |
| Merge Join | O(n + m) nếu đã sắp | Đã có thứ tự, điều kiện bằng/khoảng | Phải sort nếu chưa có thứ tự |
MySQL từ 8.0.18 mới có hash join; trước đó chủ yếu là nested loop (có biến thể block nested loop).
- Thấy
Nested Loopvớiloops=250000trong plan → kiểm tra ước lượng và index của bảng bên trong. - Hash join có
Batches: 16→ bảng băm tràn ra đĩa; tăngwork_memcho phiên báo cáo đó (SET LOCAL), không tăng toàn cục (mỗi node sort/hash của mỗi connection đều có thể dùng tới mức đó).
Câu hỏi nối tiếp: "Vì sao thêm LIMIT 10 làm truy vấn chậm hơn?" → Planner nghĩ sẽ sớm tìm được 10 dòng nên
chọn nested loop/index scan theo thứ tự sort; nếu dòng khớp hiếm, nó quét gần hết bảng. Sửa bằng index phù hợp điều kiện lọc +
sort, hoặc viết lại truy vấn.
Chỉ biết 'JOIN chậm thì thêm index'.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
GIN: giá trị chứa nhiều phần tử — jsonb (@>), mảng, full-text, trigram (LIKE '%abc%' với pg_trgm). GiST: dữ liệu không gian, khoảng thời gian (chống chồng lấp với EXCLUDE), tìm láng giềng gần nhất. BRIN: bảng rất lớn có tương quan vật lý với thứ tự (log theo thời gian) — index cực nhỏ. Expression index: điều kiện trên biểu thức (lower(email)). Covering index: thêm cột vào index để Index Only Scan không cần đọc heap.
CREATE INDEX ON products USING gin (attributes jsonb_path_ops); -- WHERE attributes @> '{"color":"red"}'
CREATE INDEX ON users USING gin (name gin_trgm_ops); -- WHERE name ILIKE '%nguyen%'
CREATE INDEX ON bookings USING gist (room_id, during); -- chồng lấp thời gian
CREATE INDEX ON events USING brin (created_at); -- bảng 5 tỷ dòng chỉ ghi nối tiếp
CREATE INDEX ON users (lower(email)); -- WHERE lower(email) = $1
CREATE INDEX ON orders (customer_id, created_at DESC) INCLUDE (total); -- index only scan cho danh sách
CREATE INDEX ON tasks (due_at) WHERE status <> 'done'; -- partial
- GIN ghi chậm (có fastupdate/pending list) đổi lấy đọc nhanh.
- BRIN chỉ hiệu quả khi thứ tự vật lý khớp với cột — UPDATE ngẫu nhiên phá vỡ tương quan.
- Tìm kiếm tên trong admin panel: pg_trgm GIN thường đủ tốt trước khi cần Elasticsearch.
- Partial unique index cho quy tắc nghiệp vụ:
UNIQUE (user_id) WHERE is_default(mỗi người một địa chỉ mặc định).
Câu hỏi nối tiếp: "Index only scan vẫn đọc heap nhiều?" → Visibility map chưa được cập nhật (bảng ghi nhiều, vacuum
chưa chạy) nên vẫn phải kiểm tra heap. Xem Heap Fetches trong plan; vacuum thường xuyên hơn.
Chỉ biết B-tree; dùng LIKE '%x%' rồi thắc mắc vì sao không dùng index.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Từ Postgres 12, CTE không đệ quy, không có side effect và chỉ dùng một lần được inline vào truy vấn chính (planner tối ưu xuyên qua); trước đó CTE luôn là 'hàng rào tối ưu' được tính trước toàn bộ. Có thể ép bằng MATERIALIZED / NOT MATERIALIZED. Subquery tương quan chạy lại cho mỗi dòng ngoài (đôi khi planner tự chuyển thành join). LATERAL cho phép subquery tham chiếu bảng bên trái — lý tưởng cho bài 'top N mỗi nhóm'.
-- 3 đơn hàng gần nhất của mỗi khách (top-N per group) — dùng được index (customer_id, created_at DESC)
SELECT c.id, o.*
FROM customers c
CROSS JOIN LATERAL (
SELECT id, total, created_at FROM orders
WHERE orders.customer_id = c.id
ORDER BY created_at DESC LIMIT 3
) o
WHERE c.segment = 'vip';
-- Cách khác: window function — phải tính cho mọi đơn rồi lọc
SELECT * FROM (
SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) rn FROM orders o
) t WHERE rn <= 3;
NOT IN (subquery)trả kết quả sai khi subquery có NULL — dùngNOT EXISTS.- CTE
MATERIALIZEDhữu ích khi muốn tính một lần kết quả đắt dùng nhiều chỗ, hoặc ngăn planner chọn plan tệ. - Window function (
row_number,lag,sum OVER) thay cho self-join phức tạp trong báo cáo.
Câu hỏi nối tiếp: "Truy vấn dùng CTE chạy nhanh trên Postgres 15 nhưng chậm trên production Postgres 11." → Khác hành vi inline CTE giữa phiên bản. Viết lại thành subquery hoặc nâng cấp; luôn kiểm tra plan trên đúng phiên bản production.
Không biết NOT IN với NULL; nghĩ CTE luôn chỉ để dễ đọc.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Với prepared statement, 5 lần đầu Postgres lập custom plan theo giá trị cụ thể; sau đó nếu generic plan (không phụ thuộc giá trị) có chi phí ước lượng không tệ hơn, nó chuyển sang dùng generic plan mãi. Với dữ liệu lệch (một tenant có 10 triệu dòng, đa số chỉ vài trăm), generic plan tối ưu cho trường hợp 'trung bình' sẽ cực tệ cho tenant lớn — giống 'parameter sniffing' của SQL Server.
plan_cache_mode = auto | force_custom_plan | force_generic_plan(Postgres 12+) — có thể đặt theo role/phiên.- ORM và driver (node-postgres với named statement, Prisma, JDBC) thường dùng prepared statement ngầm.
- PgBouncer transaction mode trước đây không hỗ trợ prepared statement mức protocol; bản 1.21+ có hỗ trợ — kiểm tra cấu hình.
- Triệu chứng: truy vấn nhanh khi chạy tay trong psql (giá trị literal → custom plan) nhưng chậm trong ứng dụng. Tái hiện bằng
PREPARE+EXECUTE6 lần. - Giải pháp:
force_custom_plancho truy vấn đó, tách đường xử lý cho tenant lớn, hoặc index/thống kê tốt hơn để cả hai plan đều chấp nhận được.
Tình huống: "Khách hàng enterprise lớn nhất than dashboard timeout, khách nhỏ vẫn nhanh." → Dữ liệu lệch + generic plan hoặc thống kê không phản ánh tenant lớn. Kiểm tra plan với đúng tham số của tenant đó; cân nhắc partition/DB riêng cho tenant lớn.
Không biết vì sao truy vấn nhanh trong psql nhưng chậm trong app.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Declarative partitioning chia một bảng logic thành nhiều bảng con theo range (thời gian), list (vùng, tenant) hoặc hash. Lợi ích chính: partition pruning (truy vấn có khoá partition chỉ đọc partition liên quan), xoá dữ liệu cũ tức thì bằng DROP/DETACH PARTITION thay vì DELETE hàng triệu dòng, vacuum/index theo phần nhỏ. Không nên partition bảng nhỏ (< vài chục GB) hoặc khi truy vấn chủ yếu không lọc theo khoá partition — mỗi truy vấn phải chạm mọi partition.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
org_id uuid NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, created_at) -- khoá chính/unique phải chứa khoá partition
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
-- Tạo trước partition tương lai bằng pg_partman hoặc job định kỳ
- Ràng buộc: unique/PK phải bao gồm khoá partition → không có unique toàn cục trên cột khác.
- Quá nhiều partition (hàng nghìn) làm lập plan chậm.
- Use case kinh điển: bảng event/log/audit theo tháng, giữ 13 tháng, DETACH partition cũ để archive ra S3.
- Quên tạo partition tương lai → INSERT lỗi lúc nửa đêm đầu tháng; tạo trước nhiều kỳ + alert, có partition DEFAULT làm lưới an toàn (cẩn thận khi tách sau).
Câu hỏi nối tiếp: "Partition khác sharding thế nào?" → Partition chia trong một database/máy, vẫn có transaction và join bình thường; sharding chia ra nhiều máy, giải quyết giới hạn tài nguyên của một node nhưng mất join/transaction chéo dễ dàng.
Partition để 'tăng tốc mọi thứ'; không biết ràng buộc unique.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
OFFSET lớn buộc DB đọc rồi bỏ qua mọi dòng trước đó → chậm tuyến tính. Dùng keyset pagination (điều kiện theo giá trị dòng cuối với index phù hợp). Nếu bắt buộc nhảy trang: giới hạn số trang tối đa, hoặc 'deferred join' (lấy id bằng index-only scan rồi join). COUNT(*) chính xác trên bảng lớn tốn kém → hiển thị ước lượng (reltuples, EXPLAIN), đếm có giới hạn ('hơn 10.000'), hoặc bộ đếm duy trì riêng.
-- Deferred join: quét index hẹp để lấy id, sau đó mới đọc dòng đầy đủ
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE org_id = $1 ORDER BY created_at DESC OFFSET 50000 LIMIT 20) page USING (id)
ORDER BY o.created_at DESC;
-- Đếm có trần
SELECT count(*) FROM (SELECT 1 FROM orders WHERE org_id = $1 LIMIT 10001) t; -- > 10000 thì hiển thị "10.000+"
- Bot/crawler hay quét trang sâu — giới hạn trang tối đa và rate limit.
- Tìm kiếm có facet và tổng số (e-commerce) thường chuyển sang search engine (Elasticsearch/OpenSearch, Meilisearch) vốn tối ưu cho đếm gần đúng.
- Bộ đếm theo tenant cập nhật bằng trigger/event có thể gây tranh chấp khoá dòng — dùng bộ đếm phân mảnh hoặc cập nhật bất đồng bộ.
Tình huống: "Trang admin hiển thị 'Trang 1 / 48.213' mất 6 giây chỉ vì COUNT." → Bỏ tổng chính xác, hiển thị ước lượng và nút 'Trang sau'; hoặc cache số đếm vài phút.
Chỉ biết OFFSET; không nhận ra COUNT là nguyên nhân chậm.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Postgres full-text (tsvector + GIN) và pg_trgm đủ cho nhu cầu vừa phải: dữ liệu đã ở DB, nhất quán transaction, không thêm hệ thống. Chọn search engine riêng (Elasticsearch/OpenSearch, Meilisearch, Typesense) khi cần: xếp hạng liên quan tinh vi, chịu lỗi chính tả, gợi ý tức thì, facet/aggregation nhanh, phân tích ngôn ngữ tốt, dữ liệu rất lớn. Đánh đổi: phải đồng bộ dữ liệu (nhất quán cuối cùng) và vận hành thêm cluster.
- Tiếng Việt: Postgres không có từ điển tiếng Việt sẵn — thường dùng cấu hình
simple+unaccent(bỏ dấu để tìm 'nguyen' ra 'Nguyễn'); tách từ ghép tiếng Việt cần analyzer chuyên biệt (plugin tiếng Việt cho Elasticsearch/OpenSearch) hoặc chấp nhận tìm theo âm tiết. - Đồng bộ sang search engine: dual write (dễ lệch), outbox/CDC (Debezium → Kafka → indexer) đáng tin hơn; luôn có job reindex toàn bộ.
- Kết hợp ngày nay: hybrid search = BM25 (từ khoá) + vector (ngữ nghĩa, pgvector/kNN), trộn điểm bằng reciprocal rank fusion.
- Cột
tsvectorsinh tự động (generated column) + GIN index; xếp hạng bằngts_rank, đánh trọng số tiêu đề cao hơn mô tả. - Search engine không phải nguồn sự thật: sau khi tìm, tải chi tiết/giá/tồn kho từ DB; lọc quyền/tenant trong truy vấn search.
Câu hỏi nối tiếp: "Sản phẩm mới thêm không xuất hiện trong kết quả tìm kiếm ngay." → Độ trễ index/refresh interval của Elasticsearch và pipeline đồng bộ. Đặt kỳ vọng sản phẩm (vài giây), theo dõi độ trễ pipeline, cho phép tìm theo mã chính xác qua DB.
Nhảy ngay sang Elasticsearch cho 10.000 bản ghi; đồng bộ bằng dual write không có reindex.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Mỗi index tăng chi phí INSERT/UPDATE/DELETE, tăng WAL và replication lag, chiếm RAM cache, mất HOT update, có thể bị bloat, và làm planner có thêm lựa chọn (đôi khi sai). Quản lý: liệt kê index không dùng (idx_scan = 0 trên mọi replica), index trùng lặp/bị bao bởi index khác, index bloat; xoá có kiểm soát; mọi index mới phải có truy vấn cụ thể chứng minh nhu cầu.
- Index
(a)thường thừa khi đã có(a, b)(trừ khi cần nhỏ hơn hoặc unique). - Thống kê sử dụng index là theo từng node — một index không dùng ở primary có thể đang phục vụ truy vấn trên replica.
- Xoá an toàn:
DROP INDEX CONCURRENTLY; có thể thử nghiệm bằng extension hypopg (index giả định) trước khi tạo thật.
- Rà soát index theo quý; giữ tài liệu "index này phục vụ truy vấn nào".
- Bảng ghi rất nóng (event ingest) giữ càng ít index càng tốt; đẩy truy vấn phân tích sang hệ thống khác.
- Theo dõi kích thước index so với bảng — index lớn hơn bảng nhiều lần là dấu hiệu bloat hoặc thiết kế thừa.
Câu chuyện mẫu: "Bảng orders có 23 index, mỗi INSERT ~12ms. Rà soát thấy 9 index không dùng 90 ngày trên mọi node, 4 index bị bao. Xoá 13 index → INSERT 3ms, WAL giảm 35%, replica hết lag giờ cao điểm."
Coi index là 'miễn phí'; xoá index chỉ dựa vào thống kê của primary.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Tách theo mức độ: (1) chạy báo cáo trên read replica riêng (đơn giản, nhưng vẫn là row store, query dài có thể bị huỷ do conflict replication); (2) bảng tổng hợp/materialized view làm mới định kỳ; (3) đưa dữ liệu sang kho phân tích dạng cột (ClickHouse, BigQuery, Snowflake, Redshift, DuckDB cho quy mô nhỏ) qua CDC/ETL. Kho dạng cột nén tốt và chỉ đọc cột cần → nhanh hơn hàng chục đến hàng trăm lần cho aggregation trên hàng tỷ dòng.
| OLTP (row store) | OLAP (column store) | |
|---|---|---|
| Tải | Nhiều giao dịch nhỏ, đọc/ghi theo dòng | Ít truy vấn lớn, quét và tổng hợp nhiều dòng |
| Lưu trữ | Cả dòng liền nhau | Mỗi cột liền nhau, nén mạnh, xử lý vector hoá |
| Cập nhật | Rẻ | Đắt — hợp dữ liệu append |
- Replica:
hot_standby_feedback=ongiảm huỷ truy vấn nhưng gây bloat ở primary;max_standby_streaming_delaylà đánh đổi giữa huỷ truy vấn và độ trễ replica. - Pipeline: CDC (Debezium) → Kafka → ClickHouse; hoặc dịch vụ ELT (Fivetran, Airbyte) → warehouse; dbt để biến đổi.
- Dashboard trong sản phẩm cho khách hàng (customer-facing analytics) thường dùng ClickHouse/Tinybird với API riêng.
- Định nghĩa rõ độ tươi dữ liệu chấp nhận được (5 phút? 1 ngày?) — quyết định kiến trúc pipeline và chi phí.
Câu hỏi nối tiếp: "Số liệu doanh thu ở dashboard lệch với số trong DB." → Pipeline trễ/mất sự kiện, khác định nghĩa (múi giờ, đơn hoàn tiền), hoặc cập nhật/xoá không được đồng bộ. Cần đối soát tự động và một định nghĩa metric duy nhất (semantic layer).
Tăng cấu hình primary để chạy báo cáo; không biết column store.
- Tôi trả lời được câu này mà không cần xem đáp án