Skip site navigation (1) Skip section navigation (2)

Re: Q on views and performance

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 (view raw or flat)
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.


In response to

Responses

pgsql-performance by date

Next:From: Shane AmblerDate: 2008-02-23 07:53:43
Subject: Re: store A LOT of 3-tuples for comparisons
Previous:From: Kynn JonesDate: 2008-02-22 20:49:59
Subject: Q on views and performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group