PostgreSQL中UNION ALL的性能问题
前阵子写过查询参数、数据类型对性能的影响。最近遇到一个类似的问题UNION ALL，困扰了我一段时间，所以分享给大家。
UNION ALL实现多态
不同的实体可以有一些共同点。例如，客户关系管理系统必须处理客户和员工，这两者都是具有姓名、生日和其它共同属性的人。这种多态性在面向对象编程中很容易建模，但在关系数据库中很难实现。解决这个问题的一种方法是为每个实体创建一个单独的表，并使用UNION ALL对“超类”建模:
CREATE SEQUENCE seq;
CREATE TABLE bird (
id bigint PRIMARY KEY DEFAULT nextval('seq'),
wingspan real NOT NULL,
beak_size double precision NOT NULL
);
CREATE TABLE bat (
id bigint PRIMARY KEY DEFAULT nextval('seq'),
wingspan numeric NOT NULL,
body_temperature numeric NOT NULL
);
CREATE TABLE cat (
id bigint PRIMARY KEY DEFAULT nextval('seq'),
body_temperature numeric NOT NULL,
tail_length numeric
);
CREATE VIEW flying_animal AS
SELECT id, wingspan FROM bird
UNION ALL
SELECT id, wingspan FROM bat;
CREATE VIEW mammal AS
SELECT id, body_temperature FROM bat
UNION ALL
SELECT id, body_temperature FROM cat;
让我们用随机数据填充表格，以便我们可以测试性能：
INSERT INTO bird (wingspan, beak_size)
SELECT 20 + random() * 5, 2 + random()
FROM generate_series(1, 1000000);
INSERT INTO bat (wingspan, body_temperature)
SELECT 15 + random() * 5, 40 + random() * 2
FROM generate_series(1, 1000000);
INSERT INTO cat (body_temperature, tail_length)
SELECT 36.5 + random(), 20 + random() * 3
FROM generate_series(1, 1000000);
UNION ALL视图之间令人惊讶的性能差异
我们想通过加入一个查找表来查找动物：
CREATE TABLE lookup (
id bigint PRIMARY KEY
);
INSERT INTO lookup
VALUES (42), (500000), (1500000), (1700000), (2500000), (2700000);
ANALYZE lookup;
现在让我们检查两个视图的连接性能：
test=> \timing
Timing is on.
test=> SELECT * FROM flying_animal JOIN lookup USING (id);
id │ wingspan
═════════╪═══════════
42 │ 23.711483
500000 │ 24.004992
1500000 │ 15.684511
1700000 │ 18.573671
(4 rows)
Time: 384.368 ms
test=> SELECT * FROM mammal JOIN lookup USING (id);
id │ body_temperature
═════════╪══════════════════
1500000 │ 41.1855152884075
1700000 │ 40.7127191738703
2500000 │ 36.5138848514496
2700000 │ 36.9444466002649
(4 rows)
Time: 1.282 ms哎呀！那些会飞的动物怎么了？
调查差异
我们来看看各自的执行计划：
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM flying_animal JOIN lookup USING (id);
QUERY PLAN
══════════════════════════════════════
Hash Join (actual time=0.139..542.474 rows=4 loops=1)
Hash Cond: (bird.id = lookup.id)
-> Append (actual time=0.081..459.626 rows=2000000 loops=1)
-> Seq Scan on bird (actual time=0.079..72.049 rows=1000000 loops=1)
-> Subquery Scan on "*SELECT* 2" (actual time=0.016..314.671 rows=1000000 loops=1)
-> Seq Scan on bat (actual time=0.008..73.920 rows=1000000 loops=1)
-> Hash (actual time=0.018..0.019 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on lookup (actual time=0.008..0.011 rows=6 loops=1)
Planning Time: 0.311 ms
Execution Time: 542.533 ms
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM mammal JOIN lookup USING (id);
QUERY PLAN
══════════════════════════════════════
Nested Loop (actual time=0.083..0.116 rows=4 loops=1)
-> Seq Scan on lookup (actual time=0.008..0.010 rows=6 loops=1)
-> Append (actual time=0.016..0.017 rows=1 loops=6)
-> Index Scan using bat_pkey on bat (actual time=0.012..0.012 rows=0 loops=6)
Index Cond: (id = lookup.id)
-> Index Scan using cat_pkey on cat (actual time=0.004..0.004 rows=0 loops=6)
Index Cond: (id = lookup.id)
Planning Time: 0.183 ms
Execution Time: 0.150 ms
对哺乳动物mammal表的查询就像我们想要的那样:它执行嵌套循环连接，使用bat和cat上的主键索引快速检索我们需要的几行。相反，飞行动物flying_animal表上的查询扫描bird和bat的所有200万行以执行散列连接。
您看得出来差别吗？
调查UNION ALL查询之间的差异
为了获得快速执行计划，PostgreSQL必须将连接条件传播到UNION ALL(在执行计划中变为“Append”)。仔细观察，我们注意到慢速查询中奇怪的“子查询扫描”“*SELECT* 2”。实际上，UNION ALL的分支是子查询。但是通常PostgreSQL优化器会尽可能地将子查询“拉”到主连接树中。让我们读取src/backend/optimizer/prep/prepjointree.c中pull_up_subqueries_recurse() 的源代码:
/*
* Alternatively, is it a simple UNION ALL subquery? If so, flatten
* into an "append relation".
*
* It's safe to do this regardless of whether this query is itself an
* appendrel member. (If you're thinking we should try to flatten the
* two levels of appendrel together, you're right; but we handle that
* in set_append_rel_pathlist, not here.)
*/
if (rte->rtekind == RTE_SUBQUERY &&
is_simple_union_all(rte->subquery))
return pull_up_simple_union_all(root, jtnode, rte);
is_simple_union_all()的函数注释告诉我们更多：
/*
* We require all the setops to be UNION ALL (no mixing) and there can't be
* any datatype coercions involved, ie, all the leaf queries must emit the
* same datatypes.
*/
这给了我们决定性的线索。查看表定义，我们注意到鸟的翼展wingspan数据类型为real，但蝙蝠的翼展数据类型为numeric。优化器不能正确地平化UNION ALL的原因是数据类型的差异。这保留了子查询，从而阻止优化器找到理想的执行计划。
令人惊讶的是，导致问题的列在查询中并没有发挥重要作用，这使得我们很难得出这样的结论。我们甚至可以从SELECT列表中省略它，我们仍然会得到糟糕的执行计划!
UNION ALL和数据类型
UNION的两个分支必须具有相同数量的列，并且列必须具有兼容的数据类型。文档描述了“兼容”的确切含义。本质上，如果数据类型不同，PostgreSQL会选择从另一种类型进行隐式转换(PostgreSQL术语称之为强制转换)。如果存在任意一个方向上的隐式转换，PostgreSQL首选UNION的第一个分支的数据类型。
在我们的例子中，有一个从numeric到real的隐式转换，但没有相反的方式，所以在flying_animals表中翼展wingspan 的数据类型是real。虽然这使我们能够毫无问题地创建视图，但却导致了我们观察到的性能问题。
显式类型转换作为性能问题的解决方案
有一种简单的方法可以确保两侧的数据类型UNION ALL相同：应用显式类型转换！
所以我们可以将视图重新定义为：
CREATE OR REPLACE VIEW flying_animal AS
SELECT id, wingspan FROM bird
UNION ALL
SELECT id, wingspan::real FROM bat;
而且，果然，我们最终得到了一个快速执行计划：
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM flying_animal JOIN lookup USING (id);
QUERY PLAN
═══════════════════════════════════════
Nested Loop (actual time=7.714..10.593 rows=4 loops=1)
-> Seq Scan on lookup (actual time=0.008..0.015 rows=6 loops=1)
-> Append (actual time=1.682..1.759 rows=1 loops=6)
-> Index Scan using bird_pkey on bird (actual time=1.445..1.446 rows=0 loops=6)
Index Cond: (id = lookup.id)
-> Index Scan using bat_pkey on bat (actual time=0.308..0.309 rows=0 loops=6)
Index Cond: (id = lookup.id)
Planning Time: 0.452 ms
Execution Time: 10.673 ms
结论
虽然PostgreSQL允许我们在 UNION ALL 的两边使用稍微不同的数据类型是很方便的，但我们看到这可能会导致糟糕的执行计划和糟糕的性能。为安全起见，应用显式类型强制转换使数据类型相同。
也许可以让优化器在类似我们调查的情况下更加智能。然而，绕过这个限制是很容易的。
点击此处阅读原文
↓↓↓