Slow query on "UNION ALL" view
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...
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 ASSELECT time, etc. FROM t1 // #1...CREATE VIEW v2 ASSELECT time, etc. FROM t2 // #2...( SELECT * FROM v1 WHERE time >= ... AND time < ... )UNION ALL( SELECT * FROM v2 WHERE time >= ... AND time < ... )
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 ...