Re: Views- Advantages and Disadvantages

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> Two people now have stated without much qualification that views have
> some kind of associated performance (Brent Woods) or optimization
> (Dann Corbit) penalty. Where does this idea come from? Views in
> PostgreSQL are just rewritten with the view query inlined! There's
> not much overhead there AIUI.

Well, it takes some cycles to rewrite the query with the inserted
sub-select, but probably fewer than would be taken to parse and analyze
the query if it had been written out longhand (the stored form of the
view has already gone through parse analysis, so we don't have to repeat
that work for it). AFAIK that's at worst a wash. 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.

regards, tom lane