改进 PostgreSQL 查询

解释查询计划

优化规则

感兴趣的设置

索引

调音技巧

不久前，我评论说我的一大收获是将一个执行时间超过一小时的查询优化到仅需 30 秒。事实上，对于客户来说，这个查询的执行时间从近 2 小时缩短到了大约 2 分钟，这其中还包含了数百万行数据的传输。过去几个月，我还优化了不少其他查询。

本文主要讨论报表或商业智能 (BI) 查询。这些查询通常规模庞大/复杂，需要推理大量数据，并且通常需要很长时间才能执行。“很长”指的是几十秒，甚至几分钟或几小时。

我将在本文中解释的一些内容也适用于更标准的应用程序查询，例如搜索查询。但我对此并不确定。这就引出了最重要的一步。

解释查询计划

任何严谨的 DBMS 都有一个选项可以获取有关 DBMS 如何执行查询的信息。这就是查询计划。每次更改任何内容时，都应该请求查询计划来查看其影响。更改包括对 PostgreSQL 服务器设置的更改以及服务器的升级。优化后的查询性能很可能会下降。

在 PostgreSQL 中，可以使用EXPLAIN命令检索查询计划。只需在查询前面加上这个命令，它就会生成一大堆输出。

该命令有多种选项EXPLAIN。我通常使用它们EXPLAIN (VERBOSE)来获取最重要的信息。如果同时包含该ANALYZE选项，PostgreSQL 还会执行查询并返回实际的执行信息，而不仅仅是执行计划。但是，如果查询执行时间长达半小时，那么此选项就没什么用了。ANALYZE您可能还想包含BUFFERS获取共享缓冲区使用情况信息的选项。

您可能需要的另一个选项是FORMAT选项。选择哪种格式取决于您用于可视化/分析查询计划的工具。默认输出相当易于阅读。但是，当查询非常复杂时，跟踪嵌套将变得困难。

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)
(3 rows)

输出格式包括JSON、、和默认的XML。YAMLTEXT

查询计划可视化

有很多工具可以使查询计划变得更易于使用。

注意：尽管这些可视化确实很有帮助，但本文将包含纯文本输出，因为大多数细节都隐藏在交互式使用之后。

pgAdmin

PostgreSQL 的标准管理工具pgAdmin内置了可视化功能。只需使用 explain 操作执行查询即可获得可视化结果。

各个查询部分之间的线条会根据该步骤的成本而变粗。（注意：请将分析选项设置为包含成本。）这样一来，就能轻松找到最大成本的来源。但请注意，线条的粗细与查询的总成本无关。因此，成本为 1,000,000 的部分与成本为 10,000 的部分具有相同的粗细。

将鼠标悬停在各个节点上将显示一些额外的详细信息。

depesz 的解释工具

Depesz有一个在线工具，您可以在其中粘贴TEXT解释的输出，并将其呈现在更易于浏览的 HTML 表中。

它会突出显示查询中最昂贵的部分。一旦开始优化查询，您就可以继续添加新的查询计划来进行比较。

Postgres EXPLAIN 可视化工具 (pev)

Pev是另一个可视化查询计划的在线工具。它使用 JSON 输出格式，并且更倾向于使用此ANALYZE选项。

它会产生非常漂亮的输出，并提示查询中哪些部分最有问题。

当您有真正复杂的查询时，可视化可能会变得更加难以导航。

请务必阅读博客文章以获取有关该工具的更多解释。

我喜欢计划顺利完成

一旦有了查询计划，就可以检查它以寻找可能的改进点。

您首先应该关注的是成本最高的最低层节点。这可能是您获益最多的地方。计划中的每个条目都包含一个成本范围：cost=0.00..23.92。第一个值可以被视为乐观成本，第二个值可以被视为悲观成本，或者最小成本和最大成本。这些数字没有合理的单位，只需将它们视为低值即好，高值则不好。优化查询时，获取初始成本，然后尝试降低这两个值。

通常，开销最大的部分是大型表的顺序表扫描（Seq Scan在查询计划中）。目标是尽量避免这些操作。但请注意，PostgreSQL 很乐意选择对小型表执行顺序表扫描，而不是使用索引。这些操作的开销通常很低，所以不要简单地试图避免所有顺序表扫描。

如前所述，每次进行更改时都必须生成新的查询计划。成本可能会转移到计划的其他部分，实际上并没有什么不同。或者，新计划的性能可能会更差。

在文档中可以找到有关输出的更详细的解释。

优化规则

规则 1：不要

规则二：先不要

——迈克尔·A·杰克逊

在优化查询之前，请先尝试一下。然后尝试不同的参数。有时甚至可能还不需要优化。只有当性能不可接受时，才尝试改进，直到性能可以接受为止。

请务必使用不同的范围测试查询，以了解其扩展性。我通常使用N、10N和100N进行测试。其中N是预期的标称范围，10N是最坏情况。我总是会包含一个100N，这是您意料之外的范围，但实际发生的时间会比您希望的更快。它只是为了让您了解查询的扩展性。

感兴趣的设置

PostgreSQL 有很多设置可以尝试，这些设置可能会带来很大的不同。尤其是资源消耗和查询计划的设置比较有趣。

开箱即用的 PostgreSQL 配置非常安全，就好像它在只有 1GiB RAM 的机器上运行一样。

我将在这里重点介绍一些有趣的设置，这些设置并不太危险，可以随意修改。但请不要完全依赖本文。请务必阅读官方文档，并确保在修改这些设置之前理解其含义。

资源消耗

共享缓冲区

您要调整的第一个设置是shared_buffers。这是不同 PostgreSQL 连接可用于共享数据的内存量。数据（例如从分布式系统读取的索引）建议将其设置为系统 RAM 的 25% 到 40%。一位 PostgreSQL 专家告诉我，在 9.x 系列中，分配给共享缓冲区超过 8GiB 通常不会产生显著影响。

与其他各种数据库系统不同，PostgreSQL 很大程度上依赖于操作系统的功能，例如内存中的磁盘缓存。因此，如果您的服务器拥有 64GiB 的 RAM，并且共享缓冲区设置为“仅”8GiB，那么很多索引可能不在 PostgreSQL 的共享缓存中，但它们在磁盘缓存中，因此可以快速访问。

临时缓冲区

该temp_buffers设置控制在将临时表刷新到磁盘之前可以使用多少内存。此设置可以在运行时更改，但只能在会话开始时更改。

工作备忘录

另一个有趣的设置是work_mem。它会影响在将数据刷新到磁盘之前，用于排序和处理哈希表的内存大小。增加该值对于包含大量排序和子结果连接操作的复杂查询有很大的积极作用。对于类似 BI 的查询，增加该值是值得的。

工作内存的分配是按操作分配的，在单个会话中可能会有多个操作。（尤其是在较新的 PostgreSQL 版本中，查询可以并发执行）。这意味着您应该注意不要将其设置得太大，否则可能会遇到内存不足的问题。

这是一个可以在当前会话运行时更改的设置。

查询计划设置

这些设置会影响查询计划。弄乱这些设置很容易破坏服务器的性能。所以在大多数情况下，你需要非常小心。

有效缓存大小

它effective_cache_size告诉 PostgreSQL 系统内存中可以缓存多少数据。这包括 PostgreSQL 管理的缓存，也包括操作系统保存的缓存（例如磁盘缓存）。

此设置对内存分配没有影响。它只是向查询规划器指示数据在内存中的可能性。

在 Linux 系统上，如果您有系统监控功能，可以跟踪特定时间段内用于缓存的内存量，您就可以了解系统缓存了多少数据。它/proc/meminfo会告诉您当前使用了多少数据，但您真正想知道的是一周/一个月的时间范围内的数据。

顺序/随机页面成本

和设置决定了从磁盘读取数据的成本。默认设置假设磁盘是旋转磁盘，其随机寻道时间远高于顺序访问seq_page_cost。random_page_cost

如果您使用的是全闪存存储，其随机寻道时间与顺序访问非常接近。在这种情况下，将随机成本降低到更接近顺序成本是合理的。某种程度上，这也适用于基于旋转磁盘的 SAN 存储。

索引

提高查询性能的关键在于索引。如果没有索引，数据库只能进行顺序表扫描。但索引并非免费。它们需要存储空间和维护时间。更改表中的数据意味着受影响的索引也需要更新。更复杂/更高级的索引显然需要更多时间。

最糟糕的索引是未使用的索引。因此，只在需要时创建索引。并删除未使用的索引。

提醒：如前所述，对于小表，查询规划器可能会选择跳过使用索引。

多列和排序索引

覆盖多列的索引通常比为单列创建多个索引更好。即使索引中的某些列在查询中未使用，PostgreSQL 也会考虑使用多列索引。

例如，如果我们有以下索引：

create index order_dates on orders(creation_date, completion_date);

它可用于使用creation_date和completion_date列的任意组合的查询。索引数量较少的好处是它们可以在共享内存和缓存中驻留更长时间。

PostgreSQL 中默认的索引存储类型是 B 树。对于 B 树来说，无论查找最大值还是最小值，其复杂度都是一样的。但是，如果大多数查询都按索引列的降序排序，则查询执行过程中会额外增加一个步骤来对结果进行排序。

可以通过向索引添加订单来省去此步骤。导入订单部分有两个：

数据排序
空值

您可以独立控制每列的顺序。默认情况下，每列的顺序为ASC NULLS LAST，即按升序排列，末尾为空值。

如果知道哪些订单尚未完成很重要，那么最好先放置空值。

create index order_dates on orders(creation_date, completion_date nulls first);

如果您还对最近完成的订单感兴趣，那么最好将completion_date也设为降序。

create index order_dates on orders(creation_date, completion_date desc);
-- is equivalent to
create index order_dates on orders(creation_date, completion_date desc nulls first);

再次强调，索引排序仅当查询包含order by针对这些列的子句时才会影响性能。它不会加快索引搜索的速度。

部分索引

索引也会占用空间。过大的索引不利于缓存。创建较小的索引可以显著提升性能。创建较小的索引可以通过部分索引来实现。

就拿前面的例子来说order_dates。如果我们经常需要查询尚未完成的订单，并且订单最终完成的情况也很常见。那么可以创建一个完成日期为空的小索引。

create index open_orders on orders(completion_date) where completion_date is null;

与列上的完整索引相比，此索引非常小completion_date。由于它很小，而且可能经常使用，因此会被大量缓存。当它不被缓存时，只需要进行少量昂贵的磁盘读取操作。

表达式索引

查询经常在 where 子句中包含表达式。例如，不区分大小写的搜索：

select orders where lower(city) = 'devtown';

在列上创建普通索引city没有任何好处。因为该索引会包含Devtown、devtown等单独的条目。为了加快此查询速度，您可以在表达式上创建索引：

create index lc_city on orders(lower(city));

现在上述查询可以使用该索引来快速找到适当的行。

仅索引扫描

仅索引扫描是指索引包含所有需要的数据。通常，索引会生成一个指向实际数据行的指针，然后需要提取该指针。

但是，如果你创建一个包含查询中选定的所有数据的索引，那么数据库就不必提取该行。你可以将其视为单表上的快速视图。

您不必将额外的字段作为索引数据的一部分。您可以将它们作为附加数据包含在索引中。

create unique index product_ean on product(ean) include(name)

这将在产品 ID 上创建一个唯一索引，并将该name列包含在索引中。以下查询将能够执行仅索引扫描：

select product.ean, product.name, order_line.quantity
from order_line
join product on product.ean = order_line.product_ean

使用的唯一索引的一个明显好处INCLUDE(...)是，你可以向唯一索引添加其他列，而无需使其成为唯一性约束的一部分。但请注意，这将消耗存储空间。

调音技巧

当您开始调整查询时，强烈建议清理并分析或至少分析您要计划的表。

这两个操作都可以在不锁定表的情况下完成。但显然它们会影响正在运行的操作的性能。

由于查询优化器会受到收集到的统计信息的影响，因此保持统计信息相对新鲜非常重要。PostgreSQL 会在特定时刻自动清理并自动分析表。这种情况很可能发生在您的调优过程中。这时，您可能看起来改进了查询，但这是由于使用了更新的统计信息而产生了不同的查询计划。

您可以通过检查pg_stat_all_tables 视图来检查最近对表进行分析或清理的情况。

但现在我最近应用了一些技巧来改进一些查询。

规模太大，无法加入

第一个技巧看起来很奇怪。你可能以为查询规划器默认会这么做。但显然在我应用它的服务器上并非如此。也许在新版本中情况有所改变，但对于 PostgreSQL 来说，这个技巧将一个需要几个小时才能执行的查询变成了只需几秒钟就能完成的查询。

此查询的目标是检索特定时间段内更改的订单的详细信息。数据库结构大致如下。

这些表包含很多行数据：

桌子	行	尺寸
订单	31,889,100	15 GiB
订单行	73,699,400	70 GiB
订单行详情	2,320,400,000	265 GiB

尝试#1：简单选择

获取过去 6 小时内更改的订单的所有详细信息的最直接的查询如下：

select d.* 
from orders o
join order_line l on l.order_id = o.id
join order_line_detail d on d.line_id = l.id
where o.last_update > now() - interval '6 hours';

此查询执行耗时数小时。查询计划显示了原因。注意：由于执行耗时很长，查询计划的执行没有实际计时。

Hash Join  (cost=10717890.78..77356068.23 rows=823481 width=28)
  Hash Cond: (d.line_id = l.id)
  ->  Seq Scan on order_line_detail d  (cost=0.00..57928447.92 rows=2320398592 width=28)
  ->  Hash  (cost=10717563.84..10717563.84 rows=26155 width=4)
        ->  Hash Join  (cost=42202.77..10717563.84 rows=26155 width=4)
              Hash Cond: (l.order_systemid = o.id)
              ->  Seq Scan on order_line l  (cost=0.00..9938105.76 rows=73699376 width=8)
              ->  Hash  (cost=42061.31..42061.31 rows=11317 width=4)
                    ->  Index Scan using order_last_update_idx on orders o  (cost=0.57..42061.31 rows=11317 width=4)
                          Index Cond: (last_update > (now() - '06:00:00'::interval))

它根据索引选择订单。但之后，最大的两个表会进行顺序扫描。你可能会认为外键上的索引缺失了。但事实并非如此。

尝试 #2：子选择

因此，让我们尝试其他方法，使用子选择。

select d.*
from order_line_detail d
where d.line_id in (
    select l.id
    from order_line l
    where l.order_id in (
        select o.id
        from orders o
        where o.last_update > now() - interval '6 hours'
    )
);

计划发生了变化，看起来更复杂，但仍然有两个非常昂贵的顺序表扫描。

Hash Join  (cost=11221517.77..94488998.34 rows=2320398592 width=28)
  Hash Cond: (d.line_id = l.id)
  ->  Seq Scan on order_line_detail d  (cost=0.00..57928447.92 rows=2320398592 width=28)
  ->  Hash  (cost=11136752.37..11136752.37 rows=6781232 width=4)
        ->  Unique  (cost=11102846.21..11136752.37 rows=6781232 width=4)
              ->  Sort  (cost=11102846.21..11119799.29 rows=6781232 width=4)
                    Sort Key: l.id
                    ->  Hash Semi Join  (cost=42195.43..10333409.79 rows=6781232 width=4)
                          Hash Cond: (l.order_systemid = o.id)
                          ->  Seq Scan on order_line l  (cost=0.00..9938105.76 rows=73699376 width=8)
                          ->  Hash  (cost=42053.99..42053.99 rows=11315 width=4)
                                ->  Index Scan using order_last_update_idx on orders o  (cost=0.57..42053.99 rows=11315 width=4)
                                      Index Cond: (last_update > (now() - '06:00:00'::interval))

仍然不太实用。

尝试#3：通用表表达式

PostgreSQL 支持所谓的通用表表达式(CTE)。虽然像这样的简单查询对于 CTE 的典型用例来说没什么用，但尝试一下也无妨。

新的查询变为：

with cte_order as (
    select o.id 
    from orders o
    where o.last_update > now() - interval '6 hours'
),
cte_line as (
    select l.id
    from order_line l
    where l.order_id in (
        select * from cte_order
    )
)
select d.*
from order_line_details d
where d.id in (select * from cte_line)

它的功能看起来相当复杂。基本上每个子选择都被移到了 CTE 中。

那么让我们仔细看看这个查询。（注意：按实际时间执行，因为速度非常快）

Nested Loop  (cost=1225650.89..19184158.78 rows=1160199296 width=28) (actual time=471.251..1171.805 rows=1500960 loops=1)
  CTE cte_order
    ->  Index Scan using co_last_update_idx on orders o  (cost=0.57..42009.62 rows=11303 width=4) (actual time=0.262..51.518 rows=8777 loops=1)
          Index Cond: (last_update > (now() - '06:00:00'::interval))
  CTE cte_line
    ->  Nested Loop  (cost=254.88..354522.71 rows=36849688 width=4) (actual time=56.620..443.167 rows=35141 loops=1)
          ->  HashAggregate  (cost=254.32..256.32 rows=200 width=4) (actual time=56.567..61.080 rows=8777 loops=1)
                ->  CTE Scan on cte_order  (cost=0.00..226.06 rows=11303 width=4) (actual time=0.265..53.986 rows=8777 loops=1)
          ->  Index Scan using order_line_order_id_idx on order_line l  (cost=0.57..1765.34 rows=599 width=8) (actual time=0.020..0.042 rows=4 loops=8777)
                Index Cond: (order_id = cte_order.id)
  ->  HashAggregate  (cost=829117.98..829119.98 rows=200 width=4) (actual time=471.222..489.199 rows=35141 loops=1)
        ->  CTE Scan on cte_line  (cost=0.00..736993.76 rows=36849688 width=4) (actual time=56.623..454.029 rows=35141 loops=1)
  ->  Index Scan using order_line_detail_entity_id_idx on order_line_detail d  (cost=0.58..89389.59 rows=40294 width=28) (actual time=0.006..0.014 rows=43 loops=35141)
        Index Cond: (line_id = cte_line.id)

就是这样。没有顺序表扫描，只使用了索引。查询花了 1 秒多一点的时间才找到 1,500,960 行数据。这部分的成本估算严重偏差。这就是为什么你不应该只依赖成本估算，也应该尝试一下EXPLAIN其他ANALYZE选项。

尽管所有三个查询都会产生完全相同的输出，但向查询规划器提供不同的构造可能会产生完全不同的结果。

枢轴点

接下来的这个技巧花费了我很多时间。首先，我必须把这个长达 350 行的报告查询语句正确地格式化成可读的内容。

部分查询看起来是这样的。

with order_price_transport as (
    select orders.id, price_parts.code, price_parts.status, sum(price_parts.amount)
    from orders
    join price_parts on price_parts.order_id = orders.id
    where price_parts.code in ('FUEL', FREIGHT)
    group by orders.id, price_parts.code, price_parts.status
),
-- ...
order_price_other as (
    select orders.id, price_parts.code, price_parts.status, sum(price_parts.amount)
    from orders
    join price_parts on price_parts.order_id = orders.id
    where price_parts.code not in ('FUEL', 'FREIGHT', ...)
    group by orders.id, price_parts.code, price_parts.status
),
-- ... a whole bunch bunch of other CTEs
select
    orders.id,
    -- ...
    est_trans_price.amount,
    act_trans_price.amount,
    est_other_price.amount,
    act_other_price.amount,
    -- ...
from orders
join order_price_transport as est_trans_price on est_trans_price.id = orders.id and est_trans_price.status = 'estimated'
join order_price_transport as act_trans_price on act_trans_price.id = orders.id and act_trans_price.status = 'actual'
-- ...
join order_price_other as est_other_price on est_other_price.id = orders.id and est_other_price.status = 'estimated'
join order_price_other as act_other_price on act_trans_price.id = orders.id and act_other_price.status = 'actual'
-- ...
where orders.creation_date between $P{START_DATE} and $P{END_DATE}
and orders.organization_id in ($P!{ORGANIZATION_LIST})
and orders.status_id in (123, 456)

我放的地方-- ...内容更多。简单来说，这份报告运行得不太好。它包含很多类似的通用表表达式 (CTE)。有些甚至被连接了多次。

CTE 的结果（以及视图）不会被缓存。它们每次使用时都会执行。因此，仅从上面显示的查询来看，该price_parts表已经被查询了 4 次。

您会注意到，主查询包含一些针对orders表的 where 子句。这些子句在 CTE 中没有出现，因为 CTE 基本上使用相同的行顺序。将这些 where 子句复制到每个 CTE 已经带来了相当大的改进。但是，查询price_parts和orders表的次数仍然远远超出了需要。

该price_parts表格可以包含多行订单价格部分。每行包含代码、金额和状态（是预估价还是实际价格）。此报告的一部分功能是针对选定的订单生成特定价格组的概览。本质上，它的目的是将表格中的行转换price_parts为结果报告中的列。

没必要在最后一刻才对这些数据进行透视。你也可以在 CTE 中这样做。所以我就是这么做的。我把所有 CTE 的推理合并到同一个表上，不同之处主要在于 where 子句的内容。这样就把上面的查询转换成了下面这个（可能看起来更复杂的查询）。

with order_prices as (
    select
        id,
        sum(case when status = 'estimated' and code = 'trans' then amount else 0 end) as est_trans_price,
        sum(case when status = 'actual' and code = 'trans' then amount else 0 end) as act_trans_price,
        -- ...
        sum(case when status = 'estimated' and code = 'other' then amount else 0 end) as est_other_price,
        sum(case when status = 'actual' and code = 'other' then amount else 0 end) as act_other_price,
    from (
        select
            orders.id,
            case 
                when price_part.code in ('FUEL', FREIGHT) then 'trans'
                -- ...
                else 'other'
            end as code,
            price_part.status,
            sum (price_part.amount) as amount
        from orders
        join price_part on price_part.order_id = orders.id
        where orders.creation_date between $P{START_DATE} and $P{END_DATE}
            and orders.organization_id in ($P!{ORGANIZATION_LIST})
            and orders.status_id in (123, 456)
        group by orders.id, price_parts.code, price_parts.status
    ) as data
    group by id
)
-- ...
select 
    orders.id
    -- ...
    op.est_trans_price,
    op.act_trans_price,
    op.est_other_price,
    op.act_other_price
from orders
join order_prices op on op.id = orders.id
-- ...
where orders.creation_date between $P{START_DATE} and $P{END_DATE}
and orders.organization_id in ($P!{ORGANIZATION_LIST})
and orders.status_id in (123, 456)

在 CTE最内层的查询中，order_prices所有相关数据都会从price_part表中收集，并在适用的orders行上复制筛选器。这些行会根据订单 ID、价格部分代码和状态进行分组，并对金额进行求和。它不会返回行的实际代码price_part，而是将其转换为一个新组，以便在数据透视过程中使用。

然后，此选择查询将用作 CTE 外部选择的表order_prices。这就是数据透视表的执行位置。所有数据均已选择，并仅根据 ID 进行分组。对于每个代码和状态，使用条件求和聚合创建一列。

虽然最终的查询并没有小很多，因为新的 CTE 占用的行数几乎相同（仅少了 50 行）。但它确实将 CTE 的连接数从 21 个减少到了 5 个，因为现在只有 5 个 CTE。不仅连接数减少了，查询每个部分需要结转的数据量也减少了，因此执行查询所需的内存也减少了。

通过这些改变，报告执行时间从 30 分钟缩短到仅 1 分钟。

无限

因此，这是一个查询运行良好的情况，直到我们更改服务器配置以更好地利用可用的系统内存。

查询非常简单：

select *
from orders 
where organization_id in (7000)
and status_id in (2, 28)
and order_type_id in (146630533)
and system_entry_date < '2019-03-24 23:30:03.524'
order by system_entry_date asc
limit 25

这是查询计划（没有实际时间，因为它花费的时间太长）。

Limit  (cost=0.56..23639.89 rows=25 width=2972)
  ->  Index Scan using idx_order_entry_date on orders  (cost=0.56..10885436.12 rows=11512 width=2972)
        Index Cond: (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone)
        Filter: ((status_id = ANY ('{2,28}'::integer[])) AND (organization_id = 7000) AND (order_type_id = 146630533))

乍一看还不错。只是它使用了索引order_entry_date_idx，而这个索引并不是最佳选择。还有一个更具体的索引，其中还包含一些其他列。这个查询计划基本上是在执行顺序表扫描。索引仍然会选择大多数行，但后续的过滤操作会减少行数。

如果我放弃，limit 25查询计划将发生巨大变化：

Sort  (cost=36349.54..36378.32 rows=11512 width=2972) (actual time=0.163..0.163 rows=0 loops=1)
  Sort Key: system_entry_date
  Sort Method: quicksort  Memory: 25kB
  ->  Index Scan using idx_order_statustype on orders  (cost=0.56..35573.01 rows=11512 width=2972) (actual time=0.136..0.136 rows=0 loops=1)
        Index Cond: ((status_id = ANY ('{2,28}'::integer[])) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
        Filter: (organization_id = 7000)

这次使用了正确的索引。尽管预估成本较高，但实际时间却好得多。

如果我在查询中删除order by，它也会使用正确的索引。请注意，由于 B 树索引的性质，结果实际上会按升序返回。

Limit  (cost=0.56..77.82 rows=25 width=2972) (actual time=0.142..0.142 rows=0 loops=1)
  ->  Index Scan using idx_order_statustype on orders  (cost=0.56..35573.01 rows=11512 width=2972) (actual time=0.135..0.135 rows=0 loops=1)
        Index Cond: ((status_id = ANY ('{2,28}'::integer[])) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
        Filter: (organization_id = 7000)

如果我再次将改为，in (2, 28)则会使用正确的索引。无论你执行或in (2)都没关系。foo in (2, 28)foo = 2 or foo = 28

Limit  (cost=0.56..63.64 rows=19 width=2972) (actual time=0.105..0.105 rows=0 loops=1)
  ->  Index Scan using idx_order_statustype on orders  (cost=0.56..63.64 rows=19 width=2972) (actual time=0.103..0.103 rows=0 loops=1)
        Index Cond: ((status_id = 2) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
        Filter: (organization_id = 7000)

即使将限制增加到 40 也会产生更好的结果

Limit  (cost=35936.90..35937.00 rows=40 width=2972) (actual time=3.078..3.078 rows=0 loops=1)
  ->  Sort  (cost=35936.90..35965.68 rows=11512 width=2972) (actual time=3.076..3.076 rows=0 loops=1)
        Sort Key: system_entry_date
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using idx_order_statustype on orders  (cost=0.56..35573.01 rows=11512 width=2972) (actual time=3.066..3.066 rows=0 loops=1)
              Index Cond: ((status_id = ANY ('{2,28}'::integer[])) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
              Filter: (organization_id = 7000)

查询规划器在使用idx_order_entry_date索引时显然过于乐观。如果你查看第一个查询计划，你会发现使用错误索引的悲观成本实际上相当高。但限制后成本大幅降低。它从cost=0.56..10885436.12到cost=0.56..23639.89。

经过一番搜索后，这似乎是PostgreSQL 中一个相当复杂的错误。

在这个特殊情况下，有两个合适的选项可用于改进查询：

更高的限制；它只需要稍微高一点，这不会影响其余的过程。
无需订购；这无论如何都不是至关重要的，并且返回的订单已经足够好了。

在某些数据库系统中，你会使用索引提示来解决这个问题。但PostgreSQL 开发人员并没有考虑引入索引提示。

这里的技巧是，有时放宽一些限制可能会带来更好的性能。这看起来像是解决 bug 的变通方法，事实也确实如此，但有时你需要这些技巧。这个问题在 9.5 中已经发现，在新版本中很可能不再存在。

指数疯狂上涨

当通配符位于末尾时，B 树索引（默认类型）也可以用于通配符搜索：col LIKE 'foo%'。

不过，还有一个小问题。如果使用的排序规则不是“C”，那么它就无法正常工作。

create index city_name_idx on postal_location(city_name);

explain (analyze) select * from postal_location 
where city_name like 'LONDON%';

Seq Scan on postal_location  (cost=0.00..69965.75 rows=4392 width=75) (actual time=114.905..324.757 rows=4346 loops=1)
  Filter: ((city_name)::text ~~ 'LONDON%'::text)
  Rows Removed by Filter: 1974114

您需要明确定义索引的操作符类来使用模式变体。

create index city_name_idx on postal_location(city_name varchar_pattern_ops);

explain (analyze) select * from postal_location 
where city_name like 'LONDON%';

Bitmap Heap Scan on postal_location  (cost=111.74..12683.84 rows=4392 width=75) (actual time=0.288..0.812 rows=4346 loops=1)
  Filter: ((city_name)::text ~~ 'LONDON%'::text)
  ->  Bitmap Index Scan on city_name_idx  (cost=0.00..110.64 rows=4221 width=0) (actual time=0.277..0.277 rows=4346 loops=1)
        Index Cond: (((city_name)::text ~>=~ 'LONDON'::text) AND ((city_name)::text ~<~ 'LONDOO'::text))

请注意，如果希望涉及普通 <、<=、> 或 >= 比较的查询使用索引，则还应该使用默认操作符类创建索引。此类查询不能使用 xxx_pattern_ops 操作符类。

领先的通配符

如果通配符位于开头，则此方法将不起作用，您需要重新对表进行顺序扫描。

explain (analyze) select * from postal_location 
where city_name like '%LONDON';

Seq Scan on postal_location  (cost=0.00..69965.75 rows=4392 width=75) (actual time=118.001..361.855 rows=4316 loops=1)
  Filter: ((city_name)::text ~~ '%LONDON'::text)
  Rows Removed by Filter: 1974144

但是，您可以使用表达式创建索引来索引反转的值。

create index city_name_rev_idx on postal_location(reverse(city_name) varchar_pattern_ops);

explain (analyze) select * from postal_location 
where reverse(city_name) like reverse('%LONDON');

Bitmap Heap Scan on postal_location  (cost=257.83..24199.06 rows=9892 width=75) (actual time=0.365..1.133 rows=4316 loops=1)
  Filter: (reverse((city_name)::text) ~~ 'NODNOL%'::text)
  ->  Bitmap Index Scan on city_name_rev_idx  (cost=0.00..255.35 rows=9892 width=0) (actual time=0.351..0.351 rows=4316 loops=1)
        Index Cond: ((reverse((city_name)::text) ~>=~ 'NODNOL'::text) AND (reverse((city_name)::text) ~<~ 'NODNOM'::text))

成本估算比普通索引高很多，但仍然比顺序表扫描低很多。更重要的是，实际时间几乎和普通索引相当，并且明显优于顺序表扫描。

反向通配符索引在人们通常输入最后几个字符而不是前几个字符的地方特别有用。例如订单号的情况。

可读代码

最后一个技巧应该不费脑子。但正如我之前提到的，我必须先重新格式化查询，然后才能开始优化它。

我处理过另一个大型报表查询，它的格式非常糟糕。为了弄清楚查询的实际功能，我尝试格式化查询，结果发现了一些可能是拼写错误的地方。在其中一个子查询中使用了错误的表别名。当我将别名更正为我认为正确的别名后，查询速度非常快，而且输出结果显然也正确无误。

如果原作者正确格式化了 SQL，他们可能很容易发现这个拼写错误。

直到今天，我都不知道这个查询实际上应该做什么。但是输出是正确的，查询速度很快，我在 30 分钟内就解决了这个问题。

文章来源：https://dev.to/elmuerte/improving-postgresql-queries-4pc1

改进 PostgreSQL 查询 解释查询计划 优化规则 感兴趣的设置 索引调整技巧

改进 PostgreSQL 查询

解释查询计划

优化规则

感兴趣的设置

索引

调音技巧

解释查询计划

查询计划可视化

pgAdmin

depesz 的解释工具

Postgres EXPLAIN 可视化工具 (pev)

我喜欢计划顺利完成

优化规则

感兴趣的设置

资源消耗

共享缓冲区

临时缓冲区

工作备忘录

查询计划设置

有效缓存大小

顺序/随机页面成本

索引

多列和排序索引

部分索引

表达式索引

仅索引扫描

调音技巧

规模太大，无法加入

尝试#1：简单选择

尝试 #2：子选择

尝试#3：通用表表达式

枢轴点

无限

指数疯狂上涨

领先的通配符

可读代码

改进 PostgreSQL 查询解释查询计划优化规则感兴趣的设置索引调整技巧