How to use Hash Index in PostgreSQL

This article introduces the knowledge of "how to use the Hash index in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Logical structure

Hash Index can be understood as a Hash Table, and each Hash bucket stores the corresponding index entries calculated according to Hash Function. In order to save space, Hash index entries only store Hash Code (that is, Hash Value) + TID, but not Hash Key (that is, index key values). After scanning the index, it is necessary to read the corresponding data table rows, so Index Only Scan is not suitable for Hash Index.

Testdb=# drop table if exists tweeidx1 / drop TABLEtestdb=# create table t_idx1 (id int,c1 varchar (20)); CREATE TABLEtestdb=# create index idx_t_idx1_id on t_idx1 using hash (id); CREATE INDEXtestdb=# insert into t_idx1 select generate_series (1minute 100000); INSERT 0 100000testdb=# analyze tabilidx1 / 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=#-cannot achieve Index Only Scantestdb=# 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)

A normal B-Tree index can be Index Only Scan:

Testdb=# create table t_idx2 (id int,c1 varchar (20)); CREATE TABLEtestdb=# insert into t_idx2 select generate_series (1m 100000); INSERT 0 100000testdb=# create index idx_t_idx2_id on t_idx2 using btree (id); CREATE INDEXtestdb=# analyze tweeidx2 / ANALYZEtestDB # 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)

There are four kinds of pages, Meta page,Bucket Page,Overflow page and Bitmap page.

Page type description Meta pagepage number zero, which contains information on what is inside the index.Bucket pagesmain pages of the index, which store data as «hash code-TID »pairs.Overflow pagesstructured the same way as bucket pages and used when one page is insufficient for a bucketBitmap pageswhich keep track of overflow pages that are currently clear and can be reused for other buckets

Use the pageinspect plug-in to view relevant information in 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=#\ xExpanded display is on.testdb=# select * from hash_page_stats (get_raw_page ('idx_t_idx1_id',1)) -[RECORD 1]-- +-live_items | 189dead_items | 0page_size | 8192free_size | 4368hasho_prevblkno | 256hasho_nextblkno | 4294967295hasho_bucket | 0hasho_flag | 2hasho_page_id | 65408testdb=# select * from hash_page_stats (get_raw_page ('idx_t_idx1_id',2)) -[RECORD 1]-- +-live_items | 201dead_items | 0page_size | 8192free_size | 4128hasho_prevblkno | 257hasho_nextblkno | 4294967295hasho_bucket | 1hasho_flag | 2hasho_page_id | 65408 "how to use the Hash Index in PostgreSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!