Re: Views- Advantages and Disadvantages

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             :                 :
+---------------------------------------+-----------------+