[PERFORM] Union within View vs.Union of Views

2007-11-03 Thread Jeff Larsen
Consider: CREATE VIEW_X AS SELECT UNION ALL SELECT UNION ALL SELECT ; versus CREATE VIEW_A AS SELECT ; CREATE VIEW_B AS SELECT ; CREATE VIEW_C AS SELECT ; where , and are each somewhat complex with several joins, but utilizing different tables for each of A, B and C. Performance on SELE

Re: [PERFORM] Union within View vs.Union of Views

2007-11-03 Thread Mark Mielke
Jeff Larsen wrote: 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 oppo

Re: [PERFORM] Union within View vs.Union of Views

2007-11-03 Thread Heikki Linnakangas
Jeff Larsen wrote: Performance on SELECT * from VIEW_X WHERE ; was absolutely terrible. But performance on SELECT * from VIEW_A WHERE UNION ALL SELECT * from VIEW_B WHERE UNION ALL SELECT * from VIEW_C WHERE ; was nice and speedy, perhaps 100 times faster than the first. If it's possible t

[PERFORM] "MixedCase sensitive quoted" names

2007-11-03 Thread Whatever Deep
Hello, This question may sound dumb, but I would like to know if using "MixedCase sensitive quoted" names instead of lowercase names for object names has any negative hit to the database performance. Thanks! ---(end of broadcast)--- TIP 4: Have you

Re: [PERFORM] Union within View vs.Union of Views

2007-11-03 Thread Tom Lane
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > Jeff Larsen wrote: >> If it's possible to consider this abstractly, is there any particular >> reason why there is such a vast difference in performance? > That's surprising. The planner knows how to push down WHERE conditions > to parts of a UNION

Re: [PERFORM] "MixedCase sensitive quoted" names

2007-11-03 Thread Tom Lane
"Whatever Deep" <[EMAIL PROTECTED]> writes: > This question may sound dumb, but I would like to know if using > "MixedCase sensitive quoted" names instead of lowercase names for > object names has any negative hit to the database performance. I can't imagine you could measure any performance diffe

Re: [PERFORM] Union within View vs.Union of Views

2007-11-03 Thread Jeff Larsen
[[ Oops, still not used to gmail. Accidentally posted this straight to Tom and not the list]] > There are a bunch of special cases where it can't do that, though. > Look into src/backend/optimizer/path/allpaths.c, particularly > subquery_is_pushdown_safe: My case probably fits the 'special case'