HieuHN's Blog

[PostgreSQL] Scan Types

Ha Ngoc Hieu's photo
Ha Ngoc Hieu
·

12 min read

Cover Image for [PostgreSQL] Scan Types

Mục lục

Hãy tưởng tượng database của bạn như một cuốn sách khổng lồ. Khi bạn muốn tìm một thông tin cụ thể, bạn có muốn đọc từ đầu đến cuối không? Chắc chắn là không rồi! Thay vào đó, bạn sẽ dùng phần "Mục lục" hoặc "Chỉ mục" (Index) ở cuối sách để tìm nhanh đến đúng trang chứa thông tin bạn cần. Trong thế giới database, Index (Chỉ mục) cũng có vai trò tương tự: nó giúp PostgreSQL tìm kiếm dữ liệu nhanh hơn rất nhiều thay vì phải "đọc" toàn bộ bảng.

Bài viết này tập trung vào cách PostgreSQL sử dụng các Index đó thông qua các kiểu quét (Scan Types) khác nhau. Hiểu được điều này sẽ giúp bạn thiết kế Index hiệu quả và viết câu truy vấn tối ưu hơn. Chúng ta sẽ đi qua 3 kiểu quét chính liên quan đến Index và một kiểu quét cơ bản:

  1. Sequential Scan (Quét Tuần Tự) - Cách cơ bản nhất, không dùng Index.

  2. Bitmap Index Scan (Quét Chỉ mục Bitmap) - Một phương pháp kết hợp hiệu quả.

  3. Index Scan (Quét Chỉ mục) - Cách dùng Index "cổ điển".

  4. Index-Only Scan (Quét Chỉ mục Thuần túy) - Cách dùng Index tối ưu nhất.

Để dễ hình dung, bài viết gốc đã tạo một bảng person với 10 triệu dòng dữ liệu giả lập và một Index trên 3 cột: age (tuổi), register_date (ngày đăng ký), is_active (trạng thái hoạt động). Chúng ta sẽ dựa trên kịch bản này để phân tích.

Hiểu về "Cardinality" (Lực lượng / Độ phân biệt)

Trước khi đi vào các kiểu quét (scan), có một khái niệm cực kỳ quan trọng chúng ta cần nắm: Cardinality. Nó đơn giản là số lượng giá trị duy nhất trong một cột, so với tổng số dòng của bảng.

Ví dụ trong kịch bản thử nghiệm:

  • is_active: Kiểu boolean, chỉ có 2 giá trị (true/false). Cardinality rất thấp.

  • age: Giả sử tuổi từ 1 đến 99. Có 99 giá trị duy nhất. Cardinality trung bình.

  • register_date: Mỗi dòng gần như có một giá trị thời gian khác nhau. Cardinality rất cao.

Tại sao Cardinality quan trọng?

  • Index trên cột Cardinality thấp thường vô dụng: Nếu bạn tạo Index chỉ trên cột is_active, khi bạn tìm WHERE is_active = true, PostgreSQL thấy rằng việc này sẽ trả về khoảng 5 triệu dòng (một nửa bảng). Việc đọc Index rồi nhảy đến 5 triệu vị trí khác nhau trên bảng chính còn chậm hơn là đọc tuần tự toàn bộ bảng. Do đó, PostgreSQL sẽ chọn Sequential Scan.

  • Index trên cột Cardinality cao thường rất hữu ích: Nếu bạn tìm theo điều kiện WHERE register_date với một giá trị cụ thể, Index sẽ giúp PostgreSQL xác định ngay lập tức vị trí của chỉ một vài (hoặc một) dòng cần lấy, hiệu quả hơn nhiều.

Bạn có thể dùng lệnh ANALYZE table_name; để cập nhật thống kê và xem pg_stats để kiểm tra cardinality (cột n_distinct).

Các Kiểu Quét (Scan Types) trong PostgreSQL

PostgreSQL có một bộ phận cực kỳ thông minh gọi là Planner hay Optimizer. Khi bạn gửi một câu lệnh SQL, Planner sẽ phân tích, xem xét các Index hiện có, dựa vào các thông số thống kê (như Cardinality, phân bố dữ liệu) để ước tính chi phí (cost) cho nhiều kế hoạch thực thi (execution plan) khác nhau. Cuối cùng, nó sẽ chọn kế hoạch có chi phí ước tính thấp nhất.

Công cụ để chúng ta "nhìn" vào quyết định của Planner chính là lệnh EXPLAIN. Nó cho bạn biết PostgreSQL dự định dùng kiểu quét nào, dùng Index nào, thứ tự thực hiện ra sao. Thêm ANALYZE (EXPLAIN ANALYZE) sẽ thực sự chạy câu lệnh và cho bạn thấy chi phí thực tế, số dòng thực tế, rất hữu ích để kiểm tra xem ước tính của Planner có chính xác không.

Giờ chúng ta sẽ xem PostgreSQL quyết định dùng cách nào để lấy dữ liệu nhé.

  1. Sequential Scan (Quét Tuần Tự)

  • Cách hoạt động: PostgreSQL đọc toàn bộ lần lượt các khối dữ liệu (data blocks/pages) của bảng theo thứ tự chúng được lưu trữ trên đĩa (hoặc trong bộ nhớ cache). Với mỗi dòng dữ liệu đọc được, nó sẽ kiểm tra xem dòng đó có thỏa mãn điều kiện trong mệnh đề WHERE hay không (nếu có WHERE).

  • Tưởng tượng: Bạn đọc một cuốn sách lần lượt từ trang đầu đến trang cuối, không bỏ sót trang nào để tìm thông tin.

  • Khi nào dùng:

    • Khi không có Index phù hợp cho điều kiện WHERE.

    • Khi truy vấn yêu cầu lấy một phần rất lớn dữ liệu của bảng (ví dụ: lấy 50% số dòng trở lên). Lúc này, chi phí đọc tuần tự toàn bộ còn rẻ hơn chi phí đọc Index rồi nhảy đến quá nhiều vị trí trên bảng chính.

    • Khi bảng rất nhỏ, việc đọc hết cả bảng còn nhanh hơn cả chi phí khởi tạo việc quét Index.

  • Ví dụ: EXPLAIN SELECT * FROM person; (Lấy tất cả dữ liệu không lọc)

      QUERY PLAN
  -----------------------------------------------------------------------
   Seq Scan on person  (cost=0.00..183338.12 rows=10000012 width=44)

    • Seq Scan on person: Cho biết đây là quét tuần tự trên bảng person.

    • cost=0.00..193338.12: Chi phí ước tính (khởi động..tổng cộng). Con số này dùng để so sánh giữa các kế hoạch.

    • rows=100000: Số dòng ước tính trả về.

    • Filter: (age > 90): Điều kiện lọc được áp dụng sau khi đọc mỗi dòng.

  1. Index Scan (Quét Chỉ mục)

  • Cách hoạt động: Đây là cách sử dụng Index "kinh điển". Nó gồm 2 bước cho mỗi dòng kết quả:

    1. Tìm trong Index (Index Lookup) : Sử dụng giá trị trong điều kiện WHERE để duyệt cấu trúc Index (thường là B-Tree) nhằm tìm các mục Index thỏa mãn. Mỗi mục Index này chứa giá trị cột được index và con trỏ (TID - Tuple Identifier) chỉ đến vị trí vật lý của dòng dữ liệu đầy đủ trên bảng chính (Heap

    2. Truy cập Bảng (Heap Fetch): Dùng con trỏ TID vừa tìm được ở bước 1 để truy cập trực tiếp vào khối dữ liệu (page) và vị trí cụ thể (offset) trên bảng chính để lấy toàn bộ dữ liệu của dòng đó.

  • Tưởng tượng: Bạn dùng mục lục sách để tìm số trang (bước 1), rồi lật đến đúng trang đó để đọc nội dung (bước 2). Lặp lại nếu tìm nhiều thông tin ở các trang khác nhau.

  • Khi nào dùng:

    • Khi truy vấn có điều kiện WHERE sử dụng các cột có trong Index và điều kiện đó được đánh giá là rất chọn lọc (highly selective), tức là chỉ trả về một số lượng nhỏ các dòng.

    • Khi chi phí ước tính của (số dòng * (chi phí Index Lookup + chi phí Heap Fetch)) thấp hơn chi phí của Sequential Scan hoặc Bitmap Scan.

  • Ví dụ: EXPLAIN SELECT * FROM person WHERE age = 30 AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;

       QUERY PLAN
  ----------------------------------------------------------------------------------------------------------------
   Index Scan using idx_person_age_date_active on person  (cost=0.56..8.58 rows=1 width=44)
     Index Cond: ((age = 30) AND (register_date = '2023-03-23 19:50:03.22938+00'::timestamp without time zone))

    Vì chúng ta lọc theo cả age và một giá trị register_date rất cụ thể (cardinality cao), PostgreSQL dự đoán chỉ có 1 dòng khớp. Nó dùng Index idx_person_age_date_active để tìm địa chỉ dòng đó (bước 1) rồi lấy dữ liệu dòng đó từ bảng (bước 2). Thời gian cực nhanh (0.064ms). Ngay cả khi lọc theo một khoảng nhỏ (BETWEEN) trả về vài dòng, Index Scan vẫn rất hiệu quả (ví dụ 8 dòng mất 0.337ms).

  1. Index-Only Scan (Quét Chỉ mục Thuần túy)

  • Cách hoạt động: Đây là phiên bản tối ưu của Index Scan. Nó chỉ thực hiện bước 1 (Index Lookup) và bỏ qua bước 2 (Heap Fetch).

  • Tưởng tượng: Thông tin bạn cần (ví dụ: chỉ là tiêu đề chương) đã có ngay trong mục lục sách, bạn không cần lật đến trang nội dung nữa.

  • Điều kiện để PostgreSQL chọn cách này:

    1. Tất cả các cột mà câu truy vấn cần (trong SELECT, WHERE, ORDER BY…) đều đã có sẵn trong Index. Nghĩa là, PostgreSQL có thể lấy đủ thông tin chỉ từ việc đọc Index mà không cần quay lại bảng chính.
  • Visibility Check: CSDL phải có khả năng xác định rằng các dòng (tuples) tìm thấy trong Index là "nhìn thấy được" (visible) đối với transaction hiện tại mà không cần phải truy cập vào bảng chính để kiểm tra thông tin transaction (xmin, xmax). Điều này thường được hỗ trợ bởi một cấu trúc gọi là Visibility Map (VM). VM theo dõi các trang (pages) trong bảng chính mà tất cả các dòng trên đó đều đủ "cũ" (đã được commit và nhìn thấy bởi mọi transaction đang hoạt động). Nếu trang chứa dòng cần lấy được đánh dấu trong VM là "all-visible", PostgreSQL có thể bỏ qua việc kiểm tra trên bảng chính.

  • Ví dụ: EXPLAIN SELECT age, register_date, is_active FROM person WHERE age = 30 AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp; (Lưu ý: SELECT chỉ các cột có trong Index)

       QUERY PLAN
  ----------------------------------------------------------------------------------------------------------------------
   Index Only Scan using idx_person_age_date_active on person  (cost=0.56..4.58 rows=1 width=13)
     Index Cond: ((age = 30) AND (register_date = '2023-03-23 19:50:03.22938+00'::timestamp without time zone))
     Heap Fetches: 0

    Kế hoạch ghi rõ là "Index Only Scan". Quan trọng là dòng Heap Fetches: 0, xác nhận không có lần nào phải đọc từ bảng chính. Thời gian còn nhanh hơn cả Index Scan (0.058ms).

  • Lưu ý: Đừng cố tạo Index "bao phủ" (covering index) chứa tất cả các cột của bảng chỉ để đạt được Index-Only Scan mọi lúc. Index quá lớn sẽ tốn dung lượng, làm chậm ghi dữ liệu và có thể không hiệu quả bằng các kiểu quét khác. Hãy cân nhắc kỹ lưỡng.

  1. Bitmap Index Scan & Bitmap Heap Scan

  • Cách hoạt động: Đây là phương pháp lai, thường hiệu quả khi lấy một lượng dữ liệu "vừa phải" (không quá ít như Index Scan, không quá nhiều như Sequential Scan). Nó gồm 2 giai đoạn:

    1. Bitmap Index Scan: Quét một hoặc nhiều Index liên quan đến điều kiện WHERE. Với mỗi mục Index thỏa mãn, thay vì thực hiện Heap Fetch ngay, nó chỉ đánh dấu vào một cấu trúc dữ liệu trong bộ nhớ gọi là bitmap. Bitmap này có một bit cho mỗi trang (page/block) của bảng chính. Bit tương ứng sẽ được bật lên nếu trang đó chứa ít nhất một dòng thỏa mãn điều kiện Index. Giai đoạn này có thể kết hợp kết quả từ nhiều Index khác nhau (dùng phép AND, OR trên các bitmap).

    2. Bitmap Heap Scan: Sau khi bitmap được xây dựng xong, giai đoạn này sẽ đọc bitmap và truy cập vào bảng chính. Nó chỉ đọc những trang (heap pages) có bit tương ứng được bật trong bitmap. Quan trọng là nó đọc các trang này theo thứ tự vật lý trên đĩa (hoặc gần tuần tự), giúp giảm thiểu random I/O so với Index Scan. Với mỗi dòng đọc được từ các trang này, nó phải kiểm tra lại điều kiện WHERE (gọi là "Recheck Cond") vì bitmap chỉ đảm bảo trang đó có thể chứa dòng khớp, chứ không phải mọi dòng trên trang đó đều khớp.

  • Tưởng tượng: Bạn cần tìm tất cả các trang sách có chứa từ khóa "SQL". Thay vì tìm thấy trang nào lật đọc ngay trang đó (như Index Scan), bạn làm theo cách khác:

    1. Dùng mục lục (hoặc full-text index), tìm tất cả các số trang có chứa từ khóa đó. Lấy một tờ giấy, đánh dấu lại tất cả các số trang này bằng bút nhớ (đây là Bitmap Index Scan - tạo ra một "bitmap" trong bộ nhớ đánh dấu các trang cần đọc).

    2. Bây giờ, bạn lật cuốn sách, nhưng chỉ lật và đọc những trang đã được đánh dấu bằng bút nhớ (đây là Bitmap Heap Scan). Cách này giúp bạn đọc các trang cần thiết một cách tuần tự hơn thay vì nhảy lung tung.

  • Khi nào PostgreSQL chọn cách này?

    • Khi số lượng dòng ước tính trả về là trung bình (ví dụ: vài % đến 20-30% bảng), đủ lớn để Index Scan trở nên chậm do random I/O, nhưng vẫn đủ nhỏ để việc chỉ quét các trang được chọn lọc nhanh hơn Sequential Scan.

  • Ví dụ: EXPLAIN SELECT * FROM person WHERE age = 30; (Lấy khoảng 1% dữ liệu) SQL

  •    QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------
   Bitmap Heap Scan on person  (cost=2180.18..71461.96 rows=101010 width=44)
     Recheck Cond: (age = 30)
     ->  Bitmap Index Scan on idx_person_age_date_active  (cost=0.00..2154.93 rows=101010 width=0)
           Index Cond: (age = 30)

    Bạn thấy 2 node: bên trong là Bitmap Index Scan (tạo bitmap từ Index), bên ngoài là Bitmap Heap Scan (quét bảng dựa trên bitmap). Nó nhanh hơn đáng kể (gấp 2 lần với dữ liệu mẫu) so với Sequential Scan cho cùng query này.

Tổng kết

  1. Ưu tiên Index các cột có Cardinality cao: Tạo Index trên các cột mà bạn thường dùng để lọc (trong WHERE) và có nhiều giá trị khác nhau (như ID, email, timestamp gần như unique). Tránh Index các cột có quá ít giá trị (như giới tính, trạng thái chỉ có vài loại) trừ khi nó là một phần của multicolumn index (chỉ mục đa cột) và được kết hợp với cột có cardinality cao khác.

  2. Thiết kế truy vấn lấy tập dữ liệu nhỏ (nếu có thể): Nếu logic nghiệp vụ cho phép, cố gắng lọc để lấy về số lượng dòng ít nhất có thể. Index Scan và Index-Only Scan cực kỳ hiệu quả cho trường hợp này.

  3. Chỉ SELECT những cột bạn cần: Tránh dùng SELECT * nếu không cần thiết. Việc chỉ chọn các cột có trong Index sẽ mở ra cơ hội cho PostgreSQL sử dụng Index-Only Scan siêu nhanh.

  4. (Nâng cao) Tinh chỉnh tham số:

    • random_page_cost: Giảm giá trị này (mặc định là 4.0) sẽ khiến PostgreSQL "ưa thích" Index Scan hơn Sequential Scan. Với ổ SSD hiện đại, giá trị thấp hơn (ví dụ 1.1 - 2.0) có thể hợp lý.

    • effective_cache_size: Tăng giá trị này (đến khoảng 50-75% tổng RAM nếu server dành riêng cho PostgreSQL) để báo cho PostgreSQL biết có nhiều bộ nhớ cache, giúp nó tự tin hơn khi chọn các kế hoạch dùng Index (vì có khả năng Index và dữ liệu nằm sẵn trong cache).