Postgres索引详解

适用于 Postgres 初学者的 b-tree、GIN 和 GIST 索引示例。

数据库索引的作用类似于书后的索引部分。数据库索引存储有关数据行在表中的位置的信息,因此数据库不必扫描整个表以获取信息。当数据库有要检索的查询时,它首先转到索引,然后使用该信息检索请求的数据。

索引是它们自己的数据结构,它们是 Postgres 数据定义语言 (DDL) 的一部分。它们与数据表和其他对象一起存储在磁盘上。

  • B-tree 索引是最常见的索引类型,如果您创建索引但不指定类型,它将是默认索引。B 树索引非常适合对您经常查询的信息进行通用索引。
  • BRIN 索引 是块范围索引,专门针对您正在搜索的数据以块为单位的非常大的数据集,例如时间戳和日期范围。众所周知,它们非常高效且节省空间。
  • GIST 索引在您的数据库中构建搜索树,最常用于空间数据库和全文搜索用例。
  • 当您在单个列中有多个值时, GIN 索引很有用,这在您存储数组或 json 数据时很常见。

使用 Explain analyze

Explain analyze会给你提供信息,如查询计划、执行时间,以及任何查询的其他有用信息。因此,当你在使用索引时,你将使用explain analyze检查索引,以审查查询路径和查询时间。

你会看到查询计划表明是一个 "Seq扫描",或一个顺序扫描。这意味着它扫描了表中的每一条数据行,看它是否符合查询条件。你可能会猜到,对于较大的表,顺序扫描可能会占用相当多的时间,所以这就是索引节省数据库工作量的地方。

Seq Scan on weather (cost=0.00..168.00 rows=496 width=102) (actual time=0.011..0.181 rows=100 loops=1)

如果你使用的是索引,你会在Explain的结果中看到索引扫描。

Bitmap Index Scan on idx_weather_type (cost=0.00..8.00 rows=496 width=0) (actual time=0.027..0.027 rows=496 loops=1

分析数据库

ANALYZE命令收集关于内部查询计划器使用的表或数据库的信息。在某些情况下,你可能想在添加索引之前或之后运行这个命令,以便数据库使用最新的查询计划。在我的测试中,这似乎影响了较大的数据库。

B-树样本

对于B-Tree样本,我使用了一些开放的天气数据,数据事件按类型、损害、时间和地点分类1。对于只是一个非常基本的索引,我打算找到冬季风暴的一切。一旦我添加了这个索引,这就意味着为了获得这些数据,数据库不必扫描所有的天气事件来为我获得关于恶劣天气事件的额外数据,它已经知道在哪里可以找到这些数据。

SELECT * FROM weather where event_type='Winter Storm'

在持久索引之前的explain analyze:

Seq Scan on weather (cost=0.00..9204.64 rows=3158 width=853) (actual time=0.008..27.619 rows=3182 loops=1)

Execution Time: 27.778 ms

索引:

CREATE INDEX idx_weather_type ON weather(event_type);

索引之后的explain 分析:

Bitmap Index Scan on idx_weather_type (cost=0.00..35.98 rows=3158 width=0) (actual time=0.247..0.247 rows=3182 loops=1)

Execution Time: 3.005 ms

多列B型树索引

索引并不总是只为单列创建的 - Postgres也支持多列索引。如果你知道你将同时对多个列进行大量的查询,那么这些索引就会有用:

SELECT * FROM weather WHERE event_type='Winter Storm' AND damage_crops > '0'

在索引之前的explain分析:

Seq Scan on weather (cost=0.00..9402.36 rows=2586 width=853) (actual time=0.007..67.365 rows=2896 loops=1)

Execution Time: 67.499 ms

创建多列索引:

CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);

索引之后的explain分析:

Bitmap Index Scan on idx_storm_crop (cost=0.00..38.15 rows=2586 width=0) (actual time=0.339..0.339 rows=2896 loops=1)

Execution Time: 2.204 ms

哦,.... 如果你在创建索引的过程中迷失了方向,需要看看你已经得到了什么,这将显示你在一个特定表上的所有索引。

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'weatherevents';

Drop index indexname可以删除索引

BRIN指数样本

在使用大型数据集,特别是有时间序列或时间戳的数据时,BRIN通常非常有用。我使用了一个物联网数据集2的样本,每天有成千上万的数据行。

SELECT device, humidity FROM iot WHERE ts between '2020-07-13 0:00' AND '2020-07-14 0:00'

索引之前的Explain分析:

Parallel Seq Scan on iot (cost=0.00..10363.95 rows=896 width=28) (actual time=12.710..42.080 rows=16707 loops=3)

Execution Time: 67.851 ms

建立索引:

CREATE INDEX iot_time ON iot USING brin(ts);

再次explain分析结果:

Bitmap Index Scan on iot_time (cost=0.00..12.26 rows=54025 width=0) (actual time=0.046..0.047 rows=10240 loops=1)

Execution Time: 10.513 ms

你会经常听到BRIN索引是非常节省空间的。所以当你通过索引工作时,你可能想查询实际索引的大小。比如说

pg_size_pretty(pg_relation_size('iot_time'));

使用GIST的基本空间索引

如果你在你的数据库中使用空间数据,你可能有很多数据,索引可能是至关重要的。对于空间索引,我使用了PostGIS教程3中的数据和例子。如果你刚开始使用空间数据,我强烈推荐这个教程的总体内容。

SQL query

SELECT count(blocks.blkid)

FROM nyc_census_blocks blocks

JOIN nyc_subway_stations subways

ON ST_Contains(blocks.geom, subways.geom)

WHERE subways.name LIKE 'B%';

开始 explain analyze

Timing: Generation 4.364 ms, Inlining 360.628 ms, Optimization 615.663 ms, Emission 559.573 ms, Total 1540.227 ms

Execution Time: 1467.916 ms

实现样别索引spatial GIST index

CREATE INDEX nyc_census_blocks_geom_idx

ON nyc_census_blocks

USING GIST (geom);

explain analyze时间:

Execution Time: 7.575 ms

用于JSON的GIN索引

JSON数据在Postgres用户中被广泛采用,Postgres核心项目也通过广泛的功能来支持JSON数据类型的采用。如果你的数据在每个字段中都列出了几个对象,那么有一些索引类型可以真正帮助你。GIN索引类型通常用于此。在这个例子中,我使用了NASA的一个json文件,其中有流星的位置信息。

SQL query

SELECT data -> 'name' as name FROM meteors WHERE data @> '{"mass": "100"}';

索引前的解释分析:

Parallel Seq Scan on meteors (cost=0.00..23926.28 rows=4245 width=32) (actual time=0.065..114.114 rows=1024 loops=3) Execution Time: 123.698 ms

建立索引:

CREATE INDEX gin_test ON meteors USING gin(data)

索引后解释分析:

Bitmap Index Scan on gin_test (cost=0.00..116.40 rows=10187 width=0) (actual time=12.164..12.164 rows=3072 loops=1) Execution Time: 22.017 ms

用索引找到合适的位置

你不应该在你要运行一个一次性查询的时候临时创建一个索引。一个好的索引计划需要计划和测试。索引是存储在磁盘上的,所以它们也会占用空间,这也是一个考虑因素。对于每一个插入的新数据行或更新的现有数据行,数据库会自动更新索引条目。索引绝对也会对数据库写操作的性能产生影响,所以一定要研究一些。就像Craig最近说的,每个人都需要找到他们的Goldilocks索引:不要太大,不要太小,但要恰到好处。