KingabseES 构造常量数据表的方式 union, values, array

背景

通用报表系统中，如果过滤条件是多选数据项，需要动态构造虚拟数据表，这里也会成为查询性能的痛点。

构造方式与执行计划

构造1000行数据的虚拟表。

SQL UNION

组合多个查询的结果，需要解析每个查询语句。

with cte (id, name) as
         (select 1000, 'a9b7ba70783b617e9998dc4dd82eb3c5' from dual
          union all
          select 1001, 'b8c37e33defde51cf91e1e03e51657da' from dual
          union all
          select 1002, 'fba9d88164f3e2d9109ee770223212a0' from dual
          union all
          ...
          select 2000, '08f90c1a417155361a5c4b8d297e0d78' from dual)
select *
from cte;

Append  (cost=0.00..15.01 rows=1001 width=36) (actual time=0.005..0.294 rows=1001 loops=1)
  ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=1)
  ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)
  ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)
...
  ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.000..0.000 rows=1 loops=1)
Planning Time: 440.377 ms
Execution Time: 2.045 ms

VALUES 列表

VALUES 提供了一种生成“常量表”的方法，它可以被使用在一个查询中而不需要实际在磁盘上创建一个表。

每一个被圆括号包围的表达式列表生成表中的一行。列表都必须具有相同数据的元素（即表中列的数目），并且在每个列表中对应的项必须具有可兼容的数据类型。

with cte (id, name) as
         (values (1000, 'a9b7ba70783b617e9998dc4dd82eb3c5'),
                 (1001, 'b8c37e33defde51cf91e1e03e51657da'),
                 (1002, 'fba9d88164f3e2d9109ee770223212a0'),
                 (1003, 'aa68c75c4a77c87f97fb686b2f068676'),
                 ....         
                 (2000, '08f90c1a417155361a5c4b8d297e0d78'))
select *
from cte;

Values Scan on "*VALUES*"  (cost=0.00..12.51 rows=1001 width=36) (actual time=0.001..0.174 rows=1001 loops=1)
Planning Time: 0.339 ms
Execution Time: 0.202 ms

UNNEST(ANYARRAY,...)

UNNEST函数展开数个的数组。

with cte  (id, name ) as
    (select * 
     from unnest('{1000,1001,1002,1003,...,2000}'::int[]
            ,'{a9b7ba70783b617e9998dc4dd82eb3c5,b8c37e33defde51cf91e1e03e51657da,fba9d88164f3e2d9109ee770223212a0,...,08f90c1a417155361a5c4b8d297e0d78}'::text[])
    )
select * from cte ;

Function Scan on unnest  (cost=0.01..10.02 rows=1001 width=36) (actual time=0.076..0.137 rows=1001 loops=1)
Planning Time: 0.014 ms
Execution Time: 0.174 ms

总结

SQL拼接方式，造成语句复杂，解析时间过长，性能较差。

VALUES 列表，“常量表” 比查询结果集合并，拥有更简单执行计划和更强的性能。

UNNEST函数，数组类型变量比“常量表” 更简单，更少的解析时间，执行时间最少。

所以，动态查询语句需要构造“常量表” ，建议使用UNNEST函数方式。