Re: Q on views and performance

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.

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, and when it does,
I am able to find another way to write the VIEW and SELECT to recapture
the gain.

-- Dean

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.