Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMMid24 phút đọc

SQL chạy thế nào và cách đọc execution plan PostgreSQL

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

Cụm bài Tối ưu truy vấn: 1. SQL chạy thế nào và đọc execution plan · 2. Lab index PostgreSQL trên dataset hãng bay · 3. Thuật toán JOIN và partition

Một câu SQL đúng có thể chạy 0,5 ms hoặc 900 ms tuỳ vào cách database quyết định thực thi nó, và quyết định đó nằm trong execution plan. Đọc xong, bạn đọc được một plan PostgreSQL, tìm ra node tốn nhất và biết câu nào trên production đáng tối ưu. Nên đọc trước Index, EXPLAIN, N+1 và phân trang (B+tree, clustered/secondary, composite, covering, sargable, EXPLAIN của MySQL); bài đó dùng MySQL, cụm này dùng PostgreSQL 16.

1. Tối ưu truy vấn có hai hướng

Ý chính: tối ưu trực tiếp là đổi query hoặc index; tối ưu gián tiếp là đổi dữ liệu hoặc cách truy cập. Luôn thử trực tiếp trước.

Hướng gián tiếp gồm: archive dữ liệu cũ, denormalize, partition, cache, read replica, chống phân mảnh (VACUUM, OPTIMIZE TABLE).

2. Một câu SQL đi qua những bước nào

Ý chính: câu SQL đi qua connection → parser → analyzer/rewriter → planner → executor → tầng lưu trữ. Planner là nơi quyết định nhanh hay chậm.

flowchart LR
  C["Client / JDBC"] --> N["Connection<br/>protocol, auth"]
  N --> P["Parser<br/>text → parse tree"]
  P --> A["Analyzer + Rewriter<br/>kiểm bảng/cột, mở view"]
  A --> O["Planner<br/>sinh và chấm điểm plan"]
  O --> E["Executor<br/>chạy cây plan"]
  E --> S["Access methods<br/>heap, B-tree, GIN"]
  S --> B["Buffer manager<br/>shared_buffers ↔ đĩa"]
  E -.-> T["Transaction / lock<br/>MVCC, WAL"]
  1. Connection: PostgreSQL tạo một process cho mỗi kết nối, nên mở kết nối rất đắt. Vì thế luôn cần pool (HikariCP phía Spring Boot, PgBouncer phía database).
  2. Parser kiểm cú pháp, ra parse tree. Analyzer/Rewriter tra catalog xem bảng, cột, kiểu có tồn tại, thay view bằng định nghĩa của nó, áp row-level security.
  3. Planner liệt kê các cách chạy (scan kiểu gì, join thứ tự nào, thuật toán nào), ước lượng chi phí từ statistics và chọn cách rẻ nhất. Các plan bị loại bạn không bao giờ thấy.
  4. Executor chạy cây plan theo kiểu “kéo”: node cha xin dòng từ node con.
  5. Access methods + buffer manager đọc page 8 KB từ shared_buffers (vùng cache của PostgreSQL), thiếu thì đọc từ OS/đĩa. Ghi đi qua WAL (log ghi trước khi ghi dữ liệu); MVCC (giữ nhiều phiên bản của một dòng) và lock manager giữ ACID.

Khác biệt quan trọng khi đọc plan:

  • PostgreSQL lưu bảng dạng heap: các dòng nằm không theo thứ tự, mọi index trỏ về heap bằng địa chỉ dòng (TID).
  • InnoDB lưu bảng trong clustered index theo PK; secondary index chứa giá trị PK.
MySQL: server layer, storage engine và query cache đã bị bỏ

MySQL cùng ý tưởng nhưng tách server layer (connector → parser → preprocessor → optimizer → executor) khỏi storage engine (InnoDB…).

3. Thứ tự logic của các mệnh đề

Ý chính: về mặt logic, FROM chạy đầu và SELECT chạy gần cuối. Đây không phải thứ tự vật lý: planner được đổi thứ tự, miễn kết quả giống hệt.

flowchart LR
  F["FROM / JOIN"] --> ON["ON"] --> W["WHERE"] --> G["GROUP BY"] --> H["HAVING"] --> S["SELECT"] --> D["DISTINCT"] --> O["ORDER BY"] --> L["LIMIT"]
  • Alias đặt ở SELECT không dùng được trong WHERE vì lúc đó SELECT chưa chạy; dùng được ở ORDER BY.
  • WHERE lọc dòng trước khi gom nhóm, HAVING lọc nhóm sau khi gom.
  • LIMIT chạy cuối, nên ORDER BY ... LIMIT 10 vẫn phải có thứ tự trước khi cắt.

Planner được đẩy điều kiện xuống lúc scan, đổi thứ tự join, dùng index để khỏi sort. Execution plan chính là thứ tự vật lý.

4. Planner chọn plan dựa vào đâu

Ý chính: PostgreSQL dùng cost-based optimizer: chi phí = đơn giá × số lượng. Số lượng (mỗi bước trả bao nhiêu dòng) lấy từ statistics, nên statistics sai thì plan sai.

Đơn giá tính bằng đơn vị trừu tượng:

Tham số Mặc định Ý nghĩa
seq_page_cost 1 đọc một page tuần tự
random_page_cost 4 đọc một page ngẫu nhiên
cpu_tuple_cost 0.01 xử lý một dòng (còn vài đơn giá CPU nhỏ hơn)

Statistics do ANALYZE (hoặc autovacuum, tiến trình dọn dẹp tự động) thu thập vào pg_stats:

  • n_distinct: số giá trị khác nhau.
  • MCV: các giá trị phổ biến nhất.
  • Histogram: phân bố các giá trị còn lại.
  • correlation: thứ tự vật lý của dòng khớp thứ tự giá trị đến đâu.
Xem correlation trong pg_stats và chuyện hạ random_page_cost trên SSD
SELECT attname, n_distinct, correlation FROM pg_stats
WHERE tablename = 'bookings0' AND attname = 'book_date';
-- n_distinct = -1 (mọi giá trị khác nhau), correlation ≈ -0.007

correlation gần 0 nghĩa là các dòng có book_date liền nhau nằm rải rác trên đĩa, đi theo index sẽ nhảy page liên tục. Đó là lý do ở bài 2, truy vấn một năm dữ liệu chọn Seq Scan.

5. EXPLAIN và các option

Ý chính: EXPLAIN chỉ in plan dự kiến; thêm ANALYZE thì câu lệnh chạy thật và có số đo thực tế; thêm BUFFERS thì biết đọc bao nhiêu page từ cache, bao nhiêu từ đĩa.

EXPLAIN SELECT ...;                                 -- chỉ ước lượng, không chạy
EXPLAIN (ANALYZE) SELECT ...;                       -- chạy thật, có thời gian, số dòng, số vòng lặp
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;              -- thêm số page đọc từ cache (hit) và từ OS/đĩa (read)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...; -- trường có tên, đưa vào tool như explain.dalibo.com
  • FORMAT YAML/JSON dễ đọc cho người mới vì mỗi con số có tên trường (Startup Cost, Plan Rows, Actual Loops…).
  • Từ PostgreSQL 18, EXPLAIN ANALYZE tự kèm BUFFERS.
  • Với plan hàng triệu dòng, TIMING OFF giảm overhead đo giờ mà vẫn giữ số dòng.

Mọi plan trong bài là output thật mình chạy trong Docker trên một dataset mô phỏng hãng bay: 2,1 triệu bookings, 3,1 triệu tickets (cách tạo ở bài 2). Thời gian trên máy bạn sẽ khác; tỉ lệ giữa các phương án mới là thứ đáng nhớ.

Prepared statement và generic plan: query đột nhiên chậm dù không ai đổi gì

6. Các kiểu scan

Ý chính: scan là cách lấy dòng từ một bảng. Cần phần lớn bảng thì Seq Scan rẻ nhất, cần ít dòng thì Index Scan, lượng dòng vừa thì Bitmap; Index Only Scan khỏi đọc bảng nhưng phụ thuộc VACUUM.

Kiểu scan Làm gì Hợp khi Cái giá
Seq Scan / Parallel Seq Scan đọc mọi page theo thứ tự vật lý, kiểm điều kiện từng dòng; bảng lớn thì chia cho nhiều worker cần phần lớn bảng, hoặc bảng nhỏ đọc cả bảng dù chỉ cần 1 dòng
Index Scan đi xuống B-tree, với mỗi entry khớp nhảy sang heap lấy dòng ít dòng, hoặc cần thứ tự index mỗi dòng một lần đọc ngẫu nhiên
Index Only Scan trả kết quả từ index khi index chứa đủ mọi cột cần covering index trên bảng đã VACUUM vẫn đọc heap nếu page chưa all-visible
Bitmap Index Scan + Bitmap Heap Scan đánh dấu các page heap có thể chứa dòng khớp vào bitmap, rồi đọc theo thứ tự vật lý, mỗi page một lần lượng dòng vừa, kết hợp nhiều index (BitmapAnd/BitmapOr) mất thứ tự index, ORDER BY phải sort lại

Số đo trên dataset hãng bay:

  • Seq Scan tìm một vé trên bảng không index: gần 30.000 page, bỏ 3,1 triệu dòng, 98 ms.
  • Index Scan cho cùng việc đó: 4 page (ba tầng B-tree + một page heap), khoảng 0,5 ms.
  • Bitmap Heap Scan lấy 5.404 booking của một ngày: 4.458 page heap, 17 ms.

Seq scan không xấu: nó đọc tuần tự và không nhảy qua lại giữa index và bảng. Trong plan, phân biệt Index Cond (điều kiện dùng để đi trong index) với Filter (kiểm sau khi đã lấy dòng).

Index Only Scan có một điều kiện ngầm. Index của PostgreSQL không lưu thông tin MVCC, nên executor phải hỏi visibility map (bản đồ ghi page nào mọi transaction đều thấy hết, gọi là all-visible). Page chưa all-visible thì vẫn phải đọc heap, đếm ở Heap Fetches.

Output thật của bốn kiểu scan, Recheck Cond và bitmap lossy

Seq Scan: tìm một vé trên bảng không có index. 1.033.333 dòng bị bỏ × 3 tiến trình = 3,1 triệu dòng.

Gather  (cost=1000.00..46777.93 rows=1 width=46) (actual time=97.307..97.876 rows=0 loops=1)
  Workers Launched: 2
  Buffers: shared hit=2080 read=27552
  ->  Parallel Seq Scan on tickets0  (cost=0.00..45777.83 rows=1 width=46) (actual time=93.640..93.640 rows=0 loops=3)
        Filter: (ticket_no = '0005435703703'::bpchar)
        Rows Removed by Filter: 1033333

Index Scan: bốn page thay vì 30.000.

Index Scan using tickets0_ticket_no_idx on tickets0  (cost=0.43..8.45 rows=1 width=46) (actual time=0.517..0.518 rows=1 loops=1)
  Index Cond: (ticket_no = '0005434508964'::bpchar)
  Buffers: shared hit=1 read=3

Index Only Scan: update 5.404 dòng của một ngày rồi chạy cùng một câu trước và sau VACUUM.

-- Ngay sau UPDATE
Index Only Scan using bookings0_date_ref_amount on bookings0 (actual time=0.016..2.334 rows=881 loops=1)
  Heap Fetches: 1762
  Buffers: shared hit=1820
-- Sau VACUUM bookings0
Index Only Scan using bookings0_date_ref_amount on bookings0 (actual time=0.015..0.242 rows=881 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=58

Bitmap Index Scan + Bitmap Heap Scan: biến I/O ngẫu nhiên thành gần tuần tự.

Bitmap Heap Scan on bookings0  (actual time=0.608..17.179 rows=5404 loops=1)
  Recheck Cond: ((book_date >= '2017-03-01') AND (book_date < '2017-03-02'))
  Heap Blocks: exact=4458
  ->  Bitmap Index Scan on bookings0_book_date_idx  (actual time=0.330..0.330 rows=5404 loops=1)

Recheck Cond cần vì khi bitmap vượt work_mem (bộ nhớ cho mỗi phép sort/hash) nó thành lossy: chỉ nhớ page chứ không nhớ dòng (Heap Blocks: lossy=...), nên phải kiểm lại điều kiện.

7. Đọc một execution plan

Ý chính: plan là một cây; node thụt sâu nhất chạy trước và trả dòng lên cha. Đọc từ trong ra ngoài, và luôn nhân actual time với loops trước khi kết luận node nào tốn nhất.

Với mỗi node, hỏi ba câu: lấy dữ liệu bằng cách nào, ước lượng bao nhiêu dòng, thực tế bao nhiêu.

EXPLAIN (ANALYZE, BUFFERS)
SELECT b.book_ref, t.ticket_no
FROM bookings b JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_date >= '2017-03-01 10:00' AND b.book_date < '2017-03-01 10:10';
Gather  (cost=1000.43..27693.57 rows=53 width=21) (actual time=1.280..54.726 rows=50 loops=1)
  Workers Launched: 2
  Buffers: shared hit=132 read=13394
  ->  Nested Loop  (cost=0.43..26688.27 rows=22 width=21) (actual time=5.045..48.992 rows=17 loops=3)
        ->  Parallel Seq Scan on bookings b  (cost=0.00..26501.00 rows=15 width=7) (actual time=4.967..48.562 rows=12 loops=3)
              Filter: ((book_date >= '2017-03-01 10:00') AND (book_date < '2017-03-01 10:10'))
              Rows Removed by Filter: 699988
              Buffers: shared hit=74 read=13302
        ->  Index Scan using tickets_book_ref_idx on tickets t  (cost=0.43..12.46 rows=2 width=21) (actual time=0.031..0.032 rows=1 loops=37)
              Index Cond: (book_ref = b.book_ref)
              Buffers: shared hit=58 read=92
Execution Time: 54.745 ms
  1. Parallel Seq Scan on bookings: ba tiến trình (loops=3), mỗi tiến trình bỏ ~700.000 dòng để tìm ~12 dòng, chiếm gần hết 13.400 page. Bảng bookings không có index trên book_date.
  2. Index Scan on tickets chạy 37 lần, mỗi lần ~0,03 ms nhờ index trên tickets.book_ref.
  3. Nested Loop ghép hai phía, Gather gom kết quả từ worker.

Kết luận: index bookings(book_date) là thứ có tác dụng; phía tickets đã ổn.

Checklist khi đọc plan:

  1. Node nào tốn nhất sau khi nhân loops?
  2. rows ước lượng lệch thực tế bao nhiêu lần? Trên 10 lần ở node dưới thường giải thích vì sao node trên chọn sai thuật toán.
  3. Điều kiện nào nằm ở Filter mà lẽ ra nên ở Index Cond?
  4. Có Sort/Hash với temp written, có Heap Fetches lớn không?
  5. Lần chạy thứ hai có nhanh hẳn không (read thành hit)? Nếu có, câu hỏi thật là dữ liệu nóng có vừa RAM không.
Bảng tra: ý nghĩa từng trường trong plan
Trường Ý nghĩa
cost=0.43..8.45 Startup cost (trước dòng đầu tiên; Sort có startup cao) .. total cost, đã gồm node con
rows= / width= ước lượng Số dòng dự kiến mỗi lần chạy / số byte trung bình mỗi dòng
actual time=a..b ms đến dòng đầu .. đến dòng cuối, cho một vòng lặp
rows= thực tế, loops= Số dòng trung bình mỗi vòng / số lần node được chạy
Rows Removed by Filter Dòng đọc lên rồi bỏ: đọc thừa, ứng viên cho index
shared hit / read Page lấy từ shared_buffers / phải đọc từ OS hoặc đĩa
temp read/written Sort/Hash vượt work_mem và tràn ra đĩa

8. Tìm câu query nào đáng tối ưu

Ý chính: xếp hạng theo tổng thời gian, không theo lần chạy chậm nhất. Một câu chạy 5.000 lần mỗi phút mất 20 ms đáng tối ưu hơn một câu chạy mỗi ngày một lần mất 2 giây.

pg_stat_statements gom thống kê theo câu lệnh đã chuẩn hoá (hằng số thành $1):

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (cần restart)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT left(query, 60) AS query, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Các nguồn khác để tìm query chậm: log, auto_explain, MySQL, Hibernate
  • log_min_duration_statement = 500ms ghi log câu chậm kèm tham số.
  • Extension auto_explain ghi luôn plan của câu chậm. Quý vì plan lúc sự cố có thể khác plan bạn chạy lại sau.
  • MySQL có slow query log và sys.statements_with_runtimes_in_95th_percentile.
  • Phía ứng dụng: APM/tracing, hibernate.generate_statistics, hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS, hoặc datasource-proxy để đếm query mỗi request.

9. Case study: API chậm dần sau một đợt import

Ý chính: khi query chậm đột ngột sau một đợt load dữ liệu mà không có deploy, nghi statistics cũ trước. ANALYZE để cầm máu, rồi sửa dữ liệu để vấn đề không quay lại.

Bối cảnh (kịch bản minh hoạ dựa trên một kiểu sự cố hay gặp). API “lịch sử đặt chỗ của khách” có p95 khoảng 40 ms (95% request nhanh hơn mức này). Sau khi import 3 triệu booking từ hệ thống cũ, p95 lên 2,5 giây; không có deploy nào. Ràng buộc: không downtime, sửa trong ngày.

Điều tra.

  • pg_stat_statements: câu ... WHERE t.passenger_id = $1 ORDER BY b.book_date DESC LIMIT 20 đứng đầu theo tổng thời gian, mean từ 3 ms lên 1.800 ms.
  • Plan từ auto_explain: node tickets ước lượng rows=1 nhưng thực tế rows=48000. Hệ thống cũ ghi '0000 000000' cho mọi khách không khai báo giấy tờ.
  • Tin rằng chỉ có 1 dòng, planner chọn nested loop rồi sort; thực tế là 48.000 vòng lặp.
  • pg_stat_user_tables.last_autoanalyze của tickets có từ trước đợt import.

Lựa chọn và quyết định. Ép plan (tắt nested loop, hint) tác dụng tức thì nhưng ảnh hưởng mọi câu khác và che giấu vấn đề dữ liệu. Thêm index mới chưa chắc được chọn khi ước lượng vẫn sai. Đội chọn:

  1. Chạy ANALYZE tickets ngay (vài giây, không chặn ghi) để cầm máu.
  2. Migration đổi giá trị placeholder thành NULL.
  3. Thêm ANALYZE vào cuối job import.
  4. Cảnh báo khi mean_exec_time của top query tăng gấp 3 so với tuần trước.

Bài tập

Bài 1: Đọc plan nested loop

Đề: với plan ở mục 7, node nào tốn nhất, tổng thời gian của Index Scan on tickets là bao nhiêu, và khi thêm index bookings(book_date) thì plan nhiều khả năng đổi thế nào?

Lời giải Bài 1
  • Tốn nhất là Parallel Seq Scan on bookings (~48,5 ms mỗi tiến trình, 13.302 page).
  • Index Scan on tickets tốn khoảng 0,032 × 37 ≈ 1,2 ms.
  • Có index book_date thì phía bookings thành Index Scan vài page cho ~37 dòng, Gather biến mất vì không cần song song, và tổng thời gian xuống dưới 1 ms.
  • Phía tickets vẫn là Index Scan bên trong nested loop.

Bài 2: Tối ưu một câu query trong dự án của bạn

Đề (bài về nhà của khoá, diễn đạt lại): chọn một câu query thật trong dự án, tối ưu nó, trình bày giải pháp và giải thích vì sao nó nhanh hơn.

Lời giải Bài 2: khung trình bày
  1. Câu SQL, tham số thật và tần suất gọi.
  2. Plan trước khi sửa, đánh dấu node tốn nhất và chỗ ước lượng lệch.
  3. Giả thuyết: thiếu index, không sargable (điều kiện không dùng được index), ước lượng sai, sort tràn đĩa.
  4. Một thay đổi mỗi lần.
  5. Plan sau khi sửa với cùng tham số, so Execution Time và Buffers.
  6. Cái giá: dung lượng index, tốc độ ghi, câu khác có bị đổi plan không.

Ghi nhớ

  • Kể được các bước một câu SQL đi qua, và planner dựa vào gì để chọn plan.
  • Giải thích được vì sao alias của SELECT không dùng được trong WHERE, và vì sao plan vật lý vẫn được phép khác thứ tự logic.
  • Biết khi nào planner chọn Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan, và vì sao Heap Fetches cao là dấu hiệu thiếu VACUUM.
  • Đọc được một plan: tìm node tốn nhất sau khi nhân loops, so rows ước lượng với thực tế.
  • Biết phản xạ đầu tiên khi query chậm sau một đợt import, và vì sao không ép plan.

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

Junior

EXPLAIN và EXPLAIN ANALYZE khác nhau thế nào?

EXPLAIN chỉ in plan dự kiến và chi phí ước lượng, không chạy câu lệnh; EXPLAIN ANALYZE chạy thật.

EXPLAIN ANALYZE in thêm thời gian, số dòng, số vòng lặp thực tế, nên với câu ghi phải bọc trong BEGIN ... ROLLBACK. Thêm BUFFERS để biết số page đọc từ cache và từ đĩa.

Vì sao không dùng alias của SELECT trong WHERE được?

Vì theo thứ tự logic, WHERE được đánh giá trước SELECT, nên lúc đó alias chưa tồn tại.

Thứ tự logic là FROM/JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. ORDER BY chạy sau SELECT nên dùng được alias. Plan vật lý có thể khác thứ tự này miễn kết quả giống.

Seq Scan có phải luôn là dấu hiệu xấu?

Không. Khi cần phần lớn bảng hoặc bảng nhỏ, đọc tuần tự rẻ hơn đi index rồi nhảy ngẫu nhiên sang bảng.

Đáng lo khi Seq Scan trên bảng lớn trả rất ít dòng so với Rows Removed by Filter.

Mid

Index Scan, Index Only Scan và Bitmap Heap Scan khác nhau ra sao?

Khác ở cách chạm vào bảng: từng dòng một, không chạm, hay gom theo page.

  • Index Scan đi index rồi lấy từng dòng từ heap theo thứ tự index; hợp với ít dòng hoặc khi cần thứ tự.
  • Index Only Scan trả kết quả từ index, chỉ đọc heap khi page chưa all-visible.
  • Bitmap scan gom page cần đọc thành bitmap rồi đọc theo thứ tự vật lý; hợp với lượng dòng trung bình và kết hợp được nhiều index, nhưng mất thứ tự.
cost=0.43..8.45 và actual time=0.5..0.6 rows=1 loops=37 nghĩa là gì?

Cost là ước lượng theo đơn vị trừu tượng; actual time và rows là số đo thật của một vòng lặp.

Cost: 0.43 trước dòng đầu, 8.45 tổng chi phí của node kể cả node con. Actual time là ms cho một vòng lặp; node chạy 37 lần nên tổng khoảng 22 ms và trả khoảng 37 dòng.

Vì sao rows ước lượng lệch xa thực tế là dấu hiệu quan trọng nhất trong plan?

Vì mọi quyết định của planner (kiểu scan, thuật toán và thứ tự join, có song song không) đều dựa trên ước lượng số dòng.

Sai ở node dưới lan lên cả cây: tưởng 1 dòng nên chọn nested loop, thực tế 50.000 dòng thành 50.000 lần lookup. Sửa bằng ANALYZE, tăng statistics target, CREATE STATISTICS.

Senior

Query nhanh trong psql nhưng chậm khi chạy từ ứng dụng Java. Nguyên nhân có thể là gì?

Thường là plan khác (generic plan, ép kiểu tham số), hoặc thời gian không nằm ở database.

  • Prepared statement chuyển sang generic plan và plan đó tệ với giá trị phổ biến (kiểm bằng EXPLAIN EXECUTE, thử plan_cache_mode).
  • Kiểu tham số khác kiểu cột gây ép kiểu và mất index.
  • Ứng dụng kéo cả result set về RAM do fetch size.
  • Thời gian thực ra nằm ở chờ lock, chờ connection trong pool, mạng, mapping ORM.

Dùng auto_explain và tracing để tách.

Bạn có tắt enable_seqscan hoặc hạ random_page_cost để planner dùng index không?

enable_seqscan = off thì không trên production; hạ random_page_cost thì có, nếu chạy SSD, và đo lại.

  • enable_seqscan = off chỉ để thử nghiệm xem plan thay thế tốn bao nhiêu.
  • Hạ random_page_cost về 1.1–1.5 hợp lý với SSD và dữ liệu nằm trong RAM; đổi ở mức database và đo lại các câu chính.
  • Planner chọn sai cho một câu thường do ước lượng sai hoặc index không khớp; sửa chỗ đó trước.

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
execution plan /ˌeksɪˈkjuːʃn plæn/ kế hoạch thực thi “The execution plan shows a sequential scan on a two-million-row table.”
query planner /ˈkwɪəri ˈplænə/ bộ lập kế hoạch truy vấn “The planner picked a nested loop because it expected a single row.”
sequential scan /sɪˈkwenʃl skæn/ quét tuần tự toàn bảng “For ninety percent of the table a sequential scan is actually cheaper.”
index-only scan /ˈɪndeks ˈəʊnli skæn/ quét chỉ trên index “It’s an index-only scan, but heap fetches are high because the table hasn’t been vacuumed.”
visibility map /ˌvɪzəˈbɪləti mæp/ bản đồ page đã hiển thị với mọi transaction “The visibility map tells Postgres which heap pages it can skip.”
bitmap heap scan /ˈbɪtmæp hiːp skæn/ đọc heap theo bitmap page “A bitmap heap scan turns random reads into mostly sequential ones.”
spill to disk /spɪl tə dɪsk/ tràn ra đĩa “The sort spilled to disk because it exceeded work_mem.”
generic plan /dʒəˈnerɪk plæn/ plan dùng chung cho mọi tham số “After five executions the prepared statement switched to a generic plan.”

Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.