Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2C8AE2E00F6 for ; Sat, 23 Feb 2008 09:07: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 66648-01 for ; Sat, 23 Feb 2008 09:07:37 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by postgresql.org (Postfix) with ESMTP id 16BFC2E002E for ; Sat, 23 Feb 2008 09:07:36 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so938751waf.5 for ; Sat, 23 Feb 2008 05:07:35 -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=gZyDTgDjhLzcJxr28iViA5NEPN7fYDnfDFvNWem2iN8=; b=I1d7k1VCPpKIXlMicLza+3jZDFqclTiakMNYeNqKyjlfD/5pTgTua49pzzGMexXkIhJyB/CZ3E+FsKkqCrPy11K1/Or2CzPGnaCxW7G+r1Jx6amJlf3lRvW949WjfMTaWhptIfk5+vRZ64Vp/9KXnXeWtYYENE9EjTm6svo1O6Q= 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=KQOZxEUN4wP7nD4YdaxjUuTAr4yj8ZAeym4Eyk7JUzQbzmkxRTI91VGD6SVr1iKpL4Jw4H0jVxowVFkOXC1RQOXFG1n/Z8C7UkBwoXBbTJkwKnxDdUsNzRhJv+o/VYqG/Qjk4kZkS2t0LdQ2OC+xhWgBJYZkk9YbovP6nY1m0Zw= Received: by 10.114.146.1 with SMTP id t1mr481385wad.20.1203772055560; Sat, 23 Feb 2008 05:07:35 -0800 (PST) Received: by 10.114.176.19 with HTTP; Sat, 23 Feb 2008 05:07:35 -0800 (PST) Message-ID: Date: Sat, 23 Feb 2008 08:07: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_5301_24452919.1203772055555" References: <47BF7B80.1040309@ultimeth.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/292 X-Sequence-Number: 28834 ------=_Part_5301_24452919.1203772055555 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. Well, the last consideration you mention there does not apply to the two alternatives I'm comparing because they differ only in that one uses views V1, V2, V3, ... , V100 where the other one uses the corresponding tables T1, T2, T3, ... , T100, so the query statements would be identical in both cases. > 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... That's truly amazing! Just to make sure I get you right, you're saying that when you replace a view by its equivalent table you see no performance gain? How could it be? With views every query entails the additional work of searching the underlying tables for the records that make up the views... OK, if I think a bit more about it I suppose that a view could be implemented for performance as a special sort of table consisting of a single column of pointers to the "true" records, in which case using views would entail only the cost of this indirection, and not the cost of a search... (And also the cost of maintaining this pointer table, if the underlying tables are mutable.) So I guess views could be implemented in such a way that the difference in SELECT performance relative to replacing them with tables would be negligible... Anyway, your post once again reminded me of awesomeness of PostgreSQL. Props to the developers! kynn ------=_Part_5301_24452919.1203772055555 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.

Well, the last consideration you mention there does not apply to the two alternatives I'm comparing because they differ only in that one uses views V1, V2, V3, ... , V100 where the other one uses the corresponding tables T1, T2, T3, ... , T100, so the query statements would be identical in both cases.
 
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...

That's truly amazing!  Just to make sure I get you right, you're saying that when you replace a view by its equivalent table you see no performance gain?  How could it be?  With views every query entails the additional work of searching the underlying tables for the records that make up the views...

OK, if I think a bit more about it I suppose that a view could be implemented for performance as a special sort of table consisting of a single column of pointers to the "true" records, in which case using views would entail only the cost of this indirection, and not the cost of a search...  (And also the cost of maintaining this pointer table, if the underlying tables are mutable.)  So I guess views could be implemented in such a way that the difference in SELECT performance relative to replacing them with tables would be negligible...

Anyway, your post once again reminded me of awesomeness of PostgreSQL.  Props to the developers!

kynn

------=_Part_5301_24452919.1203772055555--