Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id C77772E00C4
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 22 Feb 2008 22:17:38 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 34444-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 22 Feb 2008 22:17:36 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id 6E3D22E00C1
	for <pgsql-performance@postgresql.org>;
	Fri, 22 Feb 2008 22:17:36 -0400 (AST)
Received: from [192.168.42.79] (207.155.204.150.ptr.us.xo.net
	[207.155.204.150])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 55A4D2FE81D
	for <pgsql-performance@postgresql.org>;
	Fri, 22 Feb 2008 17:48:49 -0800 (PST)
Message-ID: <47BF7B80.1040309@ultimeth.com>
Date: Fri, 22 Feb 2008 17:48:48 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Q on views and performance
References: <c2350ba40802221249h37913d0cyaa6387a93c55fe69@mail.gmail.com>
In-Reply-To: <c2350ba40802221249h37913d0cyaa6387a93c55fe69@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/289
X-Sequence-Number: 28831

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.