Documentation
搜索文档
karat

+

K

API 参考 ↗
用例开发SlateTroubleshooting优化Postgres中的查询

注意：以下翻译的准确性尚未经过验证。这是使用 AIP ↗ 从原始英文文本进行的机器翻译。

优化Postgres中的查询

本页面专注于优化Postgres中的查询性能。我们通常建议在Ontology功能之上搭建Slate应用，使用如对象集和操作等特性来读写数据。

设计一个响应式应用需要仔细的规划和考量。数据模型、查询结构和依赖关系图在应用性能和可用性上都扮演着重要角色。本指南聚焦于可以在Slate中编写的PostgreSQL查询的性能调优方面，包括性能启发式、识别调优机会的方法和提高性能的建议。

查询性能调优是一个迭代的过程。为了生成“正确”的答案，可以有许多不同的方式来编写相同的请求。

性能启发式

PostgreSQL查询的目标执行时间为<= 500毫秒。这对于少于1000万条记录的数据集的大多数应用案例来说应该是可能的，尽管在设计数据模型时需要仔细规划和考虑。

在评估查询性能时，尤其是对于一个新的数据集，确保在收集和分析统计数据之前多次运行查询。这将有助于确保数据集被正确缓存，并且性能与预期的使用情况更好地匹配。如果您对PostgreSQL缓存感兴趣，请参阅以下链接：

EXPLAIN... 解释

EXPLAIN是Postgres提供的一个特别有用的命令，用于返回查询执行计划。对于Postgres收到的每个请求，都会创建一个查询计划，该计划使用查询结构和数据属性来确定服务请求的最快方法。我们将从对EXPLAIN命令的快速回顾开始，因为它将在整个指南中被引用。

EXPLAIN

NODES

你可以把节点看作是一个逻辑的工作单元，或者查询评估中的一个步骤。节点以倒置图的形式返回，这意味着响应的第一行是执行的最后一个工作单元。每个节点前面都有->。

-> Index Scan using event_type_idx on event (...)

为什么这很重要：这些节点将被用来识别查询执行计划中的低效操作，并帮助优先考虑性能调优工作。

COST

第一个数字是启动成本（获取第一条记录的时间）。第二个数字是从启动到完成处理整个节点的成本。

(**cost=86.83..4577.07** rows=2368 width=10)

成本是Postgres查询规划器基于对象（通常是表）统计数据生成的估计。虽然这个数字不代表实际运行时间，但它应该与实际执行直接相关。

成本是几个工作组件的组合：顺序获取、非顺序（随机）获取、行处理、处理操作符（函数）和处理索引项。成本代表了I/O和CPU活动；数字越大，Postgres认为完成任务所需的工作量就越大。值得注意的是，Postgres查询优化器根据成本来决定使用哪个执行计划。

ROWS

估计将由此计划节点输出的行数。

(cost=86.83..4577.07 **rows=2368** width=10)

为什么这很重要：ROWS可以用来识别输出大量数据和/或未按预期行为返回的节点。

WIDTH

节点输出的行的估计平均大小（以字节为单位）。

(cost=86.83..4577.07 rows=2368 **width=10**)

为什么这很重要：WIDTH可以用来识别输出具有非常大属性或列数较多的行的节点。

EXPLAIN ANALYZE

NODES

见上文。

ACTUAL TIME

与_成本_类似，第一个数字是启动所需的实际时间（以毫秒为单位）。第二个数字是从启动到完成处理整个节点的实际时间。

(**actual time=10.313..12.530** rows=4857 loops=1)

顾名思义，实际时间是通过执行语句来捕获的。关键字ANALYZE告诉Postgres在显示执行计划的同时执行查询。如果您在查询超时时遇到问题，删除ANALYZE将只返回查询计划，这应比执行查询快得多。

为什么这很重要：这是最清晰的指示哪个节点或操作正在导致性能问题。

ROWS

估计由节点输出的行数。

(actual time=10.313..12.530 **rows=4857** loops=1)

为什么这很重要：ROWS可以帮助提供上下文以解释_为什么_某个特定操作可能花费的时间比预期的要长。

LOOPS

报告节点的总执行次数。显示的实际时间和行值是每次执行的平均值。将LOOPS值乘以实际时间即可得到节点中花费的总时间。

(actual time=10.313..12.530 rows=150 **loops=10**)

理解操作（计划节点）

扫描

  • 顺序扫描（seq scan）：Seq Scan操作扫描磁盘上存储的整个关系（表）（类似于TABLE ACCESS FULL）。无论关系模式、大小、约束和索引的存在与否，始终可以对关系执行seq扫描。
    • seq扫描的特点如下：
      • 启动速度快（顺序I/O比随机访问快得多）。
      • 每个块只读取一次。
      • 产生无序输出。
  • 索引扫描：Index Scan执行B树遍历，遍历叶节点以查找所有匹配的条目，并获取相应的表数据。它类似于INDEX RANGE SCAN，后跟TABLE ACCESS BY INDEX ROWID操作。
    • 索引扫描的特点如下：
      • 随机访问比顺序I/O慢得多。
      • 需要额外的I/O来访问索引。
      • 潜在地多次读取相同的块。
      • 产生有序输出。
  • 位图索引/堆扫描：普通索引扫描一次从索引中获取一个元组指针，并立即访问表中的该元组。位图扫描一次从索引中获取所有元组指针，使用内存中的“位图”数据结构对它们进行排序，然后按物理元组位置顺序访问表元组。
    • 位图索引/堆扫描的特点如下：
      • 顺序I/O与索引选择性。
      • 启动慢，因为所有索引元组都被读取和排序。
      • 通常选择用于IN和=ANY（数组）操作符，以及低选择性索引扫描。
      • 可以组合多个索引。
      • 产生无序输出。
  • 仅索引：Index Only Scan执行B树遍历，遍历叶节点以查找所有匹配的条目。不需要访问表，因为索引拥有满足查询的所有列（除了MVCC可见性信息）。

合并

合并操作通常一次只处理两个表。当一个查询涉及合并超过两个表时，合并是顺序执行的：首先是两个表，然后是中间结果与下一个表，依此类推。在合并的上下文中，术语“表”因此也可以表示“中间结果”。

  • 嵌套循环：通过从一个表中获取结果并为第一个表中的每一行查询另一个表来合并两个表。
    • 通常是性能最差的合并形式。
    • 生成第一条记录的速度快。
    • 如果第二个子级慢，可能导致负性能。
    • 唯一能够执行CROSS JOIN的合并。
    • 唯一能够执行不等式合并条件的合并。
  • 合并合并：合并合并将两个排序列表像拉链一样组合在一起。合并的两边必须预排序。
    • 只能用于等式合并条件。
    • 通常是大型数据集的最优性能。
    • 需要有序输入——这可能需要缓慢的排序或索引扫描。
    • 启动慢，因为所有索引元组都被读取和排序。
  • 哈希合并：哈希合并将合并一侧的候选记录加载到哈希表中（在计划中标记为Hash），然后对合并另一侧的每条记录进行探测。
    • 只能用于等式合并条件。
    • 通常是将大型表与小型表合并的最优性能。
    • 仅适用于可哈希的数据类型。
    • 由于对较小表进行哈希处理导致启动慢。
    • 如果表统计数据过时且不正确，性能会受到负面影响。

聚合

  • GroupAggregate：根据group by子句对预排序集合进行聚合。此操作不缓冲大量数据。
  • HashAggregate：使用临时哈希表对记录进行分组。HashAggregate操作不需要预排序数据集；相反，它使用大量内存来物化中间结果（非流水线）。输出没有任何有意义的顺序。
  • Unique
  • WindowAgg：表示使用窗口函数。

杂项

  • Sort
    • 发生在ORDER BY、DISTINCT、GROUP BY、UNION和合并合并时。
    • 启动时间长。
    • 如果排序适合work_mem，则可以使用快速排序。
    • 如果排序不适合内存，将溢出到磁盘并使用临时文件，这可能非常昂贵。
  • Limit
    • 处理LIMIT和OFFSET。
    • 如果没有WHERE子句，可以用于min()和max()。
    • OFFSET跳过的记录仍然会生成/物化，但在返回结果之前会被丢弃。
    • 子扫描的成本仍然是全部成本。
    • Sort与limit结合可以使用排序的优化形式。

查询调优最佳实践

SELECT

  • 仅包括显示所需的属性，这将有助于限制支持请求的页面的提取。

DISTINCT

  • DISTINCT来自PostgreSQL代码库的较旧部分，使用了一种效率较低的方法来识别不同的记录。
  • 如果可能，避免使用DISTINCT，而使用GROUP BY或子查询。

JOIN

  • 当合并表时，尝试在ON子句中使用简单的等式语句（如a.id = b.person_id）。这样可以使用更有效的合并技术（如哈希合并而不是嵌套循环合并）。
  • 在可能的情况下，将子查询转换为JOIN语句，因为这通常允许优化器了解意图，并可能选择更好的计划。
  • 正确使用JOIN：您是否仅仅因为得到重复结果而使用GROUP BY或DISTINCT？这通常表明JOIN使用不当，并可能导致更高的成本。
  • 如果执行计划正在使用哈希合并，如果表大小估计错误，它可能会非常慢。因此，通过审查您的真空策略来确保您的表统计数据准确。
  • 在可能的情况下，避免相关子查询；它们可能显著增加查询成本。
  • 在根据标准检查行的存在时使用EXISTS，因为它会“短路”（在找到至少一个匹配项时停止处理）。

WHERE子句

  • 尽量避免使用LIKE。
  • 避免将大列表传入IN()语句——相反，考虑使用JOIN条件或调整逻辑为排除为基础。
  • 避免在WHERE子句中调用函数。

GROUP BY和GROUPING SET

参见深入探讨Postgres 9.5 - 新的Group By选项用于聚合 ↗。

UNION与UNION ALL

UNION将消除重复记录；这需要对相关表进行排序。

我们强烈建议避免使用UNION，因为其请求相关的成本。应该很少有必要使用它的情况，如果需要，应该在从Slate查询之前在变换层中进行。

UNION ALL不会消除重复项，并将有效地将一个表的行附加到另一个表。

参见常见错误：UNION VS. UNION ALL ↗。

索引

在以下情况下考虑添加索引：

  • 消除顺序扫描（seq scan），除非它是一个小表和/或查询正在提取超过5%的行。
  • 如果使用多列索引，请注意定义包含列的顺序。
  • 使用在频繁使用的列上具有高度选择性的索引。

识别缺失的索引

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  relname,
  seq_scan - idx_scan AS too_much_seq, -- 计算顺序扫描次数减去索引扫描次数，得到顺序扫描过多的次数
  CASE
    WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'missing index?' -- 如果顺序扫描次数大于索引扫描次数，可能缺少索引
    ELSE 'OK' -- 否则表示索引使用正常
  END,
  pg_relation_size(relname::regclass) AS rel_size, -- 获取关系（表）的大小
  seq_scan, -- 顺序扫描次数
  idx_scan -- 索引扫描次数
FROM pg_stat_all_tables
WHERE schemaname = 'public' -- 仅查询公共模式下的表
AND pg_relation_size(relname::regclass) > 80000 -- 仅查询表大小大于80000字节的表
ORDER BY too_much_seq DESC; -- 按顺序扫描过多的次数降序排列

识别未使用的索引

Copied!
1
2
3
4
5
6
7
SELECT
  indexrelid::regclass as index, -- 索引名称
  relid::regclass as table, -- 表名称
  'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement -- 生成删除索引的SQL语句
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND indisunique is false; -- 查找没有被扫描过且非唯一的索引

此SQL查询用于查找在PostgreSQL数据库中那些从未被扫描过且不是唯一的索引，并生成相应的删除索引的SQL语句。

仅索引扫描（覆盖索引）

阅读更多关于仅索引扫描的正确使用和好处 ↗。