SQL优化子查询上拉

根据该篇文章做的实验发现个有趣的现象，所以记录下来。

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 SELECT i, 1 FROM generate_series(1, 100000) i;
CREATE TABLE t2 AS SELECT * FROM t1;

EXPLAIN (COSTS OFF, ANALYZE)
   SELECT * FROM t1
   WHERE t1.a IN (SELECT a FROM t2 WHERE t2.b = t1.b AND t1.b = 1);

Hash Join (actual time=85.709..122.494 rows=100000 loops=1)
  Hash Cond: (t1.a = t2.a)
  ->  Seq Scan on t1 (actual time=0.025..13.294 rows=100000 loops=1)
        Filter: (b = 1)
  ->  Hash (actual time=85.652..85.654 rows=100000 loops=1)
        Buckets: 131072 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 4931kB
        ->  HashAggregate (actual time=51.838..67.200 rows=100000 loops=1)
              Group Key: t2.b, t2.a
              Batches: 1  Memory Usage: 7185kB
              ->  Seq Scan on t2 (actual time=0.023..17.255 rows=100000 loops=1)
                    Filter: (b = 1)
Planning Time: 0.501 ms
Execution Time: 127.278 ms

注意看执行计划有个HashAggregate ，为什么会出现这个呢？一般来说，子查询不允许返回重复值，为什么呢？

因为你想一想嘛，返回重复值有啥好处吗？ in (1,1,1) 和 in (1) 有什么区别，明显没有。

所以优化器决定使用HashAggregate来消除子查询结果中可能的重复值，如果该值是唯一值，那么就不会有这个操作。

去重操作默认是开启，该配置项由enable_hashagg 控制，一般是采用 group by 和 DISTINCT ，如果禁用，优化器会强制使用其他聚合方法（如排序聚合），可能导致性能下降。该参数是PostgreSQL性能调优的重要选项之一，通常保持默认开启状态。仅在特殊场景（如内存受限时）可能需要临时关闭。

笔者再次执行的时候，计划计划则发生了改变，这个执行计划才符合文档记录。

我们可以看到由Hash Semi Join 替换了 Hash Join ，而且没有了HashAggregate，执行时间大大缩减了 50ms。

可能是统计信息重新收集后，执行器发现了更好的执行路径。

Hash Semi Join (actual time=32.688..69.145 rows=100000 loops=1)
  Hash Cond: (t1.a = t2.a)
  ->  Seq Scan on t1 (actual time=0.022..14.337 rows=100000 loops=1)
        Filter: (b = 1)
  ->  Hash (actual time=32.502..32.503 rows=100000 loops=1)
        Buckets: 131072  Batches: 1  Memory Usage: 4931kB
        ->  Seq Scan on t2 (actual time=0.015..16.514 rows=100000 loops=1)
              Filter: (b = 1)
Planning Time: 0.179 ms
Execution Time: 73.556 ms