Jeff Larsen wrote:
Performance on SELECT * from VIEW_X WHERE <conditions>; was absolutely terrible. But performance on SELECT * from VIEW_A WHERE <conditions> UNION ALL SELECT * from VIEW_B WHERE <conditions> UNION ALL SELECT * from VIEW_C WHERE <conditions>; was nice and speedy, perhaps 100 times faster than the first. If it's possible to consider this abstractly, is there any particular reason why there is such a vast difference in performance? I would guess that is has something to do with how the WHERE conditions are applied to a view composed of a UNION of queries. Perhaps this is an opportunity for improvement in the code. In the first case, it's as if the server is doing the union on all rows (over 10 million altogether in my case) without filtering, then applying the conditions to the result. Maybe there is no better way.
That's surprising. The planner knows how to push down WHERE conditions to parts of a UNION ALL, and should be able to generate the same plan in both cases. Maybe it's just estimating the costs differently? Did you copy-paste all the conditions in the single WHERE clause of the slow query to all the three WHERE clauses on the separate views? Even if some of the clauses are not applicable, they might still affect the cost estimates and lead to a worse plan.
I can post query plans if anyone is interested. I haven't really learned how to make sense out of them myself yet.
Yes, please. Please post the SQL and schema as well if possible. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
