Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 12CE264FCF3
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 19 Nov 2008 19:28:01 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 09853-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 19 Nov 2008 19:27:58 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from 27.mail-out.ovh.net (27.mail-out.ovh.net [91.121.30.210])
	by mail.postgresql.org (Postfix) with SMTP id 0F4BD64FC5A
	for <pgsql-performance@postgresql.org>;
	Wed, 19 Nov 2008 19:27:57 -0400 (AST)
Received: (qmail 11201 invoked by uid 503); 20 Nov 2008 00:44:40 -0000
Received: from b7.ovh.net (HELO mail177.ha.ovh.net) (213.186.33.57)
	by 27.mail-out.ovh.net with SMTP; 20 Nov 2008 00:44:40 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 19 Nov 2008 23:27:40 -0000
Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO soyouz)
	(88.161.102.87) by ns0.ovh.net with SMTP; 19 Nov 2008 23:27:38 -0000
To: Andrus <kobruleht2@hot.ee>, pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <gg1sv3$27fr$1@news.hub.org>
Content-Transfer-Encoding: 7bit
Date: Thu, 20 Nov 2008 00:28:25 +0100
Message-ID: <op.ukv3hnsfcigqcu@soyouz>
In-Reply-To: <gg1sv3$27fr$1@news.hub.org>
User-Agent: Opera Mail/9.62 (Linux)
X-Ovh-Tracer-Id: 11595080192427099782
X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/171
X-Sequence-Number: 31902


> Query below seems to use indexes everywhere in most optimal way.
> dokumnr column is of type int
>
> Speed of this query varies rapidly:
>
> In live db fastest response I have got is 8 seconds.
> Re-running same query  after 10 seconds may take 60 seconds.
> Re-running it again after 10 seconds may take 114 seconds.
>
> Any idea how to speed it up ?
>
> Is it possible to optimize it, will upgrading to 8.3.5 help or should I
> require to add more RAM, disk or CPU speed ?
>
> Real query contains column list instead of sum(1) used in test below.
>
> Andrus.

	Just a question, what are you doing with the 20.000 result rows ?