"UNION ALL"视图上的查询速度慢

Question

"UNION ALL"视图上的查询速度慢

sqlperformancepostgresqlindexingunion-all

15

我有一个数据库视图，基本上由两个 SELECT 查询和 UNION ALL 组成，如下所示：

CREATE VIEW v AS
SELECT time, etc. FROM t1 // #1...
UNION ALL
SELECT time, etc. FROM t2 // #2...

问题在于形如以下的 select 语句

SELECT ... FROM v WHERE time >= ... AND time < ...

执行非常缓慢。

当我创建视图v1和v2时，选择语句1和2都相当快，已经正确地建立了索引等：

CREATE VIEW v1 AS
SELECT time, etc. FROM t1 // #1...

CREATE VIEW v2 AS
SELECT time, etc. FROM t2 // #2...

上面提到的相同选择（SELECT）和相同的 WHERE 条件，在它们各自单独执行时都能正常工作。

有任何想法关于可能出现问题的地方以及如何解决它？

（只是提一下，这是最近的 Postgres 版本之一。）

编辑：添加匿名查询计划（感谢 @filiprem 提供的链接到一个很棒的工具）：

v1:

Aggregate  (cost=9825.510..9825.520 rows=1 width=53) (actual time=59.995..59.995 rows=1 loops=1)
  ->  Index Scan using delta on echo alpha  (cost=0.000..9815.880 rows=3850 width=53) (actual time=0.039..53.418 rows=33122 loops=1)
          Index Cond: (("juliet" >= 'seven'::uniform bravo_victor oscar whiskey) AND ("juliet" <= 'november'::uniform bravo_victor oscar whiskey))
          Filter: ((NOT victor) AND ((bravo_sierra five NULL) OR ((bravo_sierra)::golf <> 'india'::golf)))

v2：

Aggregate  (cost=15.470..15.480 rows=1 width=33) (actual time=0.231..0.231 rows=1 loops=1)
  ->  Index Scan using yankee on six charlie  (cost=0.000..15.220 rows=99 width=33) (actual time=0.035..0.186 rows=140 loops=1)
          Index Cond: (("juliet" >= 'seven'::uniform bravo oscar whiskey) AND ("juliet" <= 'november'::uniform bravo oscar whiskey))
          Filter: (NOT victor)

v：

Aggregate  (cost=47181.850..47181.860 rows=1 width=0) (actual time=37317.291..37317.291 rows=1 loops=1)
  ->  Append  (cost=42.170..47132.480 rows=3949 width=97) (actual time=1.277..37304.453 rows=33262 loops=1)
        ->  Nested Loop Left Join  (cost=42.170..47052.250 rows=3850 width=99) (actual time=1.275..37288.465 rows=33122 loops=1)
              ->  Hash Left Join  (cost=42.170..9910.990 rows=3850 width=115) (actual time=1.123..117.797 rows=33122 loops=1)
                      Hash Cond: ((alpha_seven.two)::golf = (quebec_three.two)::golf)
                    ->  Index Scan using delta on echo alpha_seven  (cost=0.000..9815.880 rows=3850 width=132) (actual time=0.038..77.866 rows=33122 loops=1)
                            Index Cond: (("juliet" >= 'seven'::uniform bravo_victor oscar whiskey_two) AND ("juliet" <= 'november'::uniform bravo_victor oscar whiskey_two))
                            Filter: ((NOT victor) AND ((bravo_sierra five NULL) OR ((bravo_sierra)::golf <> 'india'::golf)))
                    ->  Hash  (cost=30.410..30.410 rows=941 width=49) (actual time=1.068..1.068 rows=941 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 75kB
                          ->  Seq Scan on alpha_india quebec_three  (cost=0.000..30.410 rows=941 width=49) (actual time=0.010..0.486 rows=941 loops=1)
              ->  Index Scan using mike on hotel quebec_sierra  (cost=0.000..9.630 rows=1 width=24) (actual time=1.112..1.119 rows=1 loops=33122)
                      Index Cond: ((alpha_seven.zulu)::golf = (quebec_sierra.zulu)::golf)
        ->  Subquery Scan on "*SELECT* 2"  (cost=34.080..41.730 rows=99 width=38) (actual time=1.081..1.951 rows=140 loops=1)
              ->  Merge Right Join  (cost=34.080..40.740 rows=99 width=38) (actual time=1.080..1.872 rows=140 loops=1)
                      Merge Cond: ((quebec_three.two)::golf = (charlie.two)::golf)
                    ->  Index Scan using whiskey_golf on alpha_india quebec_three  (cost=0.000..174.220 rows=941 width=49) (actual time=0.017..0.122 rows=105 loops=1)
                    ->  Sort  (cost=18.500..18.750 rows=99 width=55) (actual time=0.915..0.952 rows=140 loops=1)
                            Sort Key: charlie.two
                            Sort Method:  quicksort  Memory: 44kB
                          ->  Index Scan using yankee on six charlie  (cost=0.000..15.220 rows=99 width=55) (actual time=0.022..0.175 rows=140 loops=1)
                                  Index Cond: (("juliet" >= 'seven'::uniform bravo_victor oscar whiskey_two) AND ("juliet" <= 'november'::uniform bravo_victor oscar whiskey_two))
                                  Filter: (NOT victor)

juliet 是 time。

- Mladen Jablanović

1

你的视图中的“时间”列没有索引。你需要手动为该列建立索引。查看执行计划。 - stian.net

@MarkBannister：是的。如果那是一个建议，我想避免创建物化视图或者叫什么名字的东西。 :) - Mladen Jablanović

4

Mladen，请解释一下输出格式化程序和匿名器 -> http://explain.depesz.com/ - filiprem

2

第七天了，仍然没有明显的答案。你能否实际发布两个实际的查询，而不仅仅是查询计划本身... - DRapp

1

如果您的视图使用UNION DISTINCT而不是UNION ALL会发生什么？它是否执行得更快？它是否会给出错误的结果？ - Walter Mitty

显示剩余5条评论

8个回答

6

我相信你的查询正在类似这样的方式下执行:

(
   ( SELECT time, etc. FROM t1 // #1... )
   UNION ALL
   ( SELECT time, etc. FROM t2 // #2... )
)
WHERE time >= ... AND time < ...

优化器正在尝试优化的查询存在困难。也就是说，在应用WHERE子句之前，它首先执行UNION ALL操作，但是您希望在执行UNION ALL操作之前应用WHERE子句。

您是否可以将WHERE子句放入CREATE VIEW中？

CREATE VIEW v AS
( SELECT time, etc. FROM t1  WHERE time >= ... AND time < ... )
UNION ALL
( SELECT time, etc. FROM t2  WHERE time >= ... AND time < ... )

如果视图无法使用WHERE子句，则可以保留这两个视图并在需要时使用UNION ALL和WHERE子句：

CREATE VIEW v1 AS
SELECT time, etc. FROM t1 // #1...

CREATE VIEW v2 AS
SELECT time, etc. FROM t2 // #2...

( SELECT * FROM v1 WHERE time >= ... AND time < ... )
UNION ALL
( SELECT * FROM v2 WHERE time >= ... AND time < ... )

- Stephen Quan

2

我不了解Postgres，但是一些关系型数据库在使用索引时处理比较运算符的能力不如BETWEEN。因此，我建议尝试使用BETWEEN。

SELECT ... FROM v WHERE time BETWEEN ... AND ...

- Dimitri

1

一种可能的方法是在每次调用时动态发出新的SQL，而不是创建视图，并将where子句集成到联合查询的每个SELECT中。

SELECT time, etc. FROM t1
    WHERE time >= ... AND time < ...
UNION ALL
SELECT time, etc. FROM t2
    WHERE time >= ... AND time < ...

编辑：

你能使用参数化函数吗？

CREATE OR REPLACE FUNCTION CallMyView(t1 date, t2 date)
RETURNS TABLE(d date, etc.)
AS $$
    BEGIN
        RETURN QUERY
            SELECT time, etc. FROM t1
                WHERE time >= t1 AND time < t2
            UNION ALL
            SELECT time, etc. FROM t2
                WHERE time >= t1 AND time < t2;
    END;
$$ LANGUAGE plpgsql;

调用

SELECT * FROM CallMyView(..., ...);

- Olivier Jacot-Descombes

2

这对我不起作用，我肯定需要“一种视图来统治它们所有”。 :) - Mladen Jablanović

1

将这两个表合并。添加一列以指示原始表格。如果必要，可以使用仅选择相关部分的视图替换原始表格名称。问题解决！

研究超类/子类数据库设计模式可能对您有所帮助。

- ErikE

直到实际尝试之前，我并不相信它会有所帮助。现在，当WHERE条件涉及来自同一张表的同一列时，查询速度要快得多。感谢这个技巧！ - Ondřej Bouda

0

在11g上遇到了相同的情况：

情况1：

CREATE VIEW v AS
  SELECT time, etc. FROM t1 // #1...

以下查询运行速度快，计划看起来没问题：

SELECT ... FROM v WHERE time >= ... AND time < ...

场景2：

CREATE VIEW v AS
  SELECT time, etc. FROM t2 // #2...

以下查询运行速度快，计划看起来没问题：

SELECT ... FROM v WHERE time >= ... AND time < ...

场景3，使用UNION ALL：

CREATE VIEW v AS
  SELECT time, etc. FROM t1 // #1...
  UNION ALL
  SELECT time, etc. FROM t2 // #2...

以下代码运行缓慢。计划将t1和t2（也是视图）拆分并组装为大量的联合系列。虽然时间过滤器在各个组件上被正确应用，但仍然非常缓慢：

SELECT ... FROM v WHERE time >= ... AND time < ...

我本来会很高兴只要得到一个在t1加上t2的范围内的时间，但实际上它超过了两倍。在这种情况下，添加parallel提示对我起了作用。它重新安排了所有内容，制定了更好的计划：

SELECT /*+ parallel */ ... FROM v WHERE time >= ... AND time < ...

- Glenn

0

尝试使用UNION DISTINCT而不是UNION ALL来创建您的视图。看看它是否会产生错误的结果。看看它是否能提高性能。

如果它产生了错误的结果，请尝试将SQL操作映射回关系操作。关系的元素始终是不同的。您的模型可能存在根本性的问题。

我对您展示的查询计划中的LEFT JOINS深表怀疑。为了获取您似乎正在选择的结果，执行LEFT JOINS是不必要的。

- Walter Mitty

-3

我认为我的评论点数不够，所以我将其作为答案发布

我不知道PostgreSQL在幕后是如何工作的，如果它是Oracle，你可能会有一些线索，那么这里是Oracle的工作方式。

您的 UNION ALL 视图速度较慢，因为在幕后， SELECT #1 和 #2 的记录首先组合在一个临时表中，该表是即时创建的，然后在此临时表上执行 SELECT ... FROM v WHERE time >= ... AND time < ... 。由于 #1 和 #2 都被索引，因此它们单独工作得更快，但是这个临时表没有索引（当然），最终记录是从这个临时表中选择的，因此响应速度较慢。

现在，至少我看不到任何方法可以使其更快+视图+非物化

除了显式运行SELECT #1和#2并将它们UNION起来之外，使其更快的一种方法是在应用程序编程语言（如果有的话）中使用存储过程或函数，在此过程中，您对每个索引表进行单独调用，然后组合结果，这不像SELECT ... FROM v WHERE time >= ... AND time < ...那样简单 :(

- bjan

我怀疑这里不是这种情况，因为从执行查询计划“v”中可以看出，两个子查询都受到“time”字段（julia）的限制，所以我非常确定没有创建一个巨大的临时表，然后再将“time”约束应用于其上。 - Mladen Jablanović

我相信没有大型临时表，但是只有DBA或者对背后情况有确切了解的人才能确认这一点。让答案自己揭晓，它将准确解释查询V为什么需要时间。 - bjan

Oracle >= 8i使用成本优化器（默认）通常不会按照您的要求执行。只有在优化器认为这是最佳/唯一选项时，它才会执行，但这种情况并不经常发生。 - gpeche

@gpeche，我明确提到我无法发表评论，所以将其发布为答案，这意味着它不是一个确切的答案。我在“Oracle would work”中使用了“would”的意思并不总是，你也提到了同样的事情“does NOT usually”。我在1月30日发布了我的答案（评论），对于一个有200赏金的问题来说，延迟了3天！这就是为什么我试图给@Mladen一个提示（我认为除非他处于危急情况，否则没有人会给出200赏金），但现在看起来我不应该这样做。只有当我100％确定时，我才会发布一个答案。让我们等待一个确切的答案。 - bjan

@MladenJablanović：第1部分：与v1和v2相比，v的执行计划包括额外的成本，如排序、合并右连接、子查询扫描、哈希、哈希左连接、嵌套循环左连接、追加，因此很明显这个查询由于这些额外的成本而变慢。现在的问题是为什么会生成这样的执行计划，这至少对于给定的查询来说是不必要的？可能是优化器的启发式算法，也可能是80:20规则或其他原因... - bjan

@MladenJablanović：第二部分：这在9.1文档中并不清楚。即使我在The design and implementation of the POSTGRES query optimizer和The PostgreSQL Optimizer Exposed的第一次查看中也没有找到它。希望你能从这些来源中找到一些线索，并自己得到确切的答案 :) - bjan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- maniek · Accepted Answer

这似乎是飞行员错误的情况。"v"查询计划至少从5个不同的表中选择。

现在，您确定连接到正确的数据库吗？也许有一些奇怪的search_path设置？也许t1和t2实际上是视图（可能在不同的模式中）？也许您正在从错误的视图中选择？

在澄清后进行编辑：

您正在使用一个相当新的功能，称为“连接删除”：http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Join_Removal

http://rhaas.blogspot.com/2010/06/why-join-removal-is-cool.html

看起来当涉及到union all时，该功能不会启动。您可能需要重写视图，仅使用所需的两个表。

另一个编辑：

您似乎正在使用聚合函数（例如"select count(*) from v" vs. "select * from v"），在连接删除面前可能会得到完全不同的计划。我想如果您发布实际的查询、视图和表定义以及使用的计划，我们才能更进一步……