On Thu, 10 May 2007 00:06:06 -0400, Tom Lane <tgl@sss.pgh.pa.us> 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 : kg@kgb.une.edu.au : 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 : :
+---------------------------------------+-----------------+
