postgresql 索引之 btree

最新推荐文章于 2025-06-27 14:48:46 发布

数据库人生

最新推荐文章于 2025-06-27 14:48:46 发布

阅读量2.5k

点赞数

CC 4.0 BY-SA版权

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

postgresql opti table index 专栏收录该内容

83 篇文章

订阅专栏

本文深入探讨了在PostgreSQL数据库中创建和使用不同类型的索引方法，包括btree、hash、gist等，通过实例展示了如何创建索引以及索引对查询性能的影响。

摘要生成于 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。

btree

不加任何指定时，默认使用的就是 bree 类型的索引。
适用大部分的场景。

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(c0);
CREATE INDEX

postgres=# \d+ tmp_t0
                                Table "public.tmp_t0"
 Column |          Type          | Modifiers | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+--------------+-------------
 c0     | character varying(100) |           | extended |              | 
 c1     | character varying(100) |           | extended |              | 
Indexes:
    "idx_tmp_t0_1" btree (c0)
	
postgres=# drop index idx_tmp_t0_1;
DROP INDEX
postgres=# create index idx_tmp_t0_1 on tmp_t0 using btree (c0);
CREATE INDEX

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

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

参考：
http://postgres.cn/docs/9.6/indexes-types.html
http://postgres.cn/docs/9.6/sql-createindex.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