PostgreSQL tìm dữ liệu như thế nào?

SELECT * FROM vehicles WHERE plate = '25F-950.00';

Bảng vehicles có 5 triệu xe và cột plate chưa có index. Câu trên chỉ trả về một chiếc, nhưng để tìm ra nó, PostgreSQL phải đọc cả bảng.

Dữ liệu được lưu thành từng page

PostgreSQL gom các dòng của bảng vào những khối cố định gọi là trang (page), mỗi page mặc định 8 KB. Nó đọc và ghi theo từng page, nên muốn kiểm tra một dòng, nó phải lấy cả page chứa dòng đó.

Bảng vehicles · 5 triệu dòng

plate	brand	model
10A-079.19	Honda	City
10A-158.38	Hyundai	Accent
⋮
25F-950.00	Toyota	Vios
⋮
28W-000.00	Toyota	Vios

Trên đĩa · 192.308 page, mỗi page 8 KB

1. page 0 10A-079.1910A-158.3810A-237.57 …
2. page 1 10C-138.1310C-217.3210C-296.51 …
3. ⋮
4. page 192 25F-870.8125F-950.0025G-029.19 …
5. ⋮
6. page 192.307 28V-841.6228V-920.8128W-000.00 …

Hình rút gọn: mỗi page thật chứa khoảng 26 dòng.

Một dòng xe dài khoảng 290 byte, nên mỗi page chứa được khoảng 26 xe, và 5 triệu xe chiếm 192.308 page (1,5 GB).

PostgreSQL tìm chiếc xe thế nào?

PostgreSQL ghi mỗi dòng vào page nào còn chỗ, không xếp theo biển số. Chưa có index trên plate, nó không có cách nào biết 25F-950.00 nằm ở page nào. Nó chỉ còn cách mở từng page và so biển số của từng dòng với điều kiện WHERE:

Tìm 25F-950.00 8/8 page đã kiểm tra 1 dòng khớp

1. page 0đã đọc

   1. 29A-123.45 ✗
   2. 43B-678.90 ✗
   3. 51G-246.80 ✗
   4. 30E-112.09 ✗

2. page 1đã đọc

   1. 15K-731.26 ✗
   2. 61C-905.33 ✗
   3. 37A-480.17 ✗
   4. 92H-664.02 ✗

3. page 2đã đọc

   1. 74D-218.59 ✗
   2. 88A-357.71 ✗
   3. 20B-993.40 ✗
   4. 65F-041.88 ✗

4. page 3đã đọc

   1. 36C-502.64 ✗
   2. 47P-618.20 ✗
   3. 14A-829.13 ✗
   4. 81G-175.36 ✗

5. page 4đã đọc

   1. 99E-460.21 ✗
   2. 12K-308.75 ✗
   3. 70B-647.92 ✗
   4. 34D-086.50 ✗

6. page 5đã đọc

   1. 59H-913.07 ✗
   2. 26A-251.84 ✗
   3. 83C-739.66 ✗
   4. 18F-472.39 ✗

7. page 6đã đọc

   1. 49G-604.12 ✗
   2. 25F-950.00 ✓ khớp
   3. 63A-187.45 ✗
   4. 11B-826.93 ✗

8. page 7đã đọc

   1. 76K-345.08 ✗
   2. 40E-592.61 ✗
   3. 95D-018.27 ✗
   4. 22C-764.30 ✗

Câu SQL không có LIMIT, nên PostgreSQL phải trả về mọi xe mang biển số đó. Gặp một chiếc rồi nó vẫn đọc tiếp, vì chỉ khi tới page cuối nó mới chắc là không còn chiếc nào khác.

Cách đọc lần lượt mọi page như vậy gọi là quét tuần tự (sequential scan). Với bảng thật, đó là 192.308 page.

Xem PostgreSQL đã làm gì bằng EXPLAIN

Chạy câu SELECT, bạn chỉ nhận về chiếc xe. Kết quả không cho biết PostgreSQL đã quét cả bảng hay tìm qua index.

Muốn biết, đặt EXPLAIN trước câu SQL. PostgreSQL sẽ in ra kế hoạch thực thi (execution plan): các bước nó chọn để chạy câu đó, như đọc bảng nào, bằng cách nào, lọc theo điều kiện gì. Riêng EXPLAIN chỉ in kế hoạch, chưa chạy câu SQL. Thêm ANALYZE thì PostgreSQL chạy câu đó thật và ghi lại số liệu thực tế của từng bước; thêm BUFFERS thì nó đếm luôn số lần truy cập page.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM vehicles WHERE plate = '25F-950.00';

Kết quả dưới đây đã rút gọn và chạy bằng một tiến trình cho dễ đọc; file SQL ở cuối bài chạy đúng như vậy. Mặc định, với bảng cỡ này PostgreSQL thường chia việc quét cho vài tiến trình chạy song song, nhưng tổng số page phải đọc vẫn vậy.

Đọc kết quả EXPLAIN

1. Seq Scan on vehicles (actual rows=1.00 loops=1) Quét tuần tự bảng vehicles. Trả về 1 dòng.
2. Filter: (plate = '25F-950.00'::text) Điều kiện WHERE, được thử trên từng dòng đọc ra.
3. Rows Removed by Filter: 4999999 4.999.999 dòng sai biển số, bị loại. Cộng 1 dòng trả về là đủ 5 triệu.
4. Buffers: shared hit=698 read=191610 698 page đã có sẵn trong bộ nhớ đệm của PostgreSQL (shared buffers), 191.610 page phải nạp vào, có thể từ RAM của hệ điều hành chứ không nhất thiết từ SSD. Tổng 192.308: mỗi page của bảng được truy cập một lần.

Muốn đọc ít page hơn, PostgreSQL cần biết trước chiếc xe nằm ở page nào.

Làm sao PostgreSQL tìm được một dòng giữa hàng triệu dòng mà không phải đọc cả bảng?