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