POSTGRESQL查询优化器如何工作

原创 Hans-JürgenSchönig 2021-03-26

1454

作者，Hans-JürgenSchönig，从90年代开始就拥有PostgreSQL的经验。他是CYBERTEC的首席执行官兼技术主管，CYBERTEC是该领域的市场领导者之一，自2000年以来已为全球无数客户提供服务。

就像任何高级关系数据库一样，PostgreSQL使用基于成本的查询优化器，该优化器试图将您的SQL查询转换为可以在尽可能短的时间内执行的高效查询。对于许多人来说，优化器本身的运行仍然是一个谜，因此我们决定让用户了解幕后的真实情况。

因此，让我们浏览一下PostgreSQL优化器，并概述该优化器用来加速查询的一些最重要的技术。请注意，此处列出的技术绝不是完整的。还有很多事情要做，但是有必要看一下最基本的东西，以便对过程有一个很好的了解。

PostgreSQL常量折叠

不断折叠是描述起来更简单，更容易的事情之一。但是，该过程非常重要。让我们看看在此过程中会发生什么：

demo=# SELECT *
           FROM generate_series(1, 10) AS x
           WHERE x = 7 + 1;
x
---
8
(1 row)
 
demo=# explain SELECT *
           FROM generate_series(1, 10) AS x
           WHERE x = 7 + 1;
                            QUERY PLAN
----------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..0.13 rows=1 width=4)
    Filter: (x = 8)
(2 rows)
复制

您可以在这里看到的是，我们向查询中添加了一个过滤器：x = 7 + 1。系统要做的是“折叠”常数，而是这样做“x = 8”。为什么这么重要？如果“ x”被索引（假设它是一个表），我们可以轻松地在索引中查找8。

重要的是要提到的是：

demo=# explain SELECT *
           FROM generate_series(1, 10) AS x
           WHERE x - 1 = 7 ;
                            QUERY PLAN
----------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..0.15 rows=1 width=4)
    Filter: ((x - 1) = 7)
(2 rows)
复制

我们在这里看到的是，在这种情况下PostgreSQL不会将表达式转换为“ x = 8”。这就是为什么您应该尝试确保过滤器在右侧，而不在您可能要索引的列上。

PostgreSQL查询优化器：函数内联

一种更重要的技术是函数内联的思想。目的是尽可能减少函数调用，从而加快查询速度。让我们创建一个函数来计算对数：

demo=# CREATE OR REPLACE FUNCTION ld(int)
           RETURNS numeric AS
$$
      SELECT log(2, $1);
$$ LANGUAGE 'sql';
CREATE FUNCTION
 
demo=# SELECT ld(1024);
         ld
---------------------
10.0000000000000000
(1 row)
复制

2 ^ 10 =1024。看起来不错。现在，让我们看看实际查询中会发生什么：

demo=# explain SELECT *
           FROM generate_series(1, 10) AS x
           WHERE ld(x) = 1000;
                            QUERY PLAN
----------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..0.18 rows=1 width=4)
   Filter: (log('2'::numeric, (x)::numeric) = '1000'::numeric)
(2 rows)
复制

有趣的地方可以在WHERE子句中找到。该ld功能已被基础log功能取代。请注意，这仅在SQL函数的情况下才可行。PL / pgSQL和其他存储过程语言是优化器的黑匣子，因此这些操作是否可行取决于所用语言的类型。

这是一个例子：

demo=# CREATE OR REPLACE FUNCTION pl_ld(int)
           RETURNS numeric AS
$$
     BEGIN
                 RETURN log(2, $1);
     END;
$$ LANGUAGE 'plpgsql';
CREATE FUNCTION
demo=# explain SELECT *
           FROM generate_series(1, 10) AS x
           WHERE pl_ld(x) = 1000;
                            QUERY PLAN
----------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..2.63 rows=1 width=4)
   Filter: (pl_ld(x) = '1000'::numeric)
(2 rows)
复制

在这种情况下，无法进行内联。虽然代码基本相同，但是编程语言确实有很大的不同。

功能稳定性：挥发性vs.稳定vs.免疫
功能稳定性的概念经常被忽略。创建函数时，如果将函数创建为VOLATILE（默认）STABLE，或创建，则会有所不同IMMUTABLE。它甚至可以产生很大的不同-特别是在使用索引的情况下。让我们创建一些样本数据并整理这些差异：

VOLATILE表示在给定相同输入参数的情况下，不能保证函数在相同事务内返回相同结果。换句话说，PostgreSQL优化器无法将函数视为常量，而必须为每一行执行该函数–如下面的示例所示：

demo=# CREATE TABLE t_date AS
           SELECT *
           FROM   generate_series('1900-01-01'::timestamptz,
                    '2021-12-31'::timestamptz, '1 minute') AS x;
SELECT 64164961
demo=# CREATE INDEX idx_date ON t_date (x);
CREATE INDEX
复制

自1900年1月以来，我们已经生成了6400万个条目的列表，每分钟包含1行，产生了6400万个条目。

让我们使用一个VOLATILE函数运行查询：

demo=# explain analyze SELECT *
           FROM t_date
           WHERE x = clock_timestamp();
                          QUERY PLAN 
-------------------------------------------------------------------
 Gather (cost=1000.00..685947.45 rows=1 width=8)
        (actual time=2656.961..2658.547 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  -> Parallel Seq Scan on t_date
         (cost=0.00..684947.35 rows=1 width=8)
         (actual time=2653.009..2653.009 rows=0 loops=3)
     Filter: (x = clock_timestamp())
     Rows Removed by Filter: 21388320
 Planning Time: 0.056 ms
 Execution Time: 2658.562 ms
(8 rows)
复制

在这种情况下，查询需要耗时2.6秒，并且会消耗大量资源。原因clock_timestamp()是VOLATILE。如果我们尝试使用STABLE函数做同样的事情怎么办？

demo=# explain analyze SELECT *
           FROM t_date
           WHERE x = now();
                        QUERY PLAN 
------------------------------------------------------------------
 Index Only Scan using idx_date on t_date
      (cost=0.57..4.59 rows=1 width=8)
      (actual time=0.014..0.015 rows=0 loops=1)
    Index Cond: (x = now())
    Heap Fetches: 0
 Planning Time: 0.060 ms
 Execution Time: 0.026 ms
(5 rows)
复制

该查询速度提高了数千倍，因为现在PostgreSQL可以将其转换为常量，从而使用索引。如果您想了解有关PostgreSQL中函数稳定性的更多信息，请参见这里的更多信息。

平等约束

我们列表中的下一个优化是相等约束的概念。PostgreSQL在这里试图做的是获得有关查询的隐式知识。首先，让我们创建一些示例数据：

demo=# CREATE TABLE t_demo AS
           SELECT x, x AS y
           FROM generate_series(1, 1000) AS x;
SELECT 1000
demo=# SELECT * FROM t_demo LIMIT 5;
x  | y
---+---
1  | 1
2  | 2
3  | 3
4  | 4
5  | 5
(5 rows)
复制

我们这里有1000行。我们将运行一个简单的查询：

demo=# explain SELECT *
               FROM t_demo
               WHERE x = y
                     AND y = 4;
                   QUERY PLAN
-------------------------------------------------------
 Seq Scan on t_demo (cost=0.00..20.00 rows=1 width=8)
   Filter: ((x = 4) AND (y = 4))
(2 rows)
复制

再次，魔术是执行计划。我们在这里看到的是PostgreSQL已经发现x和y恰好是4。这为重要的优化打开了大门：

demo=# CREATE INDEX idx_x ON t_demo (x);
CREATE INDEX
demo=# explain SELECT *
                FROM t_demo
                WHERE x = y
                      AND y = 4;
                           QUERY PLAN
--------------------------------------------------------------------
 Index Scan using idx_x on t_demo (cost=0.28..8.29 rows=1 width=8)
    Index Cond: (x = 4)
    Filter: (y = 4)
(3 rows)
复制

没有这种优化，绝对不可能使用我们刚刚创建的索引。为了优化查询，PostgreSQL将自动确定我们可以在此处使用索引。

索引很重要！

查看内联和子选择展平

在谈论PostgreSQL优化器和查询优化时，无法忽略视图和子选择处理。让我们创建一个简单的视图并对其进行查询：

demo=# CREATE VIEW v1 AS
           SELECT *
           FROM   generate_series(1, 10) AS x;
CREATE VIEW
demo=# explain SELECT *
           FROM v1
           ORDER BY x DESC;
                QUERY PLAN
----------------------------------------------
 Sort (cost=0.27..0.29 rows=10 width=4)
    Sort Key: x.x DESC
    -> Function Scan on generate_series x
       (cost=0.00..0.10 rows=10 width=4)
(3 rows)
复制

当我们查看执行计划时，视图无处可见。原因是PostgreSQL已将视图内联为子选择并对其进行了展平。这是如何运作的？

SELECT *
          FROM v1
          ORDER BY x DESC;
复制

该查询变成…

SELECT *
          FROM (SELECT *
                FROM generate_series(1, 10) AS x
               ) AS v1
         ORDER BY x DESC;
复制

然后子选择被展平，这给我们留下了……

SELECT *
FROM   generate_series(1, 10) AS x
ORDER BY x DESC;
复制

PostgreSQL查询优化器中有一个变量可控制此行为：

demo=# SHOW from_collapse_limit;
 from_collapse_limit
---------------------
 8
(1 row)
复制

此参数的含义是，FROM子句中最多只能展平8个子选择。如果子选择超过8个，则将在不展平它们的情况下执行它们。在大多数实际用例中，这不是问题。仅当所使用的SQL语句非常复杂时才成为问题。有关联接和join_collapse_limit的更多信息，请参见我们的博客。

请记住，内联并非总是可能的。开发人员意识到这一点。

在PostgreSQL中优化联接

在大多数查询中都使用联接，因此，联接对于提高性能至关重要。现在，我们将集中讨论与联接有关的一些技术。

优化加入订单

列表上的下一个重要事项是PostgreSQL优化器处理联接顺序的方式。在PostgreSQL数据库中，联接不一定要按照最终用户提出的顺序进行-恰恰相反：查询优化器试图找出尽可能多的联接选项。
让我们创建一些样本数据并弄清楚它是如何工作的：

demo=# CREATE TABLE a AS
     SELECT x, x % 10 AS y
     FROM generate_series(1, 100000) AS x
     ORDER BY random();
SELECT 100000
demo=# CREATE TABLE b AS
     SELECT x, x % 10 AS y
     FROM generate_series(1, 1000000) AS x
     ORDER BY random();
SELECT 1000000
demo=# CREATE TABLE c AS
     SELECT x, x % 10 AS y
     FROM generate_series(1, 10000000) AS x
     ORDER BY random();
SELECT 10000000
复制

在接下来的步骤中，将创建几个索引：

demo=# CREATE INDEX a_x ON a(x);
CREATE INDEX
demo=# CREATE INDEX b_x ON b(x);
CREATE INDEX
demo=# CREATE INDEX c_x ON c(x);
CREATE INDEX
demo=# ANALYZE;
ANALYZE
复制

现在，我们有三个表。我们将查询它们，看看会发生什么：

demo=# explain SELECT *
               FROM  a, b, c
               WHERE c.x = a.x
                     AND a.x = b.x
                     AND a.x = 10;
                        QUERY PLAN
-------------------------------------------------------------------- 
Nested Loop (cost=1.15..25.23 rows=1 width=24)
  -> Nested Loop (cost=0.72..16.76 rows=1 width=16)
    -> Index Scan using a_x on a
         (cost=0.29..8.31 rows=1 width=8)
         Index Cond: (x = 10)
    -> Index Scan using b_x on b
         (cost=0.42..8.44 rows=1 width=8)
         Index Cond: (x = 10)
    -> Index Scan using c_x on c
         (cost=0.43..8.45 rows=1 width=8)
     Index Cond: (x = 10)
(8 rows)
复制

请注意，查询先联接“c and a”，然后再联接“a and b”。但是，让我们更仔细地看一下该计划。PostgreSQL的开始与索引扫描a和b。然后将结果与合并c。这里还重要的是使用了三个索引。发生这种情况是由于我们之前讨论过的那些平等约束。

PostgreSQL中的隐式联接与显式联接

许多人不断询问显式联接和隐式联接。基本上，两个变体都是相同的。让我们检查两个查询，看看我们在PostgreSQL上下文中实际上是在谈论什么：

SELECT * FROM a, b WHERE a.id = b.id;
vs.
SELECT * FROM a JOIN b ON a.id = b.id;
复制

这两个查询是相同的，并且计划者将以与大多数常见查询相同的方式对待它们。请注意，显式联接在带或不带括号的情况下都有效。但是，这里有一个非常重要的参数：

demo=# SHOW join_collapse_limit;
 join_collapse_limit
---------------------
 8
(1 row)
复制

该join_collapse_limit有多少明确连接控制隐式计划。换句话说，隐式联接就像显式联接一样，但是最多只能由此参数控制一定数量的联接。

为了简单起见，我们可以假定对于95％的所有查询和大多数客户来说，这没有什么区别。

确定加入策略

PostgreSQL提供了各种连接策略。这些策略包括哈希联接，合并联接，嵌套循环等等。我们已经在以前的帖子中分享了其中一些信息。可以在这里找到有关PostgreSQL连接策略的更多信息。

优化外部联接（LEFT JOIN等）

优化外部联接（LEFT JOIN，RIGHT JOIN等）是一个重要的主题。通常，与内部联接相比，计划者在这里的选项更少。可以进行以下优化：

(A leftjoin B on (Pab)) innerjoin C on (Pac)
     = (A innerjoin C on (Pac)) leftjoin B on (Pab)
复制

其中Pac是引用A和C等的谓词（在这种情况下，显然
Pac不能引用B，否则转换是无意义的）。

(A leftjoin B on (Pab)) leftjoin C on (Pac)
     = (A leftjoin C on (Pac)) leftjoin B on (Pab)
 
(A leftjoin B on (Pab)) leftjoin C on (Pbc)
     = A leftjoin (B leftjoin C on (Pbc)) on (Pab)
复制

尽管这种理论解释是正确的，但大多数人都不知道它在现实生活中意味着什么。因此，我编写了一个真实的示例，展示了PostgreSQL如何实际重新排列真实的联接：

demo=# explain SELECT *
      FROM generate_series(1, 10) AS x
                 LEFT JOIN generate_series(1, 100) AS y
                       ON (x = y)
                 JOIN generate_series(1, 10000) AS z
                       ON (y = z)
;
                           QUERY PLAN 
-------------------------------------------------------------------- 
Hash Join (cost=1.83..144.33 rows=500 width=12)
    Hash Cond: (z.z = x.x)
    -> Function Scan on generate_series z
          (cost=0.00..100.00 rows=10000 width=4)
    -> Hash (cost=1.71..1.71 rows=10 width=8)
      -> Hash Join (cost=0.23..1.71 rows=10 width=8)
           Hash Cond: (y.y = x.x)
           -> Function Scan on generate_series y
                (cost=0.00..1.00 rows=100 width=4)
           -> Hash (cost=0.10..0.10 rows=10 width=4)
                 -> Function Scan on generate_series x
                      (cost=0.00..0.10 rows=10 width=4)
(9 rows)
复制

我们在这里看到的是PostgreSQL优化器决定将x与y，然后与z联接。换句话说，PostgreSQL优化器仅遵循SQL语句中使用的连接顺序。

但是，如果我们决定稍微调整一下参数，会发生什么？

demo=# explain SELECT *
     FROM generate_series(1, 10) AS x
                LEFT JOIN generate_series(1, 100000) AS y
                      ON (x = y)
                JOIN generate_series(1, 100) AS z
                      ON (y = z)
;
                           QUERY PLAN 
-------------------------------------------------------------------- 
Hash Join (cost=1.83..1426.83 rows=5000 width=12)
   Hash Cond: (y.y = x.x)
   -> Function Scan on generate_series y
          (cost=0.00..1000.00 rows=100000 width=4)
   -> Hash (cost=1.71..1.71 rows=10 width=8)
     -> Hash Join (cost=0.23..1.71 rows=10 width=8)
               Hash Cond: (z.z = x.x)
               -> Function Scan on generate_series z
                    (cost=0.00..1.00 rows=100 width=4)
               -> Hash (cost=0.10..0.10 rows=10 width=4)
                    -> Function Scan on generate_series x
                         (cost=0.00..0.10 rows=10 width=4)
(9 rows)
复制

这是相同的查询，但参数稍有更改。不同之处在于PostgreSQL再次以开头，x但随后z先加入，然后再添加y。

请注意，此优化是自动进行的。优化程序可以做出此决定的原因之一是因为存在一段时间前已添加到PostgreSQL的优化程序支持功能。重新排序之所以起作用，是因为支持功能为计划者提供了一个机会来确定从哪一部分返回多少行。如果使用表而不是设置返回函数，则支持函数无关紧要。

PostgreSQL中的自动联接修剪

并非查询中的每个联接实际上都是由PostgreSQL执行的。优化器知道联接修剪的概念，并且能够非常有效地摆脱毫无意义的联接。主要问题是：什么时候可行，我们如何弄清楚到底发生了什么？

下一个清单显示了如何创建一些合适的样本数据：

demo=# CREATE TABLE t_left AS SELECT *
           FROM generate_series(1, 1000) AS id;
SELECT 1000
demo=# CREATE UNIQUE INDEX idx_left ON t_left (id);
CREATE INDEX
demo=# CREATE TABLE t_right AS SELECT *
           FROM generate_series(1, 100) AS id;
SELECT 100
demo=# CREATE UNIQUE INDEX idx_right ON t_right (id);
CREATE INDEX
复制

在这种情况下，我们需要确保双方实际上都有主键或某种唯一约束：

demo=# \d t_left
Table "public.t_left"
Column  | Type    | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id      | integer |           |          |
Indexes:
     "idx_left" UNIQUE, btree (id)
 
demo=# \d t_right
Table "public.t_right"
Column  | Type    | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id      | integer |           |          |
Indexes:
     "idx_right" UNIQUE, btree (id)
复制

为了显示PostgreSQL查询计划程序如何处理联接修剪，我们将看一下两个不同的SQL语句：

demo=# explain SELECT *
           FROM t_left AS a LEFT JOIN t_right AS b
                ON (a.id = b.id);
                          QUERY PLAN
--------------------------------------------------------------------
Hash Left Join (cost=3.25..20.89 rows=1000 width=8)
  Hash Cond: (a.id = b.id)
  -> Seq Scan on t_left a (cost=0.00..15.00 rows=1000 width=4)
  -> Hash (cost=2.00..2.00 rows=100 width=4)
    -> Seq Scan on t_right b (cost=0.00..2.00 rows=100 width=4)
(5 rows)
复制

在这种情况下，必须执行连接。如您所见，PostgreSQL已经决定进行哈希联接。下一个示例仅包含查询的一小部分变化：

demo=# explain SELECT a.*
           FROM t_left AS a LEFT JOIN t_right AS b
                ON (a.id = b.id);
                      QUERY PLAN
------------------------------------------------------------
Seq Scan on t_left a (cost=0.00..15.00 rows=1000 width=4)
(1 row)
复制

如果我们不从右侧读取数据，并且右侧是唯一的，则可能会发生联接修剪。如果右侧不是唯一的，则连接实际上可能会增加返回的行数；否则，可能会增加行数。因此只有在右侧是唯一的情况下才可以修剪。

让我们尝试一下：

demo=# DROP INDEX idx_right ;
DROP INDEX
 
demo=# explain SELECT a.*
           FROM t_left AS a LEFT JOIN t_right AS b
                ON (a.id = b.id);
                         QUERY PLAN
-------------------------------------------------------------------- 
Hash Left Join (cost=3.25..23.00 rows=1000 width=4)
   Hash Cond: (a.id = b.id)
   -> Seq Scan on t_left a (cost=0.00..15.00 rows=1000 width=4)
   -> Hash (cost=2.00..2.00 rows=100 width=4)
     -> Seq Scan on t_right b (cost=0.00..2.00 rows=100 width=4)
(5 rows)
复制

尽管在PostgreSQL优化器中加入修剪肯定是一件好事，但您必须意识到以下事实：计划程序基本上正在修复一些本不应该存在的内容。首先有效地编写查询；不要添加无意义的联接。

存在和反联接

在SQL EXISTS中的代码中到处都可以看到很普遍的事情。这是一个例子：

demo=# explain SELECT * FROM a
           WHERE NOT EXISTS (SELECT 1 FROM b
                 WHERE a.x = b.x
                 AND b.x = 42);
                            QUERY PLAN
-------------------------------------------------------------------
Hash Anti Join (cost=4.46..2709.95 rows=100000 width=8)
 Hash Cond: (a.x = b.x)
 -> Seq Scan on a (cost=0.00..1443.00 rows=100000 width=8)
 -> Hash (cost=4.44..4.44 rows=1 width=4)
      -> Index Only Scan using b_x on b
         (cost=0.42..4.44 rows=1 width=4)
            Index Cond: (x = 42)
(6 rows)
复制

这看起来似乎没什么大不了，但是请考虑其他选择：PostgreSQL在这里所做的是创建“哈希反联接”。这比某种嵌套循环更有效。简而言之：嵌套循环被联接所取代，这可以显着提高性能。

利用排序的输出

每个数据库都严重依赖于排序，这是处理许多不同类型的查询和优化各种工作负载所必需的。这方面的关键优化之一是PostgreSQL可以使用索引以非常聪明的方式优化ORDER BY。

让我们看看它的外观：

demo=# CREATE TABLE t_sample AS
SELECT *
FROM generate_series(1, 1000000) AS id
ORDER BY random();
SELECT 1000000
demo=# VACUUM ANALYZE t_sample ;
VACUUM
复制

该清单创建了一个以随机顺序存储在磁盘上的100万个条目的列表。随后，调用VACUUM以确保在执行测试之前已解决了所有与PostgreSQL提示位相关的问题。如果您想知道什么是提示位以及它们如何工作，请考虑查看我们有关PostgreSQL中提示位的文章。

demo=# explain analyze SELECT *
           FROM t_sample
           ORDER BY id DESC
           LIMIT 100;
                        QUERY PLAN 
-------------------------------------------------------------------
 Limit (cost=25516.40..25528.07 rows=100 width=4)
     (actual time=84.806..86.252 rows=100 loops=1)
   -> Gather Merge (cost=25516.40..122745.49 rows=833334 width=4)
          (actual time=84.805..86.232 rows=100 loops=1)
     Workers Planned: 2
     Workers Launched: 2
     -> Sort (cost=24516.38..25558.05 rows=416667 width=4)
              (actual time=65.576..65.586 rows=82 loops=3)
              Sort Key: id DESC
              Sort Method: top-N heapsort Memory: 33kB
              Worker 0: Sort Method: top-N heapsort Memory: 32kB
              Worker 1: Sort Method: top-N heapsort Memory: 33kB
              -> Parallel Seq Scan on t_sample
                   (cost=0.00..8591.67 rows=416667 width=4)
                   (actual time=0.428..33.305 rows=333333 loops=3)
 Planning Time: 0.078 ms
 Execution Time: <strong>86.286 ms</strong>
(12 rows)
复制

PostgreSQL需要多个内核来在86毫秒内处理查询，这很多。但是，如果我们创建索引会怎样？

demo=# CREATE INDEX idx_sample ON t_sample (id);
CREATE INDEX
demo=# explain analyze SELECT *
           FROM t_sample
           ORDER BY id DESC
           LIMIT 100;
                           QUERY PLAN 
------------------------------------------------------------------- 
 Limit (cost=0.42..3.02 rows=100 width=4)
       (actual time=0.071..0.125 rows=100 loops=1)
   -> Index Only Scan Backward using idx_sample on t_sample
          (cost=0.42..25980.42 rows=1000000 width=4)
          (actual time=0.070..0.113 rows=100 loops=1)
     Heap Fetches: 0
 Planning Time: 0.183 ms
 Execution Time: <strong>0.142 ms</strong>
(5 rows)
复制

最明显的发现是查询执行的时间不到一毫秒。但是，这里最重要的是我们看不到排序步骤。PostgreSQL知道索引以排序顺序（按ID排序）返回数据，因此无需再次对数据进行排序。PostgreSQL会查询索引，并且可以简单地按原样获取数据并将其提供给客户端，直到找到足够的行为止。在这种特殊情况下，甚至可能只进行索引扫描，因为我们只在查找索引中实际存在的列。

减少在各种情况下必须排序的数据量对于性能至关重要，因此对用户体验也很重要。许多人可能感兴趣的情况之一与最小和最大有关：

demo=# explain SELECT min(id), max(id) FROM t_sample;
                        QUERY PLAN 
------------------------------------------------------------------- 
Result (cost=0.91..0.92 rows=1 width=8)
   InitPlan 1 (returns $0)
      -> Limit (cost=0.42..0.45 rows=1 width=4)
           -> Index Only Scan using idx_sample on t_sample
               (cost=0.42..28480.42 rows=1000000 width=4)
               Index Cond: (id IS NOT NULL)
   InitPlan 2 (returns $1)
      -> Limit (cost=0.42..0.45 rows=1 width=4)
           -> Index Only Scan Backward using
               idx_sample on t_sample t_sample_1
                (cost=0.42..28480.42 rows=1000000 width=4)
                Index Cond: (id IS NOT NULL)
(9 rows)
复制

最小值是排序列表中的第一个非NULL值。最大值是非NULL排序值序列中的最后一个值。PostgreSQL可以考虑到这一点，并用简单地查询索引的子计划代替处理聚合的标准方式。在某种程度上，称“ min”与…相同。

demo=# SELECT id
           FROM t_sample
           WHERE id IS NOT NULL
           ORDER BY id
           LIMIT 1;
 id
----
 1
(1 row)
 
demo=# explain SELECT id
           FROM t_sample
           WHERE id IS NOT NULL
           ORDER BY id
           LIMIT 1;
                         QUERY PLAN 
-------------------------------------------------------------------
 Limit (cost=0.42..0.45 rows=1 width=4)
   -> Index Only Scan using idx_sample on t_sample
          (cost=0.42..28480.42 rows=1000000 width=4)
     Index Cond: (id IS NOT NULL)
(3 rows)
复制

幸运的是，PostgreSQL为您做到了这一点，并完美地优化了查询。

工作中的分区修剪和约束排除

分区是许多PostgreSQL用户最喜欢的功能之一。它使您能够减小表的大小并将数据分解为较小的块，这些块更易于处理，并且在许多情况下（并非全部），查询速度更快。但是，请记住，某些查询可能会更快-但是增加计划时间可能会适得其反-这并非罕见。我们已经看到，在不适合分区的情况下，分区会无数次降低速度。

从逻辑上讲，PostgreSQL优化器必须巧妙地进行分区，并确保仅触摸实际上可以包含某些数据的分区。以下是一个示例：

demo=# CREATE TABLE mynumbers (id int)
           PARTITION BY RANGE (id);
CREATE TABLE
demo=# CREATE TABLE negatives
           PARTITION OF mynumbers FOR VALUES FROM (MINVALUE) TO (0);
CREATE TABLE
demo=# CREATE TABLE positives
           PARTITION OF mynumbers FOR VALUES FROM (1) TO (MAXVALUE);
CREATE TABLE
复制

在这种情况下，将创建具有两个分区的表。问题是弄清楚如果我们只寻找正值，计划者将要做什么：

demo=# explain SELECT * FROM mynumbers WHERE id > 1000;
             QUERY PLAN
------------------------------------------
 Seq Scan on positives mynumbers
      (cost=0.00..41.88 rows=850 width=4)
   Filter: (id > 1000)
(2 rows)
复制

PostgreSQL优化器正确地确定数据不能在分区之一中，并将其从执行计划中删除。如果我们要查询所有小于1000的值，则不可能，并且将正确查询所有分区，如下一条SQL语句的执行计划所示：

demo=# explain SELECT * FROM mynumbers WHERE id < 1000; 
QUERY PLAN 
-------------------------------------------------------- 
Append (cost=0.00..92.25 rows=1700 width=4) 
   -> Seq Scan on negatives mynumbers_1
          (cost=0.00..41.88 rows=850 width=4)
     Filter: (id < 1000) 
   > Seq Scan on positives mynumbers_2
          (cost=0.00..41.88 rows=850 width=4)
     Filter: (id < 1000)
(5 rows)
复制

总结…

您在本页上看到的优化仅仅是PostgreSQL可以为您做的所有事情的开始。这是了解幕后情况的一个很好的开始。通常，数据库优化器是某种黑匣子，人们很少知道哪种优化真正发生了。该页面的目的是阐明正在进行的数学转换。

作者：Hans-JürgenSchönig
文章来源：https://www.cybertec-postgresql.com/en/how-the-postgresql-query-optimizer-works/

postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者