Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 767212E0193 for ; Sat, 23 Feb 2008 09:59:40 -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 80249-01 for ; Sat, 23 Feb 2008 09:59:37 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.238]) by postgresql.org (Postfix) with ESMTP id BBC1E2E0191 for ; Sat, 23 Feb 2008 09:59:37 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id r21so708203wxc.28 for ; Sat, 23 Feb 2008 05:59:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=ngUqroSV9IxhD8XgooG5yY7+LzRMLSdx6iQQORSIi+Q=; b=IB7qMzEGRQkJdJxjYt0JvslmzE5q6EucXm87FIsVIVbHXZCUSOAXIXgua3/ULtTnPRQzoIZ6/ZF8kX4g+Bxq+UXXY5IF01OTJi93Zuh01gASQnlh1yVf29Cyir5zceQ6cQX1IQrlCyPKgFB7QD4wIE4+LRBRg+XtV0GPsCNtGoM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=LMmULTPIE5X5T7v9t8RZ+B0Hv+3ZpapOosl+2uVzC7TBqKCL8AmCnvaUOQd/Yg8UcJa37wrL3GeCe+agpschslt/0jo81s6OUICSLBsa9U93A2j+76mZh94PoR103yrd5S4zmzVAIyfQKRECgq5ZZwuGmtA13bvXzSGgbxU1Ndk= Received: by 10.115.79.1 with SMTP id g1mr543224wal.2.1203775175768; Sat, 23 Feb 2008 05:59:35 -0800 (PST) Received: by 10.114.176.19 with HTTP; Sat, 23 Feb 2008 05:59:35 -0800 (PST) Message-ID: Date: Sat, 23 Feb 2008 08:59:35 -0500 From: "Kynn Jones" To: "Dean Gibson (DB Administrator)" Subject: Re: Q on views and performance Cc: pgsql-performance@postgresql.org In-Reply-To: <47BF7B80.1040309@ultimeth.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5508_12050791.1203775175760" References: <47BF7B80.1040309@ultimeth.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/293 X-Sequence-Number: 28835 ------=_Part_5508_12050791.1203775175760 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) < postgresql@ultimeth.com> wrote: > On 2008-02-22 12:49, Kynn Jones wrote: > > Of course, I expect that using views V and V... would > > result in a loss in performance relative to a version that used bona > > fide tables T and T. 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. Since you have experience working with views, let me ask you this. The converse strategy to the one I described originally would be to create the individual tables T1, T2, T3, ..., T100, but instead of keeping around the original (and now redundant) table T, replace it with a view V made up of the union of T1, T2, T3, ..., T100. The problem with this alternative is that one cannot index V, or define a primary key constraint for it, because it's a view. This means that a search in V, even for a primary key value, would be *have to be* very inefficient (i.e. I don't see how even the very clever PostgreSQL implementers could get around this one!), because the engine would have to search *all* the underlying tables, T1 through T100, even if it found the desired record in T1, since it has no way of knowing that the value is unique all across V. Is there a way around this? kynn ------=_Part_5508_12050791.1203775175760 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) <postgresql@ultimeth.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.

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.

Since you have experience working with views, let me ask you this.  The converse strategy to the one I described originally would be to create the individual tables T1, T2, T3, ..., T100, but instead of keeping around the original (and now redundant) table T, replace it with a view V made up of the union of T1, T2, T3, ..., T100.  The problem with this alternative is that one cannot index V, or define a primary key constraint for it, because it's a view.  This means that a search in V, even for a primary key value, would be *have to be* very inefficient (i.e. I don't see how even the very clever PostgreSQL implementers could get around this one!), because the engine would have to search *all* the underlying tables, T1 through T100, even if it found the desired record in T1, since it has no way of knowing that the value is unique all across V.

Is there a way around this?

kynn

------=_Part_5508_12050791.1203775175760--