On Thu, 10 May 2007 00:06:06 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > ... I suspect the > important point here is that if you have > > CREATE VIEW v AS SELECT sis, boom, bah ... > > then > > SELECT ... FROM ..., v, ... > > will be rewritten to the same parsetree as if you'd written > > SELECT ... FROM ..., (SELECT sis, boom, bah ...) AS v, ... > > and then everything hinges on what the planner is able to do with that. > In simple cases the planner is able to "flatten" the sub-SELECT together > with the outer query and you get a reasonable plan, but if it fails to > do that then you might get a pretty bad plan. I think some people might > complain that "views are slow" because they compared the view to a case > that is not exactly the above mechanical transformation, but one where > they had applied some simplification/optimization that was obvious to > them but not to the planner.
I think I have a classic example of this (for older pg versions anyway) - we have a lot of views with a left join in them and performance is awful when the view is inner joined to another table. "select v.* from v where key_of_1st_table = blah" takes a small fraction of a second. "select v.* from v join analysed_tmp_containing_only_blah using (key_of_1st_table)" takes a coffee and a doughnut. The outer join reordering in 8.2 should solve this situation though? klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : [EMAIL PROTECTED] : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
