案例：改写SQL消除子查询（案例2）

现象描述

如下SQL语句：

  1. UPDATE normal_date n SET time = (
  2.     SELECT time FROM normal_date_part p WHERE p.id = n.id
  3. )
  4. WHERE EXISTS
  5.     (SELECT 1
  6.     FROM normal_date_part n2
  7.     WHERE n2.id = n.id);

计划为：

  1.                                                                            QUERY PLAN
  4. ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  5.  Update on normal_date n  (cost=224.40..2334150.22 rows=5129 width=16) (actual time=17.336..42944.734 rows=10000 loops=1)
  6.    ->  Hash Semi Join  (cost=224.40..2334150.22 rows=5129 width=16) (actual time=16.997..42852.967 rows=10000 loops=1)
  7.          Hash Cond: (n.id = n2.id)
  8.          ->  Seq Scan on normal_date n  (cost=0.00..160.29 rows=5129 width=10) (actual time=0.113..7.271 rows=10000 loops=1)
  9.          ->  Hash  (cost=160.29..160.29 rows=5129 width=10) (actual time=7.381..7.381 rows=10000 loops=1)
  10.                 Buckets: 32768  Batches: 1  Memory Usage: 430kB
  11.                ->  Seq Scan on normal_date n2  (cost=0.00..160.29 rows=5129 width=10) (actual time=0.052..3.501 rows=10000 loops=1)
  12.          SubPlan 1
  13.            ->  Partition Iterator  (cost=0.00..455.00 rows=1 width=8) (actual time=21006.481..42756.884 rows=10000 loops=10000)
  14.                  Iterations: 331
  15.                  ->  Partitioned Seq Scan on normal_date_part p  (cost=0.00..455.00 rows=1 width=8) (actual time=27228.532..27261.944 rows=10000 loops=3310000)
  16.                        Filter: (id = n.id)
  17.                        Rows Removed by Filter: 99990000
  18.                        Selected Partitions:  1..331
  19.  Total runtime: 42947.153 ms
  20. (15 rows)

优化说明

很明显，执行计划中存在SubPlan，并且SubPlan中的运算相当重，即此SubPlan是一个明确的性能瓶颈点。

根据SQL语意等价改写SQL消除SubPlan如下：

  1. update normal_date n set time = (
  2.     select time from normal_date_part p where p.id = n.id
  3. );