PostgreSQL 10 - 查询优化2

最新推荐文章于 2025-05-21 10:21:38 发布

此心光明-超然

最新推荐文章于 2025-05-21 10:21:38 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏： PostgreSQL 文章标签： PostgreSQL

本文链接：https://blog.youkuaiyun.com/weixin_43364172/article/details/85318270

本文探讨了PostgreSQL中查询优化的相关话题，包括处理外连接、join_collapse_limit的影响、优化器设置的选择，以及如何通过调整工作内存参数加速排序和管理任务。示例展示了不同设置如何改变查询执行计划，强调了正确理解查询语义和适当调整参数对性能的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

连接

我们先建表，准备些数据：

postgres=# CREATE TABLE a (aid int);
CREATE TABLE
postgres=# CREATE TABLE b (bid int);
CREATE TABLE
postgres=# INSERT INTO a VALUES (1), (2), (3);
INSERT 0 3
postgres=# INSERT INTO b VALUES (2), (3), (4);
INSERT 0 3

我们看一个简单的外连接：

postgres=# SELECT * FROM a LEFT JOIN b ON (aid = bid);
 aid | bid 
-----+-----
   1 |    
   2 |   2
   3 |   3
(3 行记录)

下一个例子对很多人来说，可能是个惊喜：

postgres=# SELECT * FROM a LEFT JOIN b ON (aid = bid AND bid = 2);
 aid | bid 
-----+-----
   1 |    
   2 |   2
   3 |    
(3 行记录)

结果集没有减少。很多人以为只返回一行，会导致一些隐藏的问题。

postgres=# SELECT avg(aid), avg(bid) FROM a LEFT JOIN b
ON (aid = bid AND bid = 2);
        avg         |        avg         
--------------------+--------------------
 2.0000000000000000 | 2.0000000000000000
(1 行记录)

平均值并不是在单行的基础上计算的。人们写外连接，却不知道在让PostgreSQL做什么。所以，在质疑外连接的性能之前，先要问一下它的语义是什么。

处理外连接

一般来说，重新排列内连接的顺序，能显著加快查询的执行速度。但是，对于外连接，只允许少数重排序操作。

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

Pac是引用A和C的谓词，同样的，Pab是引用A和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)

当对于所有的B的空行，谓词Pbc一定失败，则最后一条规则成立-就是说，B至少有一列。如果Pbc不严格（strict），第一种形式可能产生一些C列非空的行，而第二种形式会使整行为空。
虽然一些连接可以重排，但是，查询一般不会受益：

SELECT ...
  FROM a LEFT JOIN b ON (aid = bid)
    LEFT JOIN c ON (bid = cid)
    LEFT JOIN d ON (cid = did)
...

解决这个问题的办法是，检查一下是否真的需要这些外连接。

join_collapse_limit

在查询计划处理期间，PostgreSQL会检查所有可能的连接顺序。很多时候，这很昂贵，让计划处理速度变慢。
join_collapse_limit变量可以让开发者解决问题的工具-以更直接的方式，查询应该如何处理。
先看一下这几条语句：

SELECT * FROM tab1, tab2, tab3
    WHERE tab1.id = tab2.id
    AND tab2.ref = tab3.id;
SELECT * FROM tab1 CROSS JOIN tab2
    CROSS JOIN tab3
    WHERE tab1.id = tab2.id
    AND tab2.ref = tab3.id;
SELECT * FROM tab1 JOIN (tab2 JOIN tab3
    ON (tab2.ref = tab3.id))
    ON (tab1.id = tab2.id);

这三条语句是相同的，优化器会以同样的办法处理。第一条语句是隐式连接，最后一个由显式连接组成。在内部，优化器会检查这些请求，对连接重新排序以获得最佳的运行时间。问题是：有多少显式连接会修改成隐式的？可以修改join_collapse_limit来告诉优化器。对于普通查询，默认值就挺好的。如果你的查询有很多连接，修改它可以减少计划执行时间。
想看join_collapse_limit如何改变执行计划，请看下面的查询：

EXPLAIN WITH x AS
(
SELECT * FROM generate_series(1, 1000) AS id
)
SELECT *
FROM x AS a
JOIN x AS b ON (a.id = b.id)
JOIN x AS c ON (b.id = c.id)
JOIN x AS d ON (c.id = d.id)
JOIN