对“UNION ALL”的慢查询视图 [英] Slow query on "UNION ALL" view
问题描述
我有一个数据库视图，基本上包含两个 SELECT 查询 UNION ALL ，如下所示：
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...
问题是选择表格
SELECT ... FROM v WHERE time >= ... AND time < ...
表现真的很慢。
SELECT＃1和＃2都非常快，索引正确等等：当我创建视图v1和v2时：
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...
并且具有与上述相同的WHERE条件的相同SELECT工作正常他们是单独的。
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?
（只是为了提及，它是最近的Postgres版本之一。）
(Just to mention, it's one of the recent Postgres versions.)
编辑：添加匿名查询计划（请联系@filiprem获取指向一个很棒的工具的链接）：
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 时间。
推荐答案
这似乎是一个飞行员错误的情况。 v查询计划从至少5个不同的表中进行选择。
This seems to be a case of a pilot error. The "v" query plan selects from at least 5 different tables.
现在，您确定已连接到正确的数据库吗？也许有一些时髦的search_path设置？也许t1和t2实际上是视图（可能在不同的模式中）？也许你是以某种方式从错误的观点中选择的？
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?
在澄清之后编辑：
你正在使用一个相当新的称为加入删除的功能： http://wiki.postgresql.org/wiki/什么％27s_new_in_PostgreSQL_9.0＃Join_Removal
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
当涉及union all时，该功能似乎没有启动。您可能只需要使用所需的两个表重写视图。
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.
另一个编辑：
您似乎正在使用聚合（例如select count（*） ）从vvs.select * from v），这可能会在加入删除时获得截然不同的计划。我想如果没有你发布实际的查询，查看和表格定义以及使用的计划，我们就不会走得太远......
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...
这篇关于对“UNION ALL”的慢查询视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
