postgresql 索引之 hash

最新推荐文章于 2024-09-23 11:00:00 发布

数据库人生

最新推荐文章于 2024-09-23 11:00:00 发布

阅读量3.7k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/ctypyb2002/article/details/83273742

postgresql opti table index 专栏收录该内容

83 篇文章

订阅专栏

本文详细介绍了在PostgreSQL中使用哈希索引的方法，包括创建哈希索引的语法、适用场景以及官方警告的注意事项。通过实例展示了哈希索引在处理简单等值比较查询时的效率，并指出了其在持久性和复制方面的限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

os: ubuntu 16.04
postgresql: 9.6.8

ip 规划
192.168.56.102 node2 postgresql

help create index

postgres=# \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

[ USING method ]
method
要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。默认方法是btree。

hash

hash 只能处理简单的等值比较，

postgres=# drop table tmp_t0;
DROP TABLE
postgres=# create table tmp_t0(c0 varchar(100),c1 varchar(100));
CREATE TABLE

postgres=# insert into tmp_t0(c0,c1) select md5(id::varchar),md5((id+id)::varchar) from generate_series(1,100000) as id;
INSERT 0 100000

postgres=# create index idx_tmp_t0_1 on tmp_t0 using hash(c0);
CREATE INDEX

postgres=# \d+ tmp_t0
                                          Table "public.tmp_t0"
 Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
 c0     | character varying(100) |           |          |         | extended |              | 
 c1     | character varying(100) |           |          |         | extended |              | 
Indexes:
    "idx_tmp_t0_1" hash (c0)

postgres=# explain select * from tmp_t0 where c0 = 'd3d9446802a44259755d38e6d163e820';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Scan using idx_tmp_t0_1 on tmp_t0  (cost=0.00..8.02 rows=1 width=66)
   Index Cond: ((c0)::text = 'd3d9446802a44259755d38e6d163e820'::text)
(2 rows)

注意事项，官网特别强调：

Hash索引操作目前不被WAL记录，因此存在未写入修改，在数据库崩溃后需要用REINDEX命令重建Hash索引。
同样，在完成初始的基础备份后，对于Hash索引的改变也不会通过流式或基于文件的复制所复制，所以它们会对其后使用它们的查询给出错误的答案。
正因为这些原因，Hash索引已不再被建议使用。

参考：
http://postgres.cn/docs/9.6/indexes-types.html
http://postgres.cn/docs/9.6/catalog-pg-am.html

https://postgrespro.com/blog
Indexes in PostgreSQL — 1 https://postgrespro.com/blog/pgsql/3994098
Indexes in PostgreSQL — 2 https://postgrespro.com/blog/pgsql/4161264
Indexes in PostgreSQL — 3 (Hash) https://postgrespro.com/blog/pgsql/4161321
Indexes in PostgreSQL — 4 (Btree) https://postgrespro.com/blog/pgsql/4161516
Indexes in PostgreSQL — 5 (GiST) https://postgrespro.com/blog/pgsql/4175817
Indexes in PostgreSQL — 6 (SP-GiST) https://postgrespro.com/blog/pgsql/4220639
Indexes in PostgreSQL — 7 (GIN) https://postgrespro.com/blog/pgsql/4261647
Indexes in PostgreSQL — 8 (RUM) https://postgrespro.com/blog/pgsql/4262305