当前位置： 文江博客话题详情

“UNION ALL”查询速度慢看法

发布于 2024-12-29 13:09:44 字数 4489 浏览 4 评论 0原文

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

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

问题是表单的选择

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

执行速度非常慢。

SELECT #1 和 #2 都相当快，索引正确等等：当我创建视图 v1 和 v2 时，如下所示：

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 is <代码>时间。

原文

I have a DB view which basically consists of two SELECT queries with UNION ALL, like this:

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

The problem is that selects of the form

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

perform really really slow on it.

Both SELECT #1 and #2 are decently fast, properly indexed and so on: when I create views v1 and v2 like:

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

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

And the same SELECT, with same WHERE condition as the above works OK on them individually.

Any ideas about where might be the problem and how to solve it?

(Just to mention, it's one of the recent Postgres versions.)

Edit: Adding anonymized query plans (thaks to @filiprem for the link to an awesome tool):

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 is time.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（8）

一笑百媚生 2025-01-05 13:09:44

这似乎是飞行员失误造成的。 “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”与“select * from v”），在连接删除时可能会得到截然不同的计划。我想如果您不发布实际的查询、视图和表定义以及使用的计划，我们就不会走得太远......

This seems to be a case of a pilot error. The "v" query plan selects from at least 5 different tables.

Now, Are You sure You are connected to the right database? Maybe there are some funky search_path settings? Maybe t1 and t2 are actually views (possibly in a different schema)? Maybe You are somehow selecting from the wrong view?

Edited after clarification:

You are using a quite new feature called "join removal" : 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

It appears that the feature does not kick in when union all is involved. You probably have to rewrite the view using only the required two tables.

another edit:
You appear to be using an aggregate (like "select count(*) from v" vs. "select * from v"), which could get vastly different plans in face of join removal. I guess we won't get very far without You posting the actual queries, view and table definitions and plans used...

回复 原文
余生共白头 2025-01-05 13:09:44

我相信您的查询的执行类似于：

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

优化器难以优化。即，它在应用 WHERE 子句之前先执行 UNION ALL，但是，您希望它在 beforeWHERE 子句> UNION ALL。

您不能将 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 子句，那么，也许您可​​以保留两个视图并使用 WHERE 子句执行 UNION ALL当你需要它们时：

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 < ... )

I believe your query is being executed similar to:

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

which the optimizer is having difficulty optimizing. i.e. it's doing the UNION ALL first before applying the WHERE clause but, you wish it to apply the WHERE clause before the UNION ALL.

Couldn't you put your WHERE clause in the 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 < ... )

Alternatively if the view cannot have the WHERE clause, then, perhaps you can keep to the two views and do the UNION ALL with the WHERE clause when you need them:

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 < ... )
回复 原文
世界如花海般美丽 2025-01-05 13:09:44

我不知道 Postgres，但在索引情况下，一些 RMDB 处理比较运算符的情况比 BETWEEN 更糟糕。
我会尝试使用 BETWEEN 。

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

I do not know Postgres, but some RMDBs handle comparison operators worse than BETWEEN in case of indexes.
I would make an attempt using BETWEEN.

SELECT ... FROM v WHERE time BETWEEN ... AND ...
回复 原文
痴梦一场 2025-01-05 13:09:44

一种可能性是在每次调用时动态发出新的 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(..., ...);

A possibility would be to issue a new SQL dynamically at each call instead of creating a view and to integrate the where clause in each SELECT of the union query

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

EDIT:

Can you use a parametrized function?

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;

Call

SELECT * FROM CallMyView(..., ...);
回复 原文
薄荷港 2025-01-05 13:09:44

合并两个表。添加一列来指示原始表。如有必要，请将原始表名称替换为仅选择相关部分的视图。问题解决了！

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

Combine the two tables. Add a column to indicate original table. If necessary, replace the original table names with views that select just the relevant part. Problem solved!

Looking into the superclass/subclass db design pattern could be of use to you.

回复 原文
聆听风音 2025-01-05 13:09:44

尝试使用 UNION DISTINCT 而不是 UNION ALL 创建视图。看看它是否给出错误的结果。看看它是否能提供更快的性能。

如果它给出错误的结果，请尝试将表上的 SQL 操作映射回关系上的关系操作。关系的要素总是不同的。您的模型可能存在根本性错误。

我对您显示的查询计划中的 LEFT JOINS 深表怀疑。不必执行 LEFT JOINS 即可获得您似乎选择的结果。

Try creating your view using UNION DISTINCT instead of UNION ALL. See if it gives wrong results. See if it gives faster performance.

If it gives wrong results, try and map your SQL operations on tables back to relational operations on relations. The elements of relations are always distinct. There may be somthing fundamentally wrong with your model.

I am deeply suspicious of the LEFT JOINS in the query plan you showed. It shouldn't be necessary to perform LEFT JOINS in order to get the results you appear to be selecting.

回复 原文
逆光飞翔i 2025-01-05 13:09:44

在 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...

下面运行缓慢。 Plan 分解了 t1 和 t2（这也是视图）并将它们组装为一个大系列的联合。时间过滤器已正确应用于各个组件，但仍然非常慢：

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

我很高兴能获得 t1 加 t2 的大概时间，但它多了一倍多。在这种情况下，添加并行提示对我来说很有效。它把一切重新安排成一个更好的计划：

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

Encountered same scenario on 11g:

Scenario 1:

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

The following query runs fast, plan looks okay:

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

Scenario 2:

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

The following query runs fast, plan looks okay:

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

Scenario 3, with UNION ALL:

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

The following runs slow. Plan breaks apart t1 and t2 (which were also views) and assembles them as a big series of unions. The time filters are being applied properly on the individual components, but it is still very slow:

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

I would have been happy to just get a time in the ballpark of t1 plus t2, but it was more than double. Adding the parallel hint did the trick for me in this case. It re-arranged everything into a better plan:

SELECT /*+ parallel */ ... FROM v WHERE time >= ... AND time < ...
回复 原文
无法言说的痛 2025-01-05 13:09:44

我认为我没有太多观点可以将其作为评论发布，因此我将其作为答案发布

我认为我不知道 PostgreSQL 在幕后是如何工作的您可能会知道它是否是 Oracle，所以这就是 Oracle 的工作方式。

您的 UNION ALL 视图速度较慢，因为在幕后，来自 SELECT #1 的记录strong> 和 #2 合并在临时表中首先，这是动态创建的，然后是 SELECT ... FROM v WHERE time >= ... AND time < ... 在此临时表上执行。由于 #1 和 #2 都已建立索引，因此它们按预期单独工作得更快，但该临时表没有建立索引（当然）并且正在选择最终记录从这个临时表中，因此导致响应速度变慢。

现在，至少，

除了运行 SELECT #1 和 #2 和 UNION 之外，我没有看到任何方法可以让它更快 + 查看 + 非物化的一种方式明确地，为了使其更快，可以使用应用程序编程语言中的存储过程或函数（如果是这种情况），并且在此过程中，您对每个索引表进行单独调用，然后合并结果，这不是就像SELECT ... FROM v WHERE 时间一样简单>= ... 且时间 < ... :(

I think i don't have much points to post it as comments so i am posting it as an answer

I don't know how PostgreSQL works behind the scene, i think you may get a clue if it would have been Oracle, so it is here how Oracle would work

Your UNION ALL view is slower because, behind the scene, records from both SELECT #1 and #2 are combined in a temporary table first, which is created on the fly, and then your SELECT ... FROM v WHERE time >= ... AND time < ... is executed on this temporary table. Since both #1 and #2 are indexed so they are working faster individually as expected, but this temporary table is not indexed (of course) and the final records are being selected from this temporary table so resulting in a slower response.

Now, at least, i don't see any way to have it faster + view + non-materialized

One way, other than running SELECT #1 and #2 and UNION them explicitly, to make it faster would be to use a stored procedure or a function in your application programming language (if it is the case), and in this procedure you make separate calls to each indexed table and then combine results, which is not as simple as SELECT ... FROM v WHERE time >= ... AND time < ... :(

回复 原文
~没有更多了~

关于作者

左秋

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

牛↙奶布丁

文章 0 评论 0

COSO

文章 0 评论 0

落叶

文章 0 评论 0

暗地喜欢

文章 0 评论 0

qq_i8qOEG

文章 0 评论 0

qq_Wl4Sbi

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文