PostgreSQL中的SQL优化：IN、EXISTS、ANYALL与JOIN

这是针对 PostgreSQL 数据库编写 SQL 查询的开发人员最常见的问题之一。有多种方法可以在 SQL 语句中构建子选择或查找。PostgreSQL 优化器在优化查询方面非常智能，并且许多查询可以重写/转换以获得更好的性能。

让我们用一个例子来讨论这个主题，我使用 pgbench 创建的模式。

注意：对于那些不熟悉 pgbench 的人来说，它是 PostgreSQL 附带的一个微型基准测试工具。可以使用一些数据来初始化示例 pgbench 模式，如下所示：

pgbench -i -s 10

对于这个例子，我更新了几个分支的分支余额：

update pgbench_branches set bbalance=4500000 where bid in (4,7);

包含查询

此示例的 SQL 挑战是：从 pgbench_accounts 中找出分支级别余额大于零的分支的每个分支的帐户数。根据 ANSI SQL 标准，可以用四种不同的方式编写此查询。

1. 使用 IN 子句

SELECT count(aid),bid FROM pgbench_accounts WHERE
bid in (SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;

2. 使用 ANY 子句

SELECT count(aid),bid FROM pgbench_accounts WHERE
bid = ANY(SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;

3. 使用 EXISTS 子句

SELECT count(aid),bid
FROM pgbench_accounts WHERE EXISTS
(SELECT bid FROM pgbench_branches WHERE bbalance > 0
AND pgbench_accounts.bid = pgbench_branches.bid)
GROUP BY bid;

4. 使用内连接

SELECT count(aid),a.bid
FROM pgbench_accounts a
JOIN pgbench_branches b ON a.bid = b.bid
WHERE b.bbalance > 0
GROUP BY a.bid;

在编写查询时，人们可能会认为 EXISTS 和 INNER JOIN 可能更好，因为它们可以使用连接两个表的所有逻辑和优化，而 IN 和 ANY 子句

需要处理子查询。然而，PostgreSQL（至少 PG 10 及以上）足够聪明，可以为所有四个选项生成相同的执行计划！

上述所有查询都将生成相同的执行计划，如下所示：

HashAggregate  (cost=31132.65..31132.75 rows=10 width=12) (actual time=279.625..279.626 rows=2 loops=1)
   Group Key: a.bid
   ->  Hash Join  (cost=1.15..30132.65 rows=200000 width=8) (actual time=63.686..242.956 rows=200000 loops=1)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..26394.00 rows=1000000 width=8) (actual time=0.012..86.250 rows=1000000 loops=1)
         ->  Hash  (cost=1.12..1.12 rows=2 width=4) (actual time=0.016..0.016 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.12 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)
                     Filter: (bbalance > 0)
                     Rows Removed by Filter: 8
 Planning Time: 0.257 ms
 Execution Time: 279.703 ms
(12 rows)

注意：抑制并行执行以获得更好的可读性和简单的执行计划。即使使用并行执行计划，所有查询也会生成相同的执行计划。

那么我们是否可以得出这样的结论：我们可以按照自己的意愿编写查询，而 PostgreSQL 的智能会处理其余的事情？等等！如果我们采用

排除场景，情况可能会有所不同。

排除查询

SQL 挑战变成：从 pgbench_accounts 找出每个分支的账户数量，除了那些分支级别余额大于零的分支。

所以编写查询的四种方式就变成了：

1. 使用 NOT IN

SELECT count(aid),bid FROM pgbench_accounts WHERE
bid NOT IN (SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;

2. 使用 <> 全部

SELECT count(aid),bid FROM pgbench_accounts WHERE
bid <> ALL(SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;

3.使用NOT EXISTS

SELECT count(aid),bid
FROM pgbench_accounts WHERE NOT EXISTS
(SELECT bid FROM pgbench_branches WHERE bbalance > 0
AND pgbench_accounts.bid = pgbench_branches.bid)
GROUP BY bid;

4. 使用 LEFT JOIN 和 IS NULL

SELECT count(aid),a.bid
FROM pgbench_accounts a
LEFT JOIN pgbench_branches b ON a.bid = b.bid AND b.bbalance > 0
WHERE b.bid IS NULL
GROUP BY a.bid;

“NOT IN”和“<> ALL”生成带有子查询（SubPlan）的执行计划。他们分别是：

HashAggregate  (cost=31395.13..31395.23 rows=10 width=12) (actual time=395.297..395.299 rows=8 loops=1)
   Group Key: pgbench_accounts.bid
   ->  Seq Scan on pgbench_accounts  (cost=1.13..28895.13 rows=500000 width=8) (actual time=0.042..250.086 rows=800000 loops=1)
         Filter: (NOT (hashed SubPlan 1))
         Rows Removed by Filter: 200000
         SubPlan 1
           ->  Seq Scan on pgbench_branches  (cost=0.00..1.12 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)
                 Filter: (bbalance > 0)
                 Rows Removed by Filter: 8
 Planning Time: 0.197 ms
 Execution Time: 395.363 ms
(11 rows)

和

HashAggregate  (cost=601394.00..601394.10 rows=10 width=12) (actual time=731.987..731.989 rows=8 loops=1)
   Group Key: pgbench_accounts.bid
   ->  Seq Scan on pgbench_accounts  (cost=0.00..598894.00 rows=500000 width=8) (actual time=0.041..579.264 rows=800000 loops=1)
         Filter: (SubPlan 1)
         Rows Removed by Filter: 200000
         SubPlan 1
           ->  Materialize  (cost=0.00..1.14 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=1000000)
                 ->  Seq Scan on pgbench_branches  (cost=0.00..1.12 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)
                       Filter: (bbalance > 0)
                       Rows Removed by Filter: 8
 Planning Time: 0.203 ms
 Execution Time: 732.142 ms
(12 rows)

虽然 NOT EXISTS 和 LEFT JOIN 产生相同的执行计划，但没有子计划，如下所示：

HashAggregate  (cost=41245.15..41245.25 rows=10 width=12) (actual time=500.193..500.195 rows=8 loops=1)
   Group Key: a.bid
   ->  Hash Anti Join  (cost=1.15..37245.15 rows=800000 width=8) (actual time=0.041..344.845 rows=800000 loops=1)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..26394.00 rows=1000000 width=8) (actual time=0.013..110.645 rows=1000000 loops=1)
         ->  Hash  (cost=1.12..1.12 rows=2 width=4) (actual time=0.018..0.018 rows=2 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.12 rows=2 width=4) (actual time=0.011..0.012 rows=2 loops=1)
                     Filter: (bbalance > 0)
                     Rows Removed by Filter: 8
 Planning Time: 0.248 ms
 Execution Time: 500.266 ms
(12 rows)

表之间的这些直接哈希（反）连接是响应查询的最明智的方法。因此，这是推荐 EXISTS 语法或 JOIN 语法的有力理由。因此，有利于

EXISTS/JOIN 的一般经验法则是有效的。

可是等等！即使使用子计划，我们是否会看到 NOT IN 子句的执行时间更好？是的。PostgreSQL 做了出色的优化，从而准备了一个子计

划 NOT的哈希值( hashed SubPlan 1 ) 。所以PostgreSQL对如何处理IN子句有了更好的理解，这是逻辑思维方式，因为很多人倾向于使

用IN子句来编写。但是子计划返回的行很少（两行）。即使子查询返回几百行，也会发生同样的情况。

但是如果子查询返回的行数很多（几十万行）怎么办？让我们尝试一个简单的例子：

CREATE TABLE t1 AS
SELECT * FROM generate_series(0, 500000) id;

CREATE TABLE t2 AS
SELECT (random() * 4000000)::integer id
FROM generate_series(0, 4000000);

ANALYZE t1;
ANALYZE t2;

EXPLAIN SELECT id
FROM t1
WHERE id NOT IN (SELECT id FROM t2);

在本例中，执行计划为：

 Seq Scan on t1  (cost=0.00..2583268004.52 rows=250000 width=4)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..9333.01 rows=400001 width=4)
           ->  Seq Scan on t2  (cost=0.00..5770.01 rows=400001 width=4)
(5 rows)

在这种情况下，执行计划切换到子计划结果的具体化，估计成本跳至25831564501.02！（使用 PostgreSQL 默认设置，如果 t2 的行数大

约小于 100k，它将使用我们讨论的散列子计划。）这将导致性能大幅下降。因此，如果子计划选择的行数较少，则 IN 子句效果很好。

这里的问题是，当开发发生时，表中的行数会减少，并且随着行数的增加，它的工作方式会有所不同，因为执行计划会发生变化，并可能

导致实时生产中出现严重的性能问题。

我们是否应该注意更多的复杂性？

是的，当我们以不同的方式编写查询时，可能会发生数据类型转换。

例如，这样的语句：

EXPLAIN ANALYZE SELECT * FROM emp WHERE gen = ANY(ARRAY['M','F']);

导致字段值隐式数据类型转换为文本。

Seq Scan on emp  (cost=0.00..1.04 rows=2 width=43) (actual time=0.023..0.026 rows=3 loops=1)
   Filter: ((gen)::text = ANY ('{M,F}'::text[]))

请注意数据类型转换： ( gen ) :: text。在大表上，这种类型的转换会产生开销，而 PostgreSQL 在处理 IN 子句方面做得更好。

EXPLAIN ANALYZE SELECT * FROM emp WHERE gen IN ('M','F');

 Seq Scan on emp  (cost=0.00..1.04 rows=3 width=43) (actual time=0.030..0.034 rows=3 loops=1)
   Filter: (gen = ANY ('{M,F}'::bpchar[]))

即使 IN 子句转换为 ANY 子句，“gen”字段的数据类型也没有转换。并且指定的值“M”、“F”将转换为 bpchar，它是 CHAR 的内部等效项。

概括

我撰写这篇博文的目的并不是要支持任何特定的查询编写方式，而是要阐明哪里可能会出错以及应该考虑什么。

一般来说，我曾经向开发人员建议，编写好的 SQL 语句的关键是遵循一步一步的过程。

1. 首先，列出应从中检索数据的表。
2. 然后考虑如何 JOIN 这些表。
3. 考虑如何让最少的记录参与连接条件。

避免从“如何将逻辑分解”到子查询中思考。

永远不要假设在表中只有少量数据的情况下查询可以很好地执行。

使用EXPLAIN 计划来理解背后发生的事情。

一般来说，表的 EXISTS 和direct JOIN 通常会产生良好的结果。在许多情况下，PostgreSQL 将 IN 子句优化为散列子计划。在某些特定情况下，“IN”可以带来更好的计划和执行。同样，一切都取决于查询在内部如何重写/转换。值得投入时间重写查询以获得更好的优化。

本栏目推荐文章
• SQL Server 检测是不是数字型数据的两种方法
• 用于PostgreSQL索引维护的有用查询
• SQL Join的一些总结
• SQL Server 高性能写入的一些总结
• 网络攻击技术开篇——SQL Injection
• SQL SERVER日期时间转字符串
• sql server 保留2位小数
• 【ubantu22.10】安装部署timescaledbv2.13.0及postgresql v14.10
• Ubuntu 安装Postgresql与PostGIS
• thinkphp6的join连表查询

PostgreSQL EXISTS ANYALL JOIN SQLpostgresql exists anyall join 语句exists sql join sql 场景exists sql 运算符exists sql sql_join postgresql案例hash join oracle server join sql inner join sql sql_join原理join sql