Re: Q on views and performance

On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) <
postgresql(at)ultimeth(dot)com> wrote:

> On 2008-02-22 12:49, Kynn Jones wrote:
> > Of course, I expect that using views V<int1> and V<int2>... would
> > result in a loss in performance relative to a version that used bona
> > fide tables T<int1> and T<int2>. My question is, how can I minimize
> > this performance loss?
>
> That used to be my thoughts too, but I have found over the years that
> the PostgreSQL execution planner is able to "flatten" SELECTs using
> VIEWs, ALMOST ALWAYS in a way that does not adversely affect
> performance, and often gives an IMPROVEMENT in performance, probably
> because by using VIEWs I am stating the query problem in a better way
> than if I try to guess the best way to optimize a SELECT.

Well, the last consideration you mention there does not apply to the two
alternatives I'm comparing because they differ only in that one uses views
V1, V2, V3, ... , V100 where the other one uses the corresponding tables T1,
T2, T3, ... , T100, so the query statements would be identical in both
cases.

> I have at least a 10:1 ratio of VIEWs to TABLEs. Occasionally, with
> some query that is slow, I will try to rewrite it without VIEWs. This
> ALMOST NEVER results in an improvement in performance...

That's truly amazing! Just to make sure I get you right, you're saying that
when you replace a view by its equivalent table you see no performance gain?
How could it be? With views every query entails the additional work of
searching the underlying tables for the records that make up the views...

OK, if I think a bit more about it I suppose that a view could be
implemented for performance as a special sort of table consisting of a
single column of pointers to the "true" records, in which case using views
would entail only the cost of this indirection, and not the cost of a
search... (And also the cost of maintaining this pointer table, if the
underlying tables are mutable.) So I guess views could be implemented in
such a way that the difference in SELECT performance relative to replacing
them with tables would be negligible...

Anyway, your post once again reminded me of awesomeness of PostgreSQL.
Props to the developers!

kynn