Chỉ mục không được dùng

Tám phần vừa rồi bàn về việc dựng chỉ mục. Phần này bàn về việc bỏ chúng đi — công việc ít được nhắc tới hơn nhiều, dù nó thường mang lại cải thiện lớn hơn.

Bảng đo

Bảng đơn hàng 3 triệu dòng với chín chỉ mục — một khoá chính, một ràng buộc duy nhất, bảy cái do lập trình viên thêm vào qua thời gian:

create table dh(
  id bigserial primary key,
  kh int, trang_thai text, ma text unique,
  tien numeric(12,2), luc timestamptz, ghi_chu text
);
-- 3.000.000 dòng
create index ix_kh on dh(kh);
create index ix_tt on dh(trang_thai);
create index ix_luc on dh(luc);
create index ix_kh_luc on dh(kh, luc);
create index ix_tien on dh(tien);
create index ix_tt_luc on dh(trang_thai, luc);
create index ix_ghi on dh(ghi_chu);

Chỉ mục nặng gấp đôi dữ liệu. Đây không phải trường hợp cực đoan mà là kết quả bình thường của việc mỗi lần gặp truy vấn chậm lại thêm một chỉ mục và không bao giờ quay lại dọn.

Cho chạy 1.200 truy vấn mô phỏng tải thật rồi đọc bộ đếm:

select indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
from pg_stat_user_indexes where relname = 'dh' order by idx_scan desc;

Tám trên chín chỉ mục có idx_scan = 0, tổng cộng 642 MB nằm không.

Chi phí thật của một chỉ mục không ai dùng

Chỉ mục thừa không chỉ tốn đĩa. Chèn 500.000 dòng vào bảng rỗng:

Sáu chỉ mục làm việc chèn chậm gấp 4,6 lần và sinh gấp 3,5 lần WAL. Nhiều WAL hơn nghĩa là sao lưu lâu hơn, máy dự phòng tụt lại xa hơn, và pg_wal đầy nhanh hơn.

Phép đo tiếp theo bất ngờ hơn. Cập nhật 300.000 dòng của cột dem — cột này không nằm trong bất kỳ chỉ mục nào, bảng đặt fillfactor=70 để cho phép cập nhật HOT:

Chậm gấp 5 lần. Cột được sửa không xuất hiện trong chỉ mục nào, và số cập nhật HOT giống hệt nhau tới từng đơn vị — nghĩa là bố cục trang y hệt, không phải chuyện HOT bị mất.

Lời giải nằm ở 56% còn lại. Trong 300.000 dòng, 132.699 dòng đi được đường HOT và không đụng tới chỉ mục; 167.301 dòng còn lại không đủ chỗ trong trang nên phải ghi phiên bản mới ở trang khác, và mỗi dòng như vậy buộc PostgreSQL thêm một mục vào cả sáu chỉ mục — kể cả sáu chỉ mục không liên quan gì tới cột vừa sửa. Đó là 1 triệu mục chỉ mục cho một câu UPDATE chạm vào một cột.

Đây là lý do "thêm chỉ mục cho chắc" là một quyết định đắt: cái giá không nằm ở truy vấn bạn đang tối ưu, mà nằm ở mọi câu ghi về sau, kể cả những câu chẳng liên quan.

Bẫy 1: idx_scan = 0 có thể là bị che

ix_kh có idx_scan = 0 sau 1.200 truy vấn, trong khi tải thử rõ ràng có lọc theo kh. Xem kế hoạch:

explain select count(*) from dh where kh = 12345;

Bitmap Heap Scan on dh  (cost=4.67..126.58 rows=31 width=0)
  ->  Bitmap Index Scan on ix_kh_luc  (cost=0.00..4.66 rows=31 width=0)

Bộ lập lịch dùng ix_kh_luc — chỉ mục 90 MB — thay vì ix_kh chỉ 22 MB. Chi phí ước lượng của hai đường bằng nhau chính xác tới hai chữ số thập phân (126,66), nên đây là một thế hoà, và PostgreSQL phân giải bằng thứ tự OID: ix_kh có OID 16395, ix_kh_luc có 16398, và nó chọn cái sau.

Hậu quả nghiêm trọng hơn vẻ ngoài. Nếu bạn tin idx_scan = 0 và xoá ix_kh, bạn giữ lại chỉ mục 90 MB và vứt đi chỉ mục 22 MB làm được đúng việc đó. Ngược hoàn toàn với ý định.

Chỉ mục thừa thật sự phải tìm bằng cấu trúc chứ không bằng bộ đếm — một chỉ mục là thừa khi cột của nó là tiền tố của một chỉ mục khác:

select a.indexrelid::regclass as thua,
       b.indexrelid::regclass as bi_phu_boi,
       pg_size_pretty(pg_relation_size(a.indexrelid)) as giai_phong
from pg_index a
join pg_index b
  on a.indrelid = b.indrelid
 and a.indexrelid <> b.indexrelid
 and a.indnkeyatts < b.indnkeyatts
 and (string_to_array(a.indkey::text, ' '))[1:a.indnkeyatts]
   = (string_to_array(b.indkey::text, ' '))[1:a.indnkeyatts]
where not a.indisunique and not a.indisprimary
  and a.indpred is null and b.indpred is null;

Trên bảng này nó trả về:

 ix_kh  là tiền tố của  ix_kh_luc   giải phóng 22 MB
 ix_tt  là tiền tố của  ix_tt_luc   giải phóng 20 MB

Lưu ý indkey là kiểu int2vector, hiển thị ra chuỗi các số cách nhau bằng dấu cách. Lần đầu tôi viết a.indkey::int2[] = (b.indkey::int2[])[0:...] và truy vấn trả về rỗng — không lỗi, chỉ rỗng, trông y như "không có chỉ mục thừa nào". Phải đổi sang string_to_array và đánh chỉ số từ 1 mới ra kết quả.

Nhưng ngay cả kết quả này cũng chỉ là gợi ý. ix_kh là tiền tố của ix_kh_luc nên về lý thuyết bỏ được — nhưng bỏ cái nào mới đúng thì còn tuỳ: nếu không truy vấn nào cần cả (kh, luc) thì nên bỏ ix_kh_luc để giải phóng 90 MB chứ không phải bỏ ix_kh để giải phóng 22 MB.

Bẫy 2: phép đo của chính bạn không chạm vào chỉ mục

Tải thử của tôi viết thế này, và nó là một cái bẫy:

do $$ declare i int; n bigint; begin
  for i in 1..300 loop
    select count(*) into n from dh where kh = (random()*99999)::int;
  end loop;
end $$;

Sau 300 vòng, idx_scan vẫn bằng 0. Không phải vì chỉ mục vô dụng, mà vì:

explain select count(*) from dh where kh = (random()*99999)::int;

Seq Scan on dh  (cost=0.00..103595.82 rows=31 width=0)

random() là hàm VOLATILE. PostgreSQL phải giả định nó cho giá trị khác nhau ở mỗi lần gọi, nên không được phép tính một lần rồi dùng làm khoá tra cứu — nó phải tính lại cho từng dòng, và điều đó buộc phải quét toàn bảng.

Gán giá trị vào biến trước khi so sánh thì mọi thứ đổi:

do $$ declare i int; n bigint; v int; begin
  for i in 1..300 loop
    v := (random()*99999)::int;          -- tính một lần
    select count(*) into n from dh where kh = v;
  end loop;
end $$;

Chênh nhau 170 lần, chỉ vì một dòng gán.

Điều đáng lo là kiểu bẫy này không giới hạn trong việc đo. Bất kỳ hàm VOLATILE nào nằm trong mệnh đề where đều có hiệu ứng tương tự — và người ta hay viết mà không nghĩ tới. Cách kiểm tra một hàm:

select proname, provolatile from pg_proc where proname in ('random','now','clock_timestamp','lower');

v là volatile, s là stable, i là immutable. Phần 14 đã gặp đúng phân loại này ở chiều ngược lại: to_char không phải IMMUTABLE nên không dựng chỉ mục biểu thức được.

Với tôi thì đây là bài học về cách đo: khi một phép đo cho ra con số 0 tròn trĩnh, khả năng cao là phép đo sai chứ không phải hiện tượng không tồn tại.

Còn một sai lầm nữa cùng buổi: tôi chạy các khối do $$ ... $$ bằng docker exec pg19 psql <<'SQL' — thiếu cờ -i nên docker exec không nối stdin, và không câu lệnh nào chạy cả. Không lỗi, không cảnh báo, chỉ là đầu ra rỗng và bộ đếm bằng 0. Tôi đổ lỗi cho PostgreSQL mất một lúc trước khi nhận ra không có gì tới được PostgreSQL.

Bẫy 3: bộ đếm nói về máy này, kể từ lần đặt lại gần nhất

Ba điều cần biết trước khi tin idx_scan:

Nó bị pg_stat_reset() xoá sạch và cũng mất khi bạn dựng lại máy chủ từ bản sao lưu. Một chỉ mục có idx_scan = 0 có thể chỉ đơn giản là bộ đếm mới được đặt lại tuần trước. Kiểm bằng pg_stat_get_db_stat_reset_time(oid).

Máy dự phòng đếm riêng. Nếu bạn tách truy vấn đọc sang replica, chỉ mục có thể idx_scan = 0 trên máy chính mà lại chạy liên tục trên replica. Xoá nó là làm hỏng đúng những báo cáo nặng nhất. Phải kiểm trên mọi máy trước khi kết luận.

Nó đếm số lần quét, không phải số dòng. Một chỉ mục có idx_scan = 5 nhưng mỗi lần trả về 2 triệu dòng thì nó vẫn đang được dùng nghiêm túc; ngược lại idx_scan = 50.000 với mỗi lần một dòng cũng chỉ là tra cứu lặt vặt. Cột idx_tup_read cho biết chiều thứ hai.

Cách xoá an toàn

PostgreSQL cho DDL nằm trong giao dịch (phần 1), nên bạn có thể thử xoá thật rồi huỷ:

begin;
drop index ix_kh_luc;
explain select count(*) from dh where kh = 12345;
explain select * from dh where kh = 999 order by luc desc limit 20;
rollback;

Sau rollback, chỉ mục quay lại nguyên vẹn — kiểm bằng select count(*) from pg_indexes where indexname = 'ix_kh_luc' cho ra 1. Trong khoảng giữa, EXPLAIN báo đúng những gì sẽ xảy ra nếu bạn xoá thật.

Đây là điểm hơn hẳn so với đọc bộ đếm: bạn thấy kế hoạch mới, chứ không đoán. Chạy EXPLAIN cho từng truy vấn quan trọng trong giao dịch đó, cái nào rơi về Seq Scan thì chỉ mục kia còn cần thiết.

Một hạn chế thật: DROP INDEX bên trong giao dịch lấy khoá ACCESS EXCLUSIVE trên bảng và giữ tới lúc rollback. Trên máy chủ đang chạy thật, mọi truy vấn chạm vào bảng đó sẽ đợi. Giao dịch thử phải thật ngắn, hoặc làm trên bản sao.

Khi đã chắc, xoá thật bằng:

drop index concurrently ix_kh_luc;

Bản concurrently không chặn đọc ghi, đổi lại không chạy được trong giao dịch. Trên bảng thử này cả hai đều mất chưa tới 0,1 giây — nhưng con số đó không nói lên gì về máy chủ đang tải, nơi DROP INDEX thường phải xếp hàng chờ khoá lâu hơn nhiều so với thời gian nó thực sự làm việc.

Thứ tự làm việc

1. Chạy truy vấn tìm chỉ mục thừa theo cấu trúc ở trên — kết quả này chắc chắn, không phụ thuộc bộ đếm.
2. Với các ứng viên còn lại, kiểm idx_scan trên cả máy chính lẫn mọi replica, và kiểm thời điểm đặt lại thống kê gần nhất.
3. Thử begin; drop index …; explain …; rollback; cho từng truy vấn quan trọng.
4. drop index concurrently.
5. Giữ lại câu CREATE INDEX trong ghi chú để dựng lại nếu cần.

Bước 5 rẻ và hay bị quên. Dựng lại một chỉ mục mất vài phút; tìm lại định nghĩa chính xác của nó sau ba tháng thì lâu hơn nhiều.

Thử ba mươi giây

select s.relname as bang,
       s.indexrelname as chi_muc,
       s.idx_scan as so_lan_dung,
       pg_size_pretty(pg_relation_size(s.indexrelid)) as nang
from pg_stat_user_indexes s
join pg_index i on i.indexrelid = s.indexrelid
where s.idx_scan = 0
  and not i.indisunique and not i.indisprimary
order by pg_relation_size(s.indexrelid) desc
limit 10;

Đây là danh sách ứng viên, không phải danh sách để xoá. Ba bẫy ở trên nói vì sao. Nhưng nếu dòng đầu tiên nặng vài trăm megabyte thì đáng để dành một buổi chiều đi qua năm bước kia.

Phần sau đo thống kê và ANALYZE: chuyện gì xảy ra khi bộ lập lịch làm việc với số liệu cũ.