From: | "Dean Gibson (DB Administrator)" <postgresql(at)ultimeth(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Q on views and performance |
Date: | 2008-02-23 01:48:48 |
Message-ID: | 47BF7B80.1040309@ultimeth.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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.
From | Date | Subject | |
---|---|---|---|
Next Message | Shane Ambler | 2008-02-23 07:53:43 | Re: store A LOT of 3-tuples for comparisons |
Previous Message | Kynn Jones | 2008-02-22 20:49:59 | Q on views and performance |