PostgreSQL 索引

PostgreSQL 性能调优：优化数据库索引

An elephant being workshopped by a developer—a metaphor for fine-tuning PostgreSQL performance

作者：Umair Shahid 和 Carlota Soto

高效的数据检索对于实现 PostgreSQL 应用程序的峰值性能至关重要，尤其是在处理海量数据集时。数据库以索引的形式提供了一个强大的解决方案，这是一种加速检索特定行的机制。本文将探讨 PostgreSQL 索引，从概述开始，逐步介绍优化索引性能的最佳实践。

本文是我们关于 PostgreSQL 性能调优的四部分系列文章的第三部分。如果您错过了我们指南的前几章，请查看 第一部分（关于数据库大小调整） 和 第二部分（关于您可能想要微调的关键 PostgreSQL 参数）。

什么是索引？回顾 Postgres 基础知识

PostgreSQL 索引的核心是一种数据结构，旨在加快数据库表内的数据检索操作。可以把它想象成书中的目录。您无需费力地搜索整本书来精确定位特定主题，而是可以使用目录快速导航到相关页面。

类似地，在 PostgreSQL 中，索引就像数据库引擎的 GPS，使其能够有效地定位和检索特定行，而无需扫描整个表。简而言之，索引是指向表内数据的指针。

这些数据库索引是独立的数据结构，与数据表和其他相关目录对象一起驻留在磁盘上。有时，当查询所需的所有数据都在索引中时，Postgres 可以使用仅索引扫描，而无需触碰表。

常见的 PostgreSQL 索引类型

Postgres 支持多种索引类型，从默认的 B 树索引到更奇特的类型，如哈希、GIN、GiST 或 BRIN。它们在 PostgreSQL 文档中有详细记录，但这里有一个快速总结

B 树索引（PostgreSQL 中的默认索引类型）

CREATE INDEX index_product_id ON products (product_id);

  • PostgreSQL 中的默认索引类型。

  • 支持 <、<=、=、>=、>、BETWEEN、IN、IS NULL、IS NOT NULL。

  • 按升序排列条目。

哈希索引

CREATE INDEX index_product_id ON products USING HASH (product_id);

  • 非常适合相等性检查，尤其是整数。

  • 不支持范围查询或排序。

复合索引

CREATE INDEX index_product_id_name ON products (product_id, product_name);

  • 在多个表列上定义的多列索引。

  • 根据 WHERE 子句中常用的列进行优化。

GiST 和 SP-GiST 索引

CREATE INDEX idx_gist ON my_table USING gist(my_column);

  • GiST（通用搜索树）支持对复杂数据类型（如几何形状和全文文档）进行索引。

  • 允许使用自定义运算符进行查询，支持自然语言处理等高级搜索功能。

  • 使用“有损”索引策略来汇总复杂项目，可能会导致需要详细行检查的误报。

CREATE INDEX idx_spgist ON my_table USING spgist(my_column);

  • SP-GiST（空间分区通用搜索树）非常适合具有自然分区的数据，例如空间数据和层次数据。

  • 允许在不同节点使用不同的分支因子，从而允许定制树结构以减少搜索空间。

  • 对于需要复杂空间查询或管理大型数据层次结构的应用程序特别有效。

部分索引

CREATE INDEX index_product_id ON products(product_id) where product_available = ‘true’;

  • 建立在表的子集之上，由条件表达式定义。

  • 用于过滤掉不常查询的行。

覆盖索引

CREATE INDEX index_product_id_name_status ON products (product_id, product_name) include (status);

  • 当选择列表与索引列匹配时，允许仅索引扫描。

  • 使用 INCLUDE 关键字指定其他列。

块范围索引 (BRIN)

CREATE INDEX brin_example_index ON logs USING BRIN(log_date);

  • 专为时间序列数据等大型排序表而设计。

  • 节省空间，在逻辑块内存储最小值和最大值。

索引最佳实践：优点

使用索引为 PostgreSQL（和其他关系数据库）带来了一些关键优势

  • 加速数据检索。索引在显著减少获取数据所需的时间方面发挥着关键作用，尤其是在处理大型表时。如果没有索引，数据库将需要执行全表扫描，这可能会非常缓慢。例如，如果没有索引，数据检索将基于对象的键。虽然这对于直接查找来说可能很快，但对于复杂查询或部分匹配来说效率很低，而 PostgreSQL 可以使用索引优雅地处理这些查询。

  • 增强的查询性能。PostgreSQL 查询规划器/优化器使用索引来推导出执行查询的最有效方式。涉及 WHERE 子句中指定的条件或需要表之间连接的查询，使用索引可以显著提高性能。这些查询可以利用索引快速识别符合指定条件的行。

  • 最小化磁盘 I/O。索引存储表数据的子集，从而减少磁盘 I/O 操作。这种减少不仅加快了查询执行速度，还减轻了存储系统的负担。

  • 数据完整性强制执行。唯一索引充当特定列中重复值的守护者。它们通过确保没有两行在指定列中具有相同的值，在维护数据完整性方面发挥着关键作用。外键约束是数据完整性的另一个方面，在引用列上使用索引可以提高效率，从而加快级联更新或删除等操作的速度。

索引最佳实践：弊端

根据我们的经验，与数据库相关的所有内容（包括索引）如果过度使用都会有缺点。精心设计的索引将为您提供最佳性能，但过度索引是大型 PostgreSQL 部署中的一个常见问题，可能会导致多个问题。

一个明显的问题是额外存储空间的消耗：您添加到表的每个索引都需要其自己的单独存储分配。从根本上讲，索引包含索引列的值以及对表中实际行的引用（或指针）。

对于较小的数据集，索引使用的空间通常占总表大小的一小部分。但是，随着 PostgreSQL 表大小的增长，其关联索引的大小也会增长。当对多列进行索引或索引列包含文本或字符串数据时，大小可能会成为一个障碍。与整数或日期等固定大小的数据类型不同，字符串的长度可以变化，这意味着索引不仅要容纳字符串值，还要容纳指示每个字符串长度的元数据，从而增加了空间开销。

PostgreSQL 表分区增加了一层复杂性。 分区是一种通过将大型表拆分为更小、更易于管理的部分来提高其性能和维护的技术，同时仍然将它们视为单个逻辑实体。

在 Timescale 中，所有 超表 都按时间自动分区。当对大型表进行分区时，每个分区本身就是一个表；如果将索引应用于分区表，则它们实际上会分别应用于每个分区。因此，如果您有数百或数千个分区，每个分区都有自己的索引，那么这些索引使用的累积存储空间可能会迅速增加。

低效地使用索引也会降低写入速度。当您引入索引时，每次插入、更新或删除行时都必须更新它。PostgreSQL 有一种称为 HOT（仅堆元组）的优化，它可以通过在更新特定列时避免索引更改来进行就地行修改。但是，如果一个表有许多索引，或者更新频繁地涉及索引列或表达式索引中使用的列，那么执行这些高效热更新的能力就会降低。

索引还会由于必要的索引调整而导致更多的 I/O 操作。每次在表或分区中添加、删除或更新数据时，也必须更新其关联的索引。这些更新可能会增加来自死元组的表膨胀，从而随着时间的推移降低性能。此外，维护大量索引会使数据库备份和恢复更加耗时。

索引是 PostgreSQL 数据库的一个重要方面，但必须养成监控和优化的习惯，以确保它们对您有利。请继续阅读有关改进索引使用情况的技巧，首先从确定您应该注意的 PostgreSQL 参数开始，因为它们会影响索引的使用。

索引的服务器参数最佳实践

某些服务器参数提供了一定程度的控制，可以控制查询规划器如何决定索引的使用，它们在优化查询性能方面发挥着重要作用。让我们介绍一下这些参数的最佳实践

work_mem 和 shared_buffers

我们在本系列的前一集中讨论了这些参数。work_mem 规定了分配给每个数据库操作（如排序和哈希）的内存量，其大小直接影响操作是保留在内存中还是溢出到磁盘。如果 work_mem 设置适当，它可以在内存中有效地容纳位图扫描和排序，从而减少 PostgreSQL 对索引的依赖。如果分配的内存太小，系统可能不得不使用基于磁盘的临时存储，从而影响性能。

shared_buffers 表示数据库磁盘页在主内存中的缓存，对于数据库访问底层磁盘的频率至关重要。大小合适的 shared_buffers 可以确保将频繁访问的表和索引页保留在快速的主内存中，从而大大减少 I/O 操作并提高整体查询响应时间。

请务必查看 第二部分 ，以获取有关这两个参数的更详细配置建议。

effective_cache_size

effective_cache_size 为查询规划器提供了一个估计值，用于估计有多少内存可用于缓存数据和索引。虽然它本身不分配内存，但它会影响规划器关于查询执行的决策。

此参数对于查询规划至关重要，因为它可以指导规划器根据不同策略的预期成本选择最有效的查询计划。例如，假设 effective_cache_size 的值很高，并且规划器根据此设置认为，大部分所需数据可能都在操作系统的缓存中。在这种情况下，查询规划器更有可能选择索引扫描而不是顺序扫描，因为它假定有充足的缓存内存。

最佳值将根据您的设置而异，但一个常见的起点是将其设置为机器总 RAM 的 50-70%。

random_page_cost 和 seq_page_cost

random_page_cost 和 seq_page_cost 是配置参数，可帮助 PostgreSQL 查询规划器估计随机页面读取与顺序页面读取的相对成本。默认情况下，seq_page_cost 设置为 1（一），表示顺序读取的基本成本，而 random_page_cost 设置为 4（四），表示随机访问的成本被认为是顺序访问的四倍。

通常，索引查找往往涉及随机磁盘 I/O，因为遍历索引树的性质。全表扫描更倾向于顺序扫描，但这可能会因频繁的更新或删除而异。向下调整 random_page_cost 可以通过缩小随机页面读取和顺序页面读取之间的感知成本差距，使规划器更倾向于索引扫描。

值得注意的是，默认值 4 更符合传统旋转磁盘的行为。但是，如果您的 PostgreSQL 实例运行在 SSD 上，您可以将 random_page_cost 调整到 1.1 左右，以反映 SSD 的随机访问惩罚显著降低。

cpu_index_tuple_cost

此参数表示在查询执行期间扫描和处理索引条目所需的 CPU 操作的相对成本。

从理论上讲，调整 cpu_index_tuple_cost 可以让您影响查询规划器关于是使用索引还是顺序扫描来处理特定查询的选择。较低的值会使索引扫描在成本方面更具吸引力，而较高的值会导致规划器倾向于顺序扫描。

在实践中，不建议更改 cpu_index_tuple 的默认值（在最新版本的 PostgreSQL 中为 0.005），至少在没有进行彻底测试的情况下不建议这样做。与风险相比，调整它带来的潜在性能提升通常微不足道，例如，查询计划欠佳和意外的性能下降。

如何知道我的 PostgreSQL 索引是否有效？

价值百万美元的问题！

确定您的 PostgreSQL 索引是否需要优化需要通过多方面的方法进行持续监控。在 PostgreSQL 中，索引不是“一劳永逸”的；随着工作负载的演变，它们将需要维护以确保数据库以最佳状态运行。

值得庆幸的是，PostgreSQL 附带了您可以用来监控索引的出色工具和技术。让我们介绍一些您可以遵循的最有效的策略，以识别您的索引是否可能无法按预期工作。

通过 pg_stat_user_indexes 查找未使用的索引

正如我们在本文开头提到的，过度索引是许多大型 PostgreSQL 部署中的一个常见问题。一旦您的数据库开始增长，识别和删除未使用的或未充分利用的索引对于保持效率至关重要。过多的索引会导致多个问题

  • 每次添加、删除或修改行时，都需要更新该表上的所有索引。如果索引过多，这种写入开销可能会变得很大，从而减慢 INSERT、UPDATE 和 DELETE 操作的速度。

  • 索引会消耗磁盘空间。过多或未使用的索引会浪费宝贵的存储空间，尤其是在大型表上。

  • 索引需要定期进行真空和分析，更多的索引会导致更长的维护窗口。

如果您想发现可能未使用的或未充分利用的索引，pg_stat_user_indexes 是您的朋友。您可以查看 idx_scan 列，该列统计在每个索引上启动的索引扫描次数。下面是一个示例查询，可以帮助识别未使用的或很少使用的索引

SELECT
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS index_scan_count
FROM
pg_stat_user_indexes
WHERE
idx_scan < 50 -- Choose a threshold that makes sense for your application.
ORDER BY
index_scan_count ASC,
pg_relation_size(indexrelid) DESC;

在上面的示例中，我们正在查找扫描次数少于 50 次的索引（这是一个任意数字，仅供参考 - 您应该根据应用程序的使用模式和自上次统计信息重置以来的持续时间进行调整；此外，如果您有每日或每周批处理作业，请确保您没有过早地删除这些不太频繁的任务使用的索引）。

监控 shared_blks_read 和 blk_read_time 以确定是否需要新索引

相反，有时您可能会缺少索引 - 随着工作负载和查询模式的变化，就可能会发生这种情况。

pg_stat_statements 视图又一次成为了您的朋友：它还可以帮助您识别可以通过其他索引获益的慢查询，方法是使用指标 shared_blks_read 和 blk_read_time。

shared_blks_read 指示从共享内存读取的磁盘块数；如果此参数的值较高，则表明系统正在直接从磁盘读取大量数据。

blk_read_time 表示读取这些块所花费的总时间，以毫秒为单位。较高的值表示系统不仅从磁盘读取大量块，而且还花费了大量时间来执行此操作。

如果您注意到与重要查询关联的 shared_blks_read 和 blk_read_time 的值较高，则表明PostgreSQL正在从磁盘获取数据，而不是有效地使用索引。创建索引可以显著提高查询性能。

让我们再次使用一个粒子示例来说明这一点。假设您运行一份每日报告，列出过去 24 小时内所有购物的客户。随着时间的推移，随着客户和购买数量的增长，您会注意到查询花费的时间越来越长。为了诊断问题，您求助于 pg_stat_statements

查询

shared_blks_read

blk_read_time

SELECT * FROM purchases WHERE purchase_date > NOW() - '1 day'::INTERVAL;

5,000

300

在您的用例中，较高的 shared_blks_read 值表明PostgreSQL在执行此查询时直接从磁盘获取了大量数据。此外，300 毫秒的 blk_read_time 表示检索此数据存在显著延迟，应该可以执行得更好。（同样，不要将这些值视为普遍适用-您必须通过加班监控来确定自己的正常范围。）

鉴于此见解，您决定调查购买表上的索引。您发现 purchase_date 列上没有索引。通过添加适当的索引，您也许可以显著减少 shared_blks_read 和 blk_read_time 的值，从而加快查询执行速度

CREATE INDEX idx_purchase_date ON purchases(purchase_date);

创建索引并填充索引后，您可以重新访问 pg_stat_statements 以确认改进。

使用 EXPLAIN ANALYZE 并查找 Seq Scan

我们在本系列中多次提到“EXPLAIN ANALYZE”，这是有充分理由的-它是一个很棒的工具。通过在执行查询之前运行 EXPLAIN ANALYZE，您可以检查计划程序生成的查询执行计划。它详细说明了正在使用哪些索引、如何检索数据以及哪个查询节点可能花费了过多的时间。对于优化索引的具体情况，如果您发现 PostgreSQL 在您希望它执行索引扫描时对大型表执行顺序扫描（Seq Scan），则这是系统未使用索引的另一个标志。

假设有一个包含一百万条记录的 customers 表，其中包含一个 email 列。您经常运行查询以按电子邮件地址查找客户，因此您在 email 列上设置了一个索引来优化这些查找。但是，随着时间的推移，您注意到查询性能似乎正在下降。为了对此进行调查，您决定使用 EXPLAIN ANALYZE 检查查询计划

EXPLAIN ANALYZE SELECT * FROM customers WHERE email = '[email protected]';

您将获得以下输出

Seq Scan on customers  (cost=0.00..20450.00 rows=1 width=1168) (actual time=500.032..600.021 rows=1 loops=1)
Filter: (email = '[email protected]'::text)
Rows Removed by Filter: 999999
Total runtime: 600.040 ms

输出显示 PostgreSQL 选择了对 customers 表进行 Seq Scan，即使 email 列上有一个索引。这种选择令人惊讶；您可能希望索引扫描对于这种特定的查找更有效。

输出还为您提供了所用的时间（总运行时间：600.040 毫秒），并突出显示了过滤器删除了表中几乎所有行（过滤器删除的行：999999），这表明确实进行了全面扫描。

此信息告诉您，email 列上的索引可能会更有效。潜在原因可能包括

  • 存在一些错误配置，使计划程序认为顺序扫描更便宜。我们前面介绍的参数（例如 random_page_cost 和 seq_page_cost）可能未根据您的资源进行准确设置。正如我们前面提到的，一个常见原因是 random_page_cost 太高，这会导致计划程序即使在索引扫描速度更快的情况下也会避免使用索引扫描。

  • 索引已损坏或丢失。这是一种罕见的情况，但索引可能会因磁盘问题、崩溃或其他问题而损坏。此外，如果意外删除了预期索引或从未创建过预期索引，则 PostgreSQL 将别无选择，只能恢复为顺序扫描。

  • 查询计划程序使用的统计信息已过期。PostgreSQL 依靠有关表和索引中数据分布的统计信息来生成高效的查询计划。随着数据的变化（由于添加、更新或删除），这些统计信息可能会过时。如果没有准确的统计信息，查询计划程序可能会做出次优选择，例如选择顺序扫描而不是索引扫描。运行 ANALYZE 命令可帮助系统收集最新的统计信息。

定期运行 VACUUM 和 ANALYZE

例行维护任务（如 VACUUM 和 ANALYZE）在优化 PostgreSQL 数据库中的索引方面起着至关重要的作用，因此，采用最佳实践，您可以获得显著成果。ANALYZE 操作值得特别注意。当您运行 ANALYZE 时，系统会检查表并更新与表中值的分布相关的统计信息。

这些统计信息至关重要，因为查询计划程序依靠它们来制定执行查询的最有效策略，其中通常包括决定是否使用索引。如前所述，如果没有更新的统计信息，计划程序可能会做出次优选择。

为这些维护任务（例如，每周）实施例行程序可能非常有益。这样的计划可以确保数据库中没有死行（由于更新或删除而不再可访问的过时数据），并且具有最新的统计信息。

索引最佳实践：总结

很好！现在，您有了一些工具，可以在扩展时加快索引游戏的速度。在结束之前，让我们快速回顾一下索引最佳实践。这些都很简单，但如果您遵循它们，您的索引将以最佳状态执行

  • **只对需要的内容进行索引。** 不要在每一列上都创建索引。评估您的查询模式，并创建与最常见查询一致的索引。过度索引会对写入操作产生负面影响，增加存储需求，并增加维护开销。

  • **避免在低基数列上建立索引。** 对具有很少不同值的均匀分布列（例如性别或布尔值）建立索引通常是一种反模式，因为使用索引生成的随机 I/O 仍将访问大多数页面，并且比顺序扫描表慢。有时，使用部分索引（具有 WHERE 子句）、多列索引或编写具有 char 的查询可以解决此问题。

  • **定期检查和优化索引。** 随着应用程序数据和使用模式的发展，现有索引的有效性可能会降低。定期检查和优化可以帮助索引与当前查询要求保持一致。

  • **测试和基准测试。** 在实施新索引之前，请使用 EXPLAIN ANALYZE 之类的工具评估它们对查询性能的影响，以验证计划程序是否使用了索引。

  • **添加或删除索引后更新统计信息。** 添加或删除索引后，请使用 ANALYZE 命令更新受影响表上的统计信息。有关索引列中值分布的准确统计信息对于查询优化器做出更好的决策至关重要。未能更新统计信息可能会导致查询计划欠佳和性能下降。

  • 并发重建索引。 标准 REINDEX 命令需要独占表锁，这会中断数据库操作。 相比之下，REINDEX CONCURRENTLY 允许表在索引重建过程中保持可访问性，尽管完成可能需要更长时间。 正如我们在上一篇文章中提到的，设置更高的 maintenance_work_mem 值可以大大加快索引创建过程。

分区表和超表的特殊注意事项

最后，在使用分区表或 Timescale 的超表（抽象分区，自动处理）时，还有一些额外的注意事项

  • 在 PostgreSQL 中，虽然您可以在父分区表上创建索引（适用于所有分区），但您也可以在单个分区上创建索引，称为本地索引。 如果您在 Timescale 中使用 CREATE_INDEX，则将在每个块上创建索引（而不是为整个超表创建一个事务），从而提高大型数据库的性能。

  • 创建超表时，Timescale 会自动在时间列上创建索引。

In the Timescale Console, you can inspect your database indexes via the Explorer view on the Timescale service page.

您可以通过 Timescale 控制台中的资源管理器视图检查数据库索引

  • 您还可以在普通 PostgreSQL 和超表中使用复合索引（包含两个或多个表列的索引）。 如果您的查询经常根据时间列与一个或多个其他列组合进行过滤，则这些索引非常有用。 例如，您可能希望保留时间索引以快速过滤给定时间范围，并在另一列（例如，device_id）上添加另一个索引。

  • 构建复合索引时，顺序很重要。 查看此博客文章以获取更多信息。

  • 要在 Timescale 中将索引定义为 UNIQUE 或 PRIMARY KEY 索引，该索引必须包含时间和分区列（如果您使用的话）。

  • 在 Timescale 中创建超表时，请将时间列的数据类型设置为 timestamptz 而不是 timestamp。

  • 在分区表中，特别要注意过度索引，因为您添加的每个索引都会在数据摄取期间带来额外的开销。

  • 如果您的数据稀疏（也称为经常包含 NULL 的列），您可以向索引添加一个子句，说明 WHERE 列 IS NOT NULL。 此子句可防止索引对 NULL 数据进行索引，从而使索引更加紧凑和高效。

  • 分区表的一个好处是可以快速删除分区（例如，基于时间的分区设置中的旧数据）。 删除分区时，也会删除其关联的索引，这比删除行和清理关联的索引条目要快得多。 由于 Timescale 的数据保留策略和连续聚合，此操作甚至更快。

  • 索引策略应考虑如何访问分区表中的数据。 例如，如果很少访问旧分区（例如，在基于时间的分区设置中），则可能不值得维护某些索引。

如本博客文章所示，将 PostgreSQL 表转换为超表时，您将保留索引。

总结

高效的 PostgreSQL 索引是微调应用程序内数据检索的有力助手。 索引是一种平衡行为，但创建适当的索引并维护它们将显着提高 PostgreSQL 数据库的整体性能。

本系列的最后一篇文章： PostgreSQL 性能调优：设计和实现数据库架构。