devbox
Gausst松鼠会
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

进阶数据库系列（十二）：PostgreSQL 索引技术详解

作者：Gausst松鼠会 | 2024-05-22 03:51:21

赞

踩

postgresql 索引

点击下方名片，设为星标！

回复“1024”获取2TB学习资源！

前面介绍了 PostgreSQL 数据类型和运算符、常用函数、锁操作、执行计划、视图与触发器、存储过程相关的知识点，今天将为大家介绍 PostgreSQL 索引 相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

概述

索引主要被用来提升数据库性能，不当的使用会导致性能变差。 PostgreSQL 提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了一种不同的算法来适应不同类型的查询。默认情况下，CREATE INDEX 命令创建适合于大部分情况的 B-tree 索引。

  • B-树（默认）：B-树是一个自平衡树（self-balancing tree），按照顺序存储数据，支持对数时间复杂度（O(logN)）的搜索、插入、删除和顺序访问。

  • 哈希：哈希索引（Hash index）只能用于简单的等值查找（=），也就是说索引字段被用于等号条件判断。因为对数据进行哈希运算之后不再保留原来的大小关系。

  • GiST：GiST 代表通用搜索树（Generalized Search Tree），GiST 索引单个索引类型，而是一种支持不同索引策略的框架。GiST 索引常见的用途包括几何数据的索引和全文搜索。

  • SP-GiST：SP-GiST 代表空间分区 GiST，主要用于 GIS、多媒体、电话路由以及 IP 路由等数据的索引。与 GiST 类似， SP-GiST 也支持“最近邻”搜索。

  • GIN：GIN 代表广义倒排索引（generalized inverted indexes），主要用于单个字段中包含多个值的数据，例如 hstore、 array、 jsonb 以及 range 数据类型。一个倒排索引为每个元素值都创建一个单独的索引项，可以有效地查询某个特定元素值是否存在。Google、百度这种搜索引擎利用的就是倒排索引。

  • BRIN：BRIN 代表块区间索引（block range indexes），存储了连续物理范围区间内的数据摘要信息。BRIN 也相比 B-树索引要小很多，维护也更容易。对于不进行水平分区就无法使用 B-树索引的超大型表，可以考虑 BRIN。

在使用上，除了常见的单列索引，还有多列索引、唯一索引、表达式索引、部分索引、覆盖索引等。更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

  • 多列索引：目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件 pg_config_manual.h 中修改，但是修改后需要重新编译PostgreSQL）。

  • 唯一索引：目前，只有 B-tree 能够被声明为唯一。

  • 表达式索引：从表的一列或多列计算而来的一个函数或者标量表达式。索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。

  • 部分索引：一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式（被称为部分索引的谓词）定义。而索引中只包含那些符合该谓词的表行的项。使用部分索引的一个主要原因是避免索引公值（查询结果行在一个表中占比超过一定百分比的值不会使用索引）。

  • 覆盖索引：目前，B-树索引总是支持只用索引的扫描。GiST 和 SP-GiST 索引只对某些操作符类支持只用索引的扫描。其他索引类型不支持这种扫描。仅访问索引就可获取查询所需的全部数据，无需回表（Index-Only Scan）。

语法

  1. CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
  2.     ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
  3.     [ INCLUDE ( column_name [, ...] ) ]
  4.     [ WITH ( storage_parameter [= value] [, ... ] ) ]
  5.     [ TABLESPACE tablespace_name ]
  6.     [ WHERE predicate ]

UNIQUE：唯一索引，在索引被创建时（如果数据已经存在）或者加入数据时检查重复值。

CONCURRENTLY：在构建索引时不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引构建将会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索引创建完毕。

IF NOT EXISTS：如果一个同名关系已经存在则不要抛出错误。

INCLUDE：指定一个列的列表，其中的列将被包括在索引中作为非键列。不能作为索引扫描的条件，主要作用是相关数据索存储在索引中，访问时无需访问该索引的基表。当前，有B-树和GiST索引访问方法支持这一特性。

name：要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在的模式中。如果索引名称被省略，PostgreSQL 将基于基表名称和被索引列名称选择一个合适的名称。

ONLY：如果该表是分区表，指示不要在分区上递归创建索引。默认会递归创建索引。

table_name：要被索引的表的名称（可以被模式限定）。

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

column_name：一个表列的名称。

expression：一个基于一个或者更多个表列的表达式。如语法中所示，表达式通常必须被写在圆括号中。不过，如果该表达式是一个函数调用的形式，圆括号可以被省略。

collation：要用于该索引的排序规则的名称。

opclass：一个操作符类的名称。

opclass_parameter：运算符类参数的名称。

ASC：指定上升排序（默认）。

DESC：指定下降排序。

NULLS FIRST：指定把空值排序在非空值前面。在指定DESC时，这是默认行为。

NULLS LAST：指定把空值排序在非空值后面。在没有指定DESC时，这是默认行为。

storage_parameter：索引方法相关的存储参数的名称。可选的WITH子句为索引指定存储参数。每一种 索引方法都有自己的存储参数集合。

B-树、哈希、GiST以及SP-GiST索引方法都接受这个参数：

fillfactor (integer):索引的填充因子是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于B-树，在初始的索引构建过程中，叶子页面会被填充至该百分数，当在索引右端扩展索引（增加新的最大键值）时也会这样处理。如果页面后来被完全填满，它们就会被分裂，导致索引的效率逐渐退化。B-树使用了默认的填充因子 90，但是也可以选择为 10 到 100 的任何整数值。如果表是静态的，那么填充因子 100 是最好的，因为它可以让索引的物理尺寸最小化。但是对于更新负荷很重的表，较小的填充因子有利于最小化对页面分裂的需求。其他索引方法以不同但是大致类似的方式使用填充因子，不同方法的默认填充因子也不相同。

deduplicate_items (boolean)：B 树重复数据删除技术的使用。设置为 ON 或 OFF 以启用或禁用优化。默认值为ON。

vacuum_cleanup_index_scale_factor：指定在以前的统计信息收集过程中计数到的堆元组总数的一个分数，插入不超过这一数量所代表的元组不会导致VACUUM清理阶段的索引扫描。这个设置当前仅适用于B-树索引。

buffering (enum)：适用于 GiST 索引，决定是否用缓冲构建技术来构建索引。OFF 会禁用它，ON 则启用该特性，如果设置为 AUTO 则初始会禁用它，但是一旦索引尺寸到达 effective_cache_size 就会随时打开。默认值是 AUTO。

fastupdate (boolean)：适用于 GIN 索引，这个设置控制快速更新技术的使用。它是一个布尔参数：ON 启用快速更新，OFF 禁用。默认是 ON。

gin_pending_list_limit (integer)：适用于 GIN 索引，设置 fastupdate 被启用时可以使用的 GIN 索引的待处理列表的最大尺寸。 如果该列表增长到超过这个最大尺寸，会通过批量将其中的项移入索引的主 GIN 数据结构来清理列表。 如果指定值时没有单位，则以千字节为单位。默认值是四兆字节（4MB）。可以通过更改索引的存储参数来为个别 GIN 索引覆盖这个设置。

pages_per_range (integer)：使用于 BRIN 索引，定义用于每一个BRIN索引项的块范围由多少个表块组成。默认是128。

autosummarize (boolean)：定义是否只要在下一个页面上检测到插入就为前面的页面范围运行概要操作。

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

B-tree 索引

Btree 结构

meta page和root page是一定有的，meta page需要一个页来存储，表示指向root page的page id。随着记录数的增加，一个root page可能存不下所有的heap item，就会有leaf page,甚至branch page，甚至多层的branch page。一共有几层branch和 leaf，可以用btree page元数据的level来表示。

安装扩展准备数据
  1. --使用pageinspect扩展工具查看结构，数据准备
  2. create extension pageinspect;
  3. --主键索引使用的是btree索引，索引名字 tb_order_pkey
  4. create table tb_order(id int primary key, order_no varchar(255)); 
  5. insert into tb_order select generate_series(1,100), md5(random()::varchar);
  6. --analyze 统计数据库表数据，统计结果存储到pg_statistic系统表中
  7. --vacuum 用于清理死亡元组占用的存储空间
  8. vacuum analyze tb_order;
btree索引演变过程
btree索引一层结构

7a56966c8d36de71a3576bad8ec585a8.png1、查看meta块

select * from bt_metap('tb_order_pkey');--查看meta块

此时level为0即高度为1，root块为1。1af6c4cae40f56424a5a5663fef9db18.png2、根据root 的 page id =1查看 root page的stats

select * from bt_page_stats('tb_order_pkey', 1);--查看page的统计状态信息

f12023d52b8c883030abd14de7625ff1.png图中参数说明如下：

  1. ive_items:存活的索引行
  2. dead_items：死亡的索引行
  3. avg_item_size:平均索引行大小
  4. page_size:块大小，详细看最后说明
  5. free_size：块空余大小
  6. btpo_prev：块左边
  7. btpo_next：块右边
  8. btpo：当前块层次，0代表处于0层
  9. btpo_flags：当前块类型，3代表：他既是leaf又是root，即2+1
  10. meta page
  11. root page:表示为btpo flags=2
  12. branch page :表示为btpo flags=0
  13. leaf page:表示为btpo flags=1

3、查看指定索引块内容

select * from bt_page_items('tb_order_pkey', 1);--查看指定索引块内容

ed28d89b3702b4923e910362060eb7c4.png索引：ctid+索引列的值

4、通过索引ctid访问数据

select * from tb_order where ctid='(0,1)'; --通过索引ctid访问数据
81358853b6997c86dc9c6b4ef8911bad.png
btree索引二层结构

包括 meta page, root page, leaf paged9964eb9855faa03c7d20bcf54890f05.png

  1. --使用pageinspect扩展工具查看结构，数据准备
  2. create extension pageinspect;
  3. --主键索引使用的是btree索引，索引名字 tb_order_pkey
  4. create table tb_order2(id int primary key, order_no varchar(255)); 
  5. insert into tb_order2 select generate_series(1,10000), md5(random()::varchar);
  6. --analyze 统计数据库表数据，统计结果存储到pg_statistic系统表中
  7. --vacuum 用于清理死亡元组占用的存储空间
  8. vacuum analyze tb_order2;

ad9b97b79a4dd6c5f04b4be110a021a5.png1、查看meta块

Vacuum用于清理死亡元组占用的存储空间，默认删除或因更新过期（为了MVVC）的元组不会被物理删除。因此需要周期性的进行Vacuum，尤其是频繁更新的表。

Analyze命令用于统计数据库表数据，统计结果存储到pg_statistic系统表中。数据库进行基于成本的优化（CBO）时通过统计数据优化SQL语句的解释计划。

select * from bt_metap('tb_order2_pkey');

此时level为1即高度为2，root块id为352d3f061befdd8ceae7a23e8104840bc.png2、根据root 的 page id =3查看 root page的stats**

select * from bt_page_stats('tb_order2_pkey', 3);--根据root 的 page id =3查看stats
6f02f642badd19f827a79d17f16b0f54.png 
  1. live_items:存活的页块
  2. dead_items：死亡的页块
  3. avg_item_size:平均索引行大小
  4. page_size:块大小，详细看最后说明
  5. free_size：块空余大小
  6. btpo_prev：块左边
  7. btpo_next：块右边
  8. btpo：当前块层次，1代表处于2层，表示下面还有一层
  9. btpo_flags：当前块类型，3代表：他既是leaf又是root，即2+1
  10. - meta page
  11. - root page:表示为btpo flags=2
  12. - branch page :表示为btpo flags=0
  13. - leaf page:表示为btpo flags=1

3、查看指定索引块内容

select * from bt_page_items('tb_order2_pkey', 3);

总共28个页块，9e59564ba1ff2f5613a731fc8b9a0997.png4、查看每个的页块统计

  1. select * from bt_page_stats('tb_order2_pkey', 1);
  2.  
  3. btpo flags=1表示为leaf page
  4. btpo_prev：0
  5. btpo_next：2

8f2d6f56f0864948f048313734c1e2c6.png5、查看每个的页块内容

select * from bt_page_items('tb_order2_pkey', 1);

75751607445b3a2eb4716c1b69dfdde6.png6、通过ctid查看数据

select * from tb_order2 where ctid='(3,1)';
b4acefc2d964e564c3a596e60b414366.png
btree索引三层结构

包括 meta page, root page, leaf page，branch page。更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

实例
  1. postgres=# create index idx_test_id on test(id);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_id" btree (id)
  11.  
  12. postgres=# explain analyze select * from test where id between 100 and 200;
  13.                                 QUERY PLAN
  14. ------------------------------------------------------------------------------------------------------------------------
  15.  Index Scan using idx_test_id on test  (cost=0.43..10.49 rows=103 width=15) (actual time=0.006..0.058 rows=101 loops=1)
  16.    Index Cond: ((id >= 100) AND (id <= 200))
  17.  Planning Time: 0.408 ms
  18.  Execution Time: 0.072 ms
  19. (4 rows)

Hash 索引

Hash索引结构

哈希索引项只存储每个索引项的哈希代码，而不是实际的数据值

应用场景

  • hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。

  • hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个索引行，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。

实例
  1. postgres=# create index idx_test_id on test using hash(id);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_id" hash (id)
  11.  
  12. postgres=# explain analyze select * from test where id = 100;
  13.                                                     QUERY PLAN
  14. -------------------------------------------------------------------------------------------------------------------
  15.  Index Scan using idx_test_id on test  (cost=0.00..8.02 rows=1 width=15) (actual time=0.014..0.015 rows=1 loops=1)
  16.    Index Cond: (id = 100)
  17.  Planning Time: 0.142 ms
  18.  Execution Time: 0.029 ms
  19. (4 rows)

GiST 索引

GiST的意思是通用的搜索树(Generalized Search Tree)。 它是一种平衡树结构的访问方法,在系统中作为一个基本模版,可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现。

与Btree索引比较的优缺点
优点

Gist索引适用于多维数据类型和集合数据类型，和Btree索引类似，同样适用于其他的数据类型。和Btree索引相比，Gist多字段索引在查询条件中包含索引字段的任何子集都会使用索引扫描，而Btree索引只有查询条件包含第一个索引字段才会使用索引扫描。

缺点

Gist索引创建耗时较长，占用空间也比较大。

实例
  1. postgres=# create index idx_t_gist_pos on t_gist using gist(pos);
  2. CREATE INDEX
  3. postgres=# \d t_gist
  4.                Table "public.t_gist"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  pos    | point   |           |          |
  9. Indexes:
  10.     "idx_t_gist_pos" gist (pos)
  11.  
  12. postgres=# explain analyze select * from t_gist where circle '((100,100) 10)'  @> pos;
  13.                                                         QUERY PLAN
  14. --------------------------------------------------------------------------------------------------------------------------
  15.  Bitmap Heap Scan on t_gist  (cost=5.06..271.70 rows=100 width=20) (actual time=0.048..0.092 rows=29 loops=1)
  16.    Recheck Cond: ('<(100,100),10>'::circle @> pos)
  17.    Heap Blocks: exact=29
  18.    ->  Bitmap Index Scan on idx_t_gist_pos  (cost=0.00..5.03 rows=100 width=0) (actual time=0.027..0.027 rows=29 loops=1)
  19.          Index Cond: (pos <@ '<(100,100),10>'::circle)
  20.  Planning Time: 0.092 ms
  21.  Execution Time: 0.136 ms
  22. (7 rows)

SP-GiST 索引

SP-GiST 中的GiST说明它跟 GiST 访问方法有一些相似性。两者的相似性在于，都是通用搜索树，都为构建各种访问方法提供了框架。SP代表空间分区（space partitioning），这里的空间，就是我们常说的空间，比如二维平面。我们将会看到，它可以表示任意搜索空间。

GIST索引不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。因此，可以使用GIST索引的特定操作符类型高度依赖于索引策略(操作符类)。

GIST是广义搜索树generalized search tree的缩写。这是一个平衡搜索树。

用于解决一些B-tree，GIN难以解决的数据减少问题，例如，范围是否相交，是否包含，地理位置中的点面相交，或者按点搜索附近的点。

Postgresql也实现了以下几种类型的SP-Gist索引的操作类，我们可以在这些类型上直接建立SP-Gist索引。c3cefabe67ed3451c66116b1812d06d8.png

实例
  1. postgres=# create index idx_t_spgist_p on t_spgist using spgist(p);
  2. CREATE INDEX
  3. postgres=# \d t_spgist
  4.               Table "public.t_spgist"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  p      | point   |           |          |
  9. Indexes:
  10.     "idx_t_spgist_p" spgist (p)
  11.  
  12. postgres=# explain analyze select * from t_spgist where p >^ point '(2,7)';
  13.                                                            QUERY PLAN
  14. ---------------------------------------------------------------------------------------------------------------------------------
  15.  Bitmap Heap Scan on t_spgist  (cost=305.78..1067.78 rows=10000 width=20) (actual time=7.387..15.011 rows=99245 loops=1)
  16.    Recheck Cond: (p >^ '(2,7)'::point)
  17.    Heap Blocks: exact=637
  18.    ->  Bitmap Index Scan on idx_t_spgist_p  (cost=0.00..303.28 rows=10000 width=0) (actual time=7.302..7.303 rows=99245 loops=1)
  19.          Index Cond: (p >^ '(2,7)'::point)
  20.  Planning Time: 0.124 ms
  21.  Execution Time: 17.611 ms
  22. (7 rows)

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

GIN 索引

gin索引结构

GIN是Generalized lnverted Index的缩写。就是所谓的倒排索引，它处理的数据类型的值不是原来的，而是由元素构成。我们称之为复合类型。

存储被索引字段的VALUE或VALUE的元素，以及行号的list或tree。

col_val:(tid_list or tid_tree),col_val_elements:(tid_list or tid_tree)

比如('hank','15:3 21:4')中，表示hank在15:3和21:4这两个位置出现过

应用场景

  • 当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索)

  • 当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持普通btree支持的类型。(支持btree的操作符)

  • 当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持内部多域索引的bitmapAnd, bitmapor合并，快速的返回按任意列搜索请求的数据。

实例
  1. postgres=# create index idx_ts_doc_tsv on ts using gin(doc_tsv);
  2. CREATE INDEX
  3. postgres=# \d ts
  4.                   Table "public.ts"
  5.  Column  |   Type   | Collation | Nullable | Default
  6. ---------+----------+-----------+----------+---------
  7.  doc     | text     |           |          |
  8.  doc_tsv | tsvector |           |          |
  9. Indexes:
  10.     "idx_ts_doc_tsv" gin (doc_tsv)
  11. postgres=# set enable_seqscan = off;
  12. SET
  13. postgres=# explain analyze select * from ts where doc_tsv @@ to_tsquery('many & slitter');
  14.                                                        QUERY PLAN
  15. ------------------------------------------------------------------------------------------------------------------------
  16.  Bitmap Heap Scan on ts  (cost=12.25..16.51 rows=1 width=64) (actual time=0.046..0.047 rows=1 loops=1)
  17.    Recheck Cond: (doc_tsv @@ to_tsquery('many & slitter'::text))
  18.    Heap Blocks: exact=1
  19.    ->  Bitmap Index Scan on idx_ts_doc_tsv  (cost=0.00..12.25 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
  20.          Index Cond: (doc_tsv @@ to_tsquery('many & slitter'::text))
  21.  Planning Time: 0.176 ms
  22.  Execution Time: 0.071 ms
  23. (7 rows)

BRIN 索引

简介

BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。

BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很好。与我们已经熟悉的索引不同，BRIN避免查找绝对不合适的行，而不是快速找到匹配的行。BRIN是一个不准确的索引:不包含表行的tid。33cf58b11168de0c3b201f1dd78739d6.png表被分割成ranges(好多个pages的大小)：因此被称作block range index(BRIN)。在每个range中存储数据的摘要信息。作为规则，这里是最小值和最大值，但有时也并非如此。假设执行了一个查询，该查询包含某列的条件;如果所查找的值没有进入区间，则可以跳过整个range;但如果它们确实在，所有块中的所有行都必须被查看以从中选择匹配的行。在元数据页和摘要数据之间，是reverse range map页(revmap)。是一个指向相应索引行的指针(TIDs)数组。

在BRIN索引中，PostgreSQL会为每个8k大小的存储数据页面读取所选列的最大值和最小值，然后将该信息（页码以及列的最小值和最大值）存储到BRIN索引中。一般可以不把BRIN看作索引，而是看作顺序扫描的加速器。 如果我们把每个range都看作是一个虚拟分区，那么我们可以把BRIN看作分区的替代方案。BRIN适合单值类型，当被索引列存储相关性越接近1或-1时，数据存储越有序，块的边界越明显，BRIN索引的效果就越好。

实例
  1. postgres=# create index idx_test_id on test using brin(id);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_id" brin (id)
  11.  
  12. postgres=# explain analyze select * from test where id between 100 and 200;
  13.                                                          QUERY PLAN
  14. ----------------------------------------------------------------------------------------------------------------------------
  15.  Bitmap Heap Scan on test  (cost=12.03..27651.36 rows=1 width=15) (actual time=0.079..4.006 rows=101 loops=1)
  16.    Recheck Cond: ((id >= 100) AND (id <= 200))
  17.    Rows Removed by Index Recheck: 23579
  18.    Heap Blocks: lossy=128
  19.    ->  Bitmap Index Scan on idx_test_id  (cost=0.00..12.03 rows=23585 width=0) (actual time=0.067..0.067 rows=1280 loops=1)
  20.          Index Cond: ((id >= 100) AND (id <= 200))
  21.  Planning Time: 0.240 ms
  22.  Execution Time: 4.045 ms
  23. (8 rows)

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

其他用法

多列索引
  1. postgres=# create index idx_test_dl on test(id,name);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_dl" btree (id, name)
  11.  
  12. postgres=# explain analyze select * from test where id = 100 and name = 'val:100';
  13.                                                        QUERY PLAN
  14. ------------------------------------------------------------------------------------------------------------------------
  15.  Index Only Scan using idx_test_dl on test  (cost=0.43..8.45 rows=1 width=15) (actual time=0.086..0.087 rows=1 loops=1)
  16.    Index Cond: ((id = 100) AND (name = 'val:100'::text))
  17.    Heap Fetches: 1
  18.  Planning Time: 0.224 ms
  19.  Execution Time: 0.104 ms
  20. (5 rows)
唯一索引
  1. postgres=# create unique index idx_test_wy  on test(id);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_wy" UNIQUE, btree (id)
  11.  
  12. postgres=# explain analyze select * from test where id = 100;
  13.                                                     QUERY PLAN
  14. -------------------------------------------------------------------------------------------------------------------
  15.  Index Scan using idx_test_wy on test  (cost=0.43..8.45 rows=1 width=15) (actual time=0.099..0.101 rows=1 loops=1)
  16.    Index Cond: (id = 100)
  17.  Planning Time: 0.350 ms
  18.  Execution Time: 0.119 ms
  19. (4 rows)
表达式索引
  1. postgres=# create index idx_test_bds on test((id+1000));
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_bds" btree ((id + 1000))
  11.  
  12. postgres=# explain analyze select * from test where id+1000 > 10000;
  13.                                                                QUERY PLAN
  14. -----------------------------------------------------------------------------------------------------------------------------------------
  15.  Bitmap Heap Scan on test  (cost=31205.10..83233.11 rows=1666667 width=15) (actual time=578.175..1501.361 rows=4991000 loops=1)
  16.    Recheck Cond: ((id + 1000) > 10000)
  17.    Heap Blocks: exact=26980
  18.    ->  Bitmap Index Scan on idx_test_bds  (cost=0.00..30788.43 rows=1666667 width=0) (actual time=574.862..574.863 rows=4991000 loops=1)
  19.          Index Cond: ((id + 1000) > 10000)
  20.  Planning Time: 0.110 ms
  21.  Execution Time: 1633.012 ms
  22. (7 rows)
部分索引
  1. postgres=# create index idx_test_bf on test(id) where (id between 1000 and 2000);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_bf" btree (id) WHERE id >= 1000 AND id <= 2000
  11.  
  12. postgres=# explain analyze select * from test where id between 1500 and 2000;
  13.                                                        QUERY PLAN
  14. ------------------------------------------------------------------------------------------------------------------------
  15.  Index Scan using idx_test_bf on test  (cost=0.28..27.29 rows=515 width=15) (actual time=0.035..0.090 rows=501 loops=1)
  16.    Index Cond: (id >= 1500)
  17.  Planning Time: 0.265 ms
  18.  Execution Time: 0.110 ms
  19. (4 rows)
  20.  
  21. postgres=# explain analyze select * from test where id between 500 and 1000;
  22.                                                        QUERY PLAN
  23. -------------------------------------------------------------------------------------------------------------------------
  24.  Seq Scan on test  (cost=10000000000.00..10000102028.00 rows=272 width=15) (actual time=0.035..427.097 rows=501 loops=1)
  25.    Filter: ((id >= 500) AND (id <= 1000))
  26.    Rows Removed by Filter: 4999499
  27.  Planning Time: 0.107 ms
  28.  Execution Time: 427.120 ms
  29. (5 rows)
覆盖索引
  1. postgres=# create index idx_test_fg on test(id) include(name);
  2. CREATE INDEX
  3. postgres=# \d test
  4.                 Table "public.test"
  5.  Column |  Type   | Collation | Nullable | Default
  6. --------+---------+-----------+----------+---------
  7.  id     | integer |           |          |
  8.  name   | text    |           |          |
  9. Indexes:
  10.     "idx_test_fg" btree (id) INCLUDE (name)
  11.  
  12. postgres=# explain analyze select * from test where id = 100;
  13.                                                        QUERY PLAN
  14. ------------------------------------------------------------------------------------------------------------------------
  15.  Index Only Scan using idx_test_fg on test  (cost=0.43..8.45 rows=1 width=15) (actual time=0.031..0.032 rows=1 loops=1)
  16.    Index Cond: (id = 100)
  17.    Heap Fetches: 1
  18.  Planning Time: 0.264 ms
  19.  Execution Time: 0.047 ms
  20. (5 rows)

常用管理操作

  1. --查询
  2. postgres=# select * from pg_indexes where tablename = 'test';
  3.  schemaname | tablename |  indexname  | tablespace |                                indexdef
  4. ------------+-----------+-------------+------------+-------------------------------------------------------------------------
  5.  public     | test      | idx_test_fg |            | CREATE INDEX idx_test_fg ON public.test USING btree (id) INCLUDE (name)
  6. (1 row)
  7.  
  8. --重建
  9. postgres=# reindex index idx_test_fg;
  10. REINDEX
  11.  
  12. --重命名
  13. postgres=# alter index idx_test_fg rename to idx_test_id;
  14. ALTER INDEX
  15.  
  16. --修改表空间
  17. postgres=# alter index idx_test_id set tablespace tab1;
  18. ALTER INDEX
  19.  
  20. --删除
  21. postgres=# drop index idx_test_id;
  22. DROP INDEX

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

参考文章：https://blog.csdn.net/songyundong1993/article/

details/128938714  https://blog.csdn.net/nusliang/article/

details/126734502  zhuanlan.zhihu.com/p/412628264

读者专属技术群

构建高质量的技术交流社群，欢迎从事后端开发、运维技术进群（备注岗位，已在技术交流群的请勿重复添加）。主要以技术交流、内推、行业探讨为主，请文明发言。广告人士勿入，切勿轻信私聊，防止被骗。

扫码加我好友，拉你进群

d749c23c13d150ee4eb0f3df22efa8a6.jpeg

推荐阅读 点击标题可跳转

一名鹅厂员工悲观发文，活着究竟是为了什么？

刚刚，腾讯突然宣布：下线！彻底凉了。。。

卸载 Navicat ！又一款更牛逼数据库神器，太炸了

就在今天！国产操作系统迎来又一次重大突破

突发！美国将限制中国使用亚马逊、微软等云服务

这些爆火的国产软件，却被大众误以为是外国佬研发的

最值得加入的173家国企名单（含各企业薪资）

47454e02163ea0d9ddcb096a38eaf8e4.png

PS：因为公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。点“在看”支持我们吧!

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/606099
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号