查看原文
其他

PostgreSQL中UNION ALL的性能问题

劳伦兹 阿尔贝 开源软件联盟PostgreSQL分会 2023-01-22

前阵子写过查询参数、数据类型对性能的影响。最近遇到一个类似的问题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  的两边使用稍微不同的数据类型是很方便的,但我们看到这可能会导致糟糕的执行计划和糟糕的性能。为安全起见,应用显式类型强制转换使数据类型相同。
 
也许可以让优化器在类似我们调查的情况下更加智能。然而,绕过这个限制是很容易的。

如果你喜欢阅读这篇博客,可以看看我关于在PostgreSQL查询中重写 OR 为UNION的博客。

点击此处阅读原文

↓↓↓

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存