Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B2AA22E0045 for ; Tue, 26 Feb 2008 12:49:28 -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 65801-10 for ; Tue, 26 Feb 2008 12:49:22 -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.183]) by postgresql.org (Postfix) with ESMTP id A79932E0043 for ; Tue, 26 Feb 2008 12:49:22 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so2565288waf.5 for ; Tue, 26 Feb 2008 08:49:21 -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=UIk5TyTREkIAGa9h3nNNnwdrsSL6Joc+3FcERDjMycc=; b=EC95XydoOCvY7pRpjNSTcyN7mmlEfwyAQa/DlG6iSZAK8c7yctuRZ18w/gQ9nsMugVaBH09VFudmjY8bJhJxoKgILJg8tejZdRhCu4Js5U9LMQbmHHRBZ8PNPaN1Oe7KNuDu9NtONX50LnMF0lN6iavXegJSTk0tP/xW/YE7CVc= 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=l+BKtz26Rv9P21ioeV81UC56dkmSwuVb/JVEcCxJGxKDXuK5wWo1at0poaypci+7AQJgpLmbffrUzpT+Z15IpHPjdk1H6Q4ZSKkcBsA4yZj7XksyoIAlPs1vFDTv7p5dakPAtQG4gOlS604s1KFFSyh5JKD1yzHgani7vtJzev8= Received: by 10.114.15.1 with SMTP id 1mr5754521wao.27.1204044561900; Tue, 26 Feb 2008 08:49:21 -0800 (PST) Received: by 10.114.176.19 with HTTP; Tue, 26 Feb 2008 08:49:21 -0800 (PST) Message-ID: Date: Tue, 26 Feb 2008 11:49:21 -0500 From: "Kynn Jones" To: Matthew Subject: Re: Q on views and performance Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5183_29654773.1204044561887" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/344 X-Sequence-Number: 28886 ------=_Part_5183_29654773.1204044561887 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Mon, Feb 25, 2008 at 11:56 AM, Matthew wrote: > On Mon, 25 Feb 2008, Kynn Jones wrote: > > This is just GREAT!!! It fits the problem to a tee. > > It makes the queries quick then? It is good that you ask. Clearly you know the story: a brilliant-sounding optimization that in practice has only a small effect at best... I'm totally puzzled. It makes absolutely no sense to me... For my analysis, in addition to creating the index on (type, zipk) that you suggested, I also added an extra column to T containing a random integer in the range 0..99, and created an index on this, so that I could produce a totally "shuffled clustering". I compared the performance in going from a randomly-clustered table to a (type, zipk)-clustered table, and the output of EXPLAIN was encouraging, but when I ran the actual queries under EXPLAIN ANALYZE the difference in execution time was negligible. Live and learn! Actually, what's driving me absolutely insane is the documentation for EXPLAIN and for Pg's query planning in general. I've read the docs (in particular the chapter on performance), but I still can't make any sense of EXPLAINs results, so I can't begin to understand why optimizations like the one you suggested turned out to be ineffective. For example, the first lines of two recent EXPLAIN ANALYZE outputs are Nested Loop Left Join (cost=58.00..1154.22 rows=626 width=26) (actual time= 1.462..26.494 rows=2240 loops=1) Merge Left Join (cost=33970.96..34887.69 rows=58739 width=26) (actual time= 106.961..126.589 rows=7042 loops=1) Actual runtimes are 27ms and 128ms. The ratio 128/27 is much smaller than one would expect from the relative costs of the two queries. It looks like there is no proportionality at all between the estimated costs and actual running time... (BTW, all these runs of EXPLAIN were done after calls to VACUUM ANALYZE.) This is one of the many things I don't understand about this case... What I would like to be able to do is to at least make enough sense of query plans to determine whether they are reasonable or not. This requires knowing the algorithms behind each type of query tree node, but I have not found this info... On the positive side, in the course of all this analysis I must have done *something* to improve the performance, because now even the unoptimized queries are running pretty fast (e.g. queries that used to take about 1.5seconds are now taking 130ms). But unfortunately I don't know what was it that I did to bring this speed-up about! Anyway, be that as it may, thank you very much for your suggestion. Kynn ------=_Part_5183_29654773.1204044561887 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On Mon, Feb 25, 2008 at 11:56 AM, Matthew <matthew@flymine.org> wrote:
On Mon, 25 Feb 2008, Kynn Jones wrote:
> This is just GREAT!!!  It fits the problem to a tee.

It makes the queries quick then?

It is good that you ask.  Clearly you know the story: a brilliant-sounding optimization that in practice has only a small effect at best...

I'm totally puzzled.  It makes absolutely no sense to me...

For my analysis, in addition to creating the index on (type, zipk) that you suggested, I also added an extra column to T containing a random integer in the range 0..99, and created an index on this, so that I could produce a totally "shuffled clustering".  I compared the performance in going from a randomly-clustered table to a (type, zipk)-clustered table, and the output of EXPLAIN was encouraging, but when I ran the actual queries under EXPLAIN ANALYZE the difference in execution time was negligible.

Live and learn!

Actually, what's driving me absolutely insane is the documentation for EXPLAIN and for Pg's query planning in general.  I've read the docs (in particular the chapter on performance), but I still can't make any sense of EXPLAINs results, so I can't begin to understand why optimizations like the one you suggested turned out to be ineffective.  For example, the first lines of two recent EXPLAIN ANALYZE outputs are

Nested Loop Left Join  (cost=58.00..1154.22 rows=626 width=26) (actual time=1.462..26.494 rows=2240 loops=1)
Merge Left Join  (cost=33970.96..34887.69 rows=58739 width=26) (actual time=106.961..126.589 rows=7042 loops=1)

Actual runtimes are 27ms and 128ms.  The ratio 128/27 is much smaller than one would expect from the relative costs of the two queries.  It looks like there is no proportionality at all between the estimated costs and actual running time...  (BTW, all these runs of EXPLAIN were done after calls to VACUUM ANALYZE.)  This is one of the many things I don't understand about this case...

What I would like to be able to do is to at least make enough sense of query plans to determine whether they are reasonable or not.  This requires knowing the algorithms behind each type of query tree node, but I have not found this info...

On the positive side, in the course of all this analysis I must have done *something* to improve the performance, because now even the unoptimized queries are running pretty fast (e.g. queries that used to take about 1.5 seconds are now taking 130ms).  But unfortunately I don't know what was it that I did to bring this speed-up about!

Anyway, be that as it may, thank you very much for your suggestion.

Kynn

------=_Part_5183_29654773.1204044561887--