Postgres索引详解

作者：王素维060615 | 来源：互联网 | 2023-09-12 11:25

适用于Postgres初学者的b-tree、GIN和GIST索引示例。数据库索引的作用类似于书后的索引部分。数据库索引存储有关数据行在表中的位置的信息，因此数据库不必扫描整个表以获

适用于 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 100%" cellpadding="0" cellspacing="0" border="0">

Bitmap Index Scan on idx_weather_type (cost=0.00..8.00 rows=496 100%" cellpadding="0" cellspacing="0" border="0">

SELECT * FROM weather where event_type='Winter Storm'

在持久索引之前的explain analyze：

Seq Scan on weather (cost=0.00..9204.64 rows=3158 100%" cellpadding="0" cellspacing="0" border="0">

CREATE INDEX idx_weather_type ON weather(event_type);

索引之后的explain 分析：

Bitmap Index Scan on idx_weather_type (cost=0.00..35.98 rows=3158 100%" cellpadding="0" cellspacing="0" border="0">

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

在索引之前的explain分析：

Seq Scan on weather (cost=0.00..9402.36 rows=2586 100%" cellpadding="0" cellspacing="0" border="0">

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 100%" cellpadding="0" cellspacing="0" border="0">

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 100%" cellpadding="0" cellspacing="0" border="0">

CREATE INDEX iot_time ON iot USING brin(ts);

再次explain分析结果：

Bitmap Index Scan on iot_time (cost=0.00..12.26 rows=54025 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 100%" cellpadding="0" cellspacing="0" border="0">

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索引：不要太大，不要太小，但要恰到好处。

推荐阅读

tree
11.通配符

在搜索数据库中的数据时，您可以使用SQL通配符。SQL通配符在搜索数据库中的数据时，SQL通配符可以替代一个或多个字符。SQL通配符必须与LIKE运算符 ... [详细]

蜡笔小新 2023-10-17 18:58:51
tree
Kotlin中扩展函数的惯用用法及其合理性

本文讨论了Kotlin中扩展函数的一些惯用用法以及其合理性。作者认为在某些情况下，定义扩展函数没有意义，但官方的编码约定支持这种方式。文章还介绍了在类之外定义扩展函数的具体用法，并讨论了避免使用扩展函数的边缘情况。作者提出了对于扩展函数的合理性的质疑，并给出了自己的反驳。最后，文章强调了在编写Kotlin代码时可以自由地使用扩展函数的重要性。 ... [详细]

蜡笔小新 2023-12-12 19:17:21
import
android studio生成jks,android studio生成 keystore 以及获取 SHA1值等

合并列值－合并为一列问题需求：createtabletab(Aint,Bint,Cint)inserttabselect1,2,3unionallsel ... [详细]

蜡笔小新 2023-12-11 12:32:55
loops
PDO混合未命名和命名参数 - PDO mixing unnamed and named parameters

Imtryingtousethisforabasicsearchwithpagination:我正在尝试使用此分区进行基本搜索:$construct?AND? ... [详细]

蜡笔小新 2023-10-15 11:14:05
loops
SQL日志收缩及截断方法详解

本文详细介绍了SQL日志收缩的方法，包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时，还介绍了截断日志的原理和注意事项，包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法，可以有效减小逻辑日志的大小，提高数据库的性能。 ... [详细]

蜡笔小新 2023-12-14 18:23:25
tree
推荐系统遇上深度学习(十七）详解推荐系统中的常用评测指标

原创：石晓文小小挖掘机2018-06-18笔者是一个痴迷于挖掘数据中的价值的学习人，希望在平日的工作学习中，挖掘数据的价值， ... [详细]

蜡笔小新 2023-12-13 19:35:25
text
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新 2023-12-13 15:15:30
object
【openwrt】设备mt7628关于wan侧eth0.1 mac地址固定的问题

本文讨论了在openwrt-17.01版本中，mt7628设备上初始化启动时eth0的mac地址总是随机生成的问题。每次随机生成的eth0的mac地址都会写到/sys/class/net/eth0/address目录下，而openwrt-17.01原版的SDK会根据随机生成的eth0的mac地址再生成eth0.1、eth0.2等，生成后的mac地址会保存在/etc/config/network下。 ... [详细]

蜡笔小新 2023-12-12 17:47:48
bit
CentOS安装Python2.7.2的步骤和注意事项

本文介绍了在CentOS上安装Python2.7.2的详细步骤，包括下载、解压、编译和安装等操作。同时提供了一些注意事项，以及测试安装是否成功的方法。 ... [详细]

蜡笔小新 2023-12-12 13:54:31
bit
Windows7 64位系统安装PLSQL Developer的步骤和注意事项

本文介绍了在Windows7 64位系统上安装PLSQL Developer的步骤和注意事项。首先下载并安装PLSQL Developer，注意不要安装在默认目录下。然后下载Windows 32位的oracle instant client，并解压到指定路径。最后，按照自己的喜好对解压后的文件进行命名和压缩。 ... [详细]

蜡笔小新 2023-12-12 13:32:08
bit
OpenStack及其构成简介

本文介绍了OpenStack的逻辑概念以及其构成简介，包括了软件开源项目、基础设施资源管理平台、三大核心组件等内容。同时还介绍了Horizon(UI模块)等相关信息。 ... [详细]

蜡笔小新 2023-12-12 06:47:38
bit
文件压缩解压的哈夫曼树实现

本文介绍了使用哈夫曼树实现文件压缩和解压的方法。首先对数据结构课程设计中的代码进行了分析，包括使用时间调用、常量定义和统计文件中各个字符时相关的结构体。然后讨论了哈夫曼树的实现原理和算法。最后介绍了文件压缩和解压的具体步骤，包括字符统计、构建哈夫曼树、生成编码表、编码和解码过程。通过实例演示了文件压缩和解压的效果。本文的内容对于理解哈夫曼树的实现原理和应用具有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-11 14:13:46
join
HashMap的相关问题及其底层数据结构和操作流程

本文介绍了关于HashMap的相关问题，包括其底层数据结构、JDK1.7和JDK1.8的差异、红黑树的使用、扩容和树化的条件、退化为链表的情况、索引的计算方法、hashcode和hash()方法的作用、数组容量的选择、Put方法的流程以及并发问题下的操作。文章还提到了扩容死链和数据错乱的问题，并探讨了key的设计要求。对于对Java面试中的HashMap问题感兴趣的读者，本文将为您提供一些有用的技术和经验。 ... [详细]

蜡笔小新 2023-12-10 17:07:36
cookie
提升Python编程效率的十点建议

本文介绍了提升Python编程效率的十点建议，包括不使用分号、选择合适的代码编辑器、遵循Python代码规范等。这些建议可以帮助开发者节省时间，提高编程效率。同时，还提供了相关参考链接供读者深入学习。 ... [详细]

蜡笔小新 2023-12-14 21:51:04
io
Ubuntu安装常用软件详细步骤

目录1.GoogleChrome浏览器2.搜狗拼音输入法3.Pycharm4.Clion5.其他软件1.GoogleChrome浏览器通过直接下载安装GoogleChro ... [详细]

蜡笔小新 2023-12-12 21:26:41

王素维060615

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章