Vui lòng đọc bài viết trước này, để hiểu sức mạnh của BRIN - Chỉ số phạm vi khối.
Database Theory: BRIN (Chỉ số phạm vi khối) là gì, nhanh hơn Chỉ số BTREE như thế nào
PostgreSQL 9.5 đã giới thiệu Chỉ số BRIN mạnh mẽ, có hiệu suất nhanh hơn nhiều so với Chỉ số BTREE thông thường.
Hai dòng quan trọng nhất của BRIN là: Nó chỉ lưu trữ giá trị tối thiểu và tối đa cho mỗi khối nên không yêu cầu thêm dung lượng. Đối với bảng cực lớn Nó chạy nhanh hơn bất kỳ Chỉ mục nào khác.
Trong bài đăng này, tôi sẽ hiển thị ví dụ về chỉ số BRIN với báo cáo hiệu suất đầy đủ (thử nghiệm trên 6gb Dữ liệu bảng).
Dưới đây là các bước:
Đầu tiên hãy tạo một bảng mẫu:
| 1 2 3 4 5 6 | CREATE TABLE tbl_ItemTransactions ( TranID SERIAL ,TransactionDate TIMESTAMPTZ ,TransactionName TEXT ); |
Chèn hàng triệu dữ liệu để kiểm tra hiệu suất của Chỉ số BRIN:
| 1 2 3 4 | INSERT INTO tbl_ItemTransactions (TransactionDate, TransactionName) SELECT x, 'dbrnd' FROM generate_series('2008-01-01 00:00:00'::timestamptz, '2016-08-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x); |
Kiểm tra tổng kích thước của bảng:
| 1 2 3 4 5 6 7 | SELECT pg_size_pretty(pg_total_relation_size('tbl_ItemTransactions')) AS TableSize; /* TableSize ------------------ 6741 MB */ |
Bây giờ Kiểm tra hiệu suất mà không có bất kỳ Chỉ mục nào:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | EXPLAIN ANALYSE SELECT COUNT(1) FROM tbl_ItemTransactions WHERE TransactionDate BETWEEN '2012-01-01 00:00:00' and '2014-08-08 08:08:08'; /* --Result: QueryPlan ------------------------------------------------------------------------- Aggregate (cost=2997896.81..2997896.82 rows=1 width=0) (actual time=40651.793..40651.793 rows=1 loops=1) -> Seq Scan on tbl_itemtransactions (cost=0.00..2894105.00 rows=41516724 width=0) (actual time=0.009..38726.686 rows=41054645 loops=1) Filter: ((transactiondate >= '2012-01-01 00:00:00+05:30'::timestamp with time zone) AND (transactiondate <= '2014-08-08 08:08:08+05:30'::timestamp with time zone)) Rows Removed by Filter: 94377356 Planning time: 0.860 ms Execution time: 80651.837 ms */ |
Tạo chỉ mục BRIN trên cột Ngày giao dịch:
| 1 2 3 | CREATE INDEX idx_tbl_ItemTransactions_TransactionDate ON tbl_ItemTransactions USING BRIN (TransactionDate); |
Bây giờ Kiểm tra hiệu suất của cùng một truy vấn có chỉ mục BRIN:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | EXPLAIN ANALYSE SELECT COUNT(1) FROM tbl_ItemTransactions WHERE TransactionDate BETWEEN '2012-01-01 00:00:00' and '2014-08-08 08:08:08'; /* QueryPlan --------------------------------------------------------------------------- Aggregate (cost=2014834.09..2014834.10 rows=1 width=0) (actual time=7108.998..7108.998 rows=1 loops=1) -> Bitmap Heap Scan on tbl_itemtransactions (cost=425666.42..1911042.28 rows=41516724 width=0) (actual time=16.995..5415.086 rows=41054645 loops=1) Recheck Cond: ((transactiondate >= '2012-01-01 00:00:00+05:30'::timestamp with time zone) AND (transactiondate <= '2014-08-08 08:08:08+05:30'::timestamp with time zone)) Rows Removed by Index Recheck: 21579 Heap Blocks: lossy=261632 -> Bitmap Index Scan on idx_tbl_itemtransactions_transactiondate (cost=0.00..415287.24 rows=41516724 width=0) (actual time=15.547..15.547 rows=2616320 loops=1) Index Cond: ((transactiondate >= '2012-01-01 00:00:00+05:30'::timestamp with time zone) AND (transactiondate <= '2014-08-08 08:08:08+05:30'::timestamp with time zone)) Planning time: 0.059 ms Execution time: 7109.060 ms */ |
Bây giờ, bạn có thể thấy sự khác biệt giữa kết quả của hai truy vấn trên.
Với chỉ mục BRIN, truy vấn tương tự chỉ mất 7 giây và không có BRIN thì mất khoảng 80 giây.
Tạo chỉ mục BRIN một phần trên cột Ngày giao dịch:
Bạn cũng có thể tạo chỉ mục BRIN một phần cho phạm vi dữ liệu riêng lẻ của mình. Chỉ số BRIN một phần cũng nhanh hơn chỉ số BRIN bình thường, nhưng chúng ta nên áp dụng bộ lọc thích hợp dựa trên chỉ số BRIN từng phần đã tạo.
| 1 2 3 4 | CREATE INDEX idx_tbl_ItemTransactions_TransactionDate_2012 ON tbl_ItemTransactions USING BRIN (TransactionDate) WHERE TransactionDate BETWEEN '2012-01-01' AND '2012-12-31'; |
| 1 2 3 4 | CREATE INDEX idx_tbl_ItemTransactions_TransactionDate_2013 ON tbl_ItemTransactions USING BRIN (TransactionDate) WHERE TransactionDate BETWEEN '2013-01-01' AND '2013-12-31'; |