本篇內容介紹了“怎么使用PostgreSQL中Hash索引”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
邏輯結構
可以把Hash Index理解為一個Hash Table,每個Hash bucket存儲根據Hash Function計算得到的對應的索引條目,為了節省空間,Hash索引條目只存儲Hash Code(即Hash Value) + TID而不存儲Hash Key(即索引鍵值),掃描索引后還必須讀取相應的數據表行,因此Index Only Scan不適用于Hash Index.
testdb=# drop table if exists t_idx1; DROP TABLE testdb=# create table t_idx1(id int,c1 varchar(20)); CREATE TABLE testdb=# create index idx_t_idx1_id on t_idx1 using hash(id); CREATE INDEX testdb=# insert into t_idx1 select generate_series(1,100000); INSERT 0 100000 testdb=# analyze t_idx1; ANALYZE testdb=# explain verbose select * from t_idx1 where id = 1; QUERY PLAN ------------------------------------------------------------------------------------ Index Scan using idx_t_idx1_id on public.t_idx1 (cost=0.00..8.02 rows=1 width=62) Output: id, c1 Index Cond: (t_idx1.id = 1) (3 rows) testdb=# -- 不能實現Index Only Scan testdb=# explain verbose select id from t_idx1 where id = 100; QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using idx_t_idx1_id on public.t_idx1 (cost=0.00..8.02 rows=1 width=4) Output: id Index Cond: (t_idx1.id = 100) (3 rows)
而普通的B-Tree索引是可以Index Only Scan的:
testdb=# create table t_idx2(id int,c1 varchar(20)); CREATE TABLE testdb=# insert into t_idx2 select generate_series(1,100000); INSERT 0 100000 testdb=# create index idx_t_idx2_id on t_idx2 using btree(id); CREATE INDEX testdb=# analyze t_idx2; ANALYZE testdb=# explain verbose select id from t_idx2 where id = 100; QUERY PLAN ---------------------------------------------------------------------------------------- Index Only Scan using idx_t_idx2_id on public.t_idx2 (cost=0.29..8.31 rows=1 width=4) Output: id Index Cond: (t_idx2.id = 100) (3 rows)
有四種頁面,分別是Meta page,Bucket Page,Overflow page和Bitmap page.
| 頁面類型 | 說明 |
|---|---|
| Meta page | page number zero, which contains information on what is inside the index. |
| Bucket pages | main pages of the index, which store data as ?hash code — TID? pairs. |
| Overflow pages | structured the same way as bucket pages and used when one page is insufficient for a bucket |
| Bitmap pages | which keep track of overflow pages that are currently clear and can be reused for other buckets |
使用pageinspect插件可查看index中的相關信息
testdb=# select hash_page_type(get_raw_page('idx_t_idx1_id',0));
hash_page_type
----------------
metapage
(1 row)
testdb=# select hash_page_type(get_raw_page('idx_t_idx1_id',1));
hash_page_type
----------------
bucket
(1 row)
testdb=# \x
Expanded display is on.
testdb=# select * from hash_page_stats(get_raw_page('idx_t_idx1_id',1));
-[ RECORD 1 ]---+-----------
live_items | 189
dead_items | 0
page_size | 8192
free_size | 4368
hasho_prevblkno | 256
hasho_nextblkno | 4294967295
hasho_bucket | 0
hasho_flag | 2
hasho_page_id | 65408
testdb=# select * from hash_page_stats(get_raw_page('idx_t_idx1_id',2));
-[ RECORD 1 ]---+-----------
live_items | 201
dead_items | 0
page_size | 8192
free_size | 4128
hasho_prevblkno | 257
hasho_nextblkno | 4294967295
hasho_bucket | 1
hasho_flag | 2
hasho_page_id | 65408“怎么使用PostgreSQL中Hash索引”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
