在from子句子查询中使用并集提高查询的性能

Question

如何提高此查询的性能？我在c1上有一个索引，在tb1上没有选择的主键。我的目标是为c3的每个实例创建一个记录。

select 
t1.*
into tb2
from (
select 
c1,
c2,
c3
from tb1
where c1 = '1'
union
select 
c1,
c2,
c4
from tb1
where c1 = '1'
) as t1
where t1.c3 != ''

表格定义(PG v9.0)：

CREATE TABLE tb1 (
c1 text
c2 text
c3 text
c4 text
);

索引：

CREATE INDEX c1_idx   ON tb1 USING btree (c1);

Answer 1

CREATE TABLE tb2 AS 
SELECT c1, c2, c3
FROM   tb1
WHERE  c1 = '1'
AND    c3 <> ''
UNION ALL
SELECT c1, c2, c4
FROM   tb1
WHERE  c1 = '1'
AND    c4 <> '';

• 除非要消除可能的重复项，否则请使用UNION ALL。更快。如果出现重复，结果会有所不同
• 不需要子查询
• 使用CREATE TABLE AS而不是SELECT INTO。根据文件：

此命令在功能上类似于SELECT INTO，但优选，因为它不太可能与SELECT INTO语法。此外，CREATE TABLE AS提供了一个超集CCD_ 6提供的功能。

• '1'周围的单引号表示将数字数据存储为字符串类型。如果是这样，那就不好了。考虑适当的数据类型

替代CTE

如果表格很大，这种带有CTE的替代方案可能会更快，因为我们只从中读取一次。

还包括来自评论、添加列、添加pk:的其他请求

CREATE TABLE tb2 AS 
WITH cte AS (
SELECT c1, c2, c3, c4
FROM   tb1
WHERE  c1 = '1'
AND   (c3 <> '' OR c4 <> '')
)
SELECT c1, c2, c3, NULL::text AS new_column  -- add new column
FROM   cte
WHERE  c3 <> ''
UNION ALL
SELECT c1, c2, c4, NULL
FROM   cte
WHERE  c4 <> '';
-- add primary key:
ALTER TABLE tb2 ADD CONSTRAINT tb2_pkey PRIMARY KEY(???);

如果表很大，则可能需要增加temp_buffers设置(仅用于会话)。此相关答案中的更多信息：
如何删除重复条目？

如果您需要经常运行此操作(并且不介意写操作的小损失)，并且如果您可以定义一个超集行来消除其余的大部分，我建议使用部分索引。。。