Re: Views- Advantages and Disadvantages

On Thu, 10 May 2007 00:06:06 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)kgb(dot)une(dot)edu(dot)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 : :
+---------------------------------------+-----------------+