Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 67A3764FD87
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 24 Nov 2008 09:39:20 -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 13316-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 24 Nov 2008 09:39:18 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.gransy.com (nathalia.gransy.com [82.208.29.194])
	by mail.postgresql.org (Postfix) with ESMTP id 06EB264FD90
	for <pgsql-performance@postgresql.org>;
	Mon, 24 Nov 2008 09:39:17 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by mail.gransy.com (Postfix) with ESMTP id 5B6AE7AC13A;
	Mon, 24 Nov 2008 14:39:14 +0100 (CET)
Received: from mail.gransy.com ([127.0.0.1])
	by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id pgThL8a+o-7d; Mon, 24 Nov 2008 14:39:13 +0100 (CET)
Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100])
	by mail.gransy.com (Postfix) with ESMTP id 734097AC11C;
	Mon, 24 Nov 2008 14:39:13 +0100 (CET)
Received: from sq.gransy.com (localhost [127.0.0.1])
	by anastacia.gransy.com (Postfix) with ESMTP id D6F0C31E9C2;
	Mon, 24 Nov 2008 14:39:24 +0100 (CET)
Received: from 89.102.139.23 (SquirrelMail authenticated user tv@fuzzy.cz)
	by sq.gransy.com with HTTP; Mon, 24 Nov 2008 14:39:24 +0100 (CET)
Message-ID: <60633.89.102.139.23.1227533964.squirrel@sq.gransy.com>
In-Reply-To: <7395E52430A9420E8248D54726DC634B@andrusnotebook>
References: <gg1sv3$27fr$1@news.hub.org> <49253FE1.3070706@archonet.com>
	<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
	<op.ukw669uvcigqcu@soyouz>
	<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
	<op.ukze24t0cigqcu@soyouz>
	<E3AFB54F617D47F4BE652E85ECC67153@andrusnotebook>
	<op.uk1nbqcgcigqcu@soyouz>,
	<AD6DEEFF0CE54A3982082A2CA7C2F722@andrusnotebook>
	<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9BB@EXVMBX018-1.exch018.msoutlookonline.net>
	<8F4674DDA1F24158A615BE304697988B@andrusnotebook>
	<4929E9C4.1010706@fuzzy.cz>
	<7395E52430A9420E8248D54726DC634B@andrusnotebook>
Date: Mon, 24 Nov 2008 14:39:24 +0100 (CET)
Subject: Re: Hash join on int takes 8..114 seconds
From: tv@fuzzy.cz
To: "Andrus" <kobruleht2@hot.ee>
Cc: "Tomas Vondra" <tv@fuzzy.cz>, "Scott Carey" <scott@richrelevance.com>,
	"PFC" <lists@peufeu.com>, pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.10a
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-2
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
Importance: Normal
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/228
X-Sequence-Number: 31959

>> Given the fact that the performance issues are caused by bloated tables
>> and / or slow I/O subsystem, moving to a similar system won't help I
>> guess.
>
> I have ran VACUUM FULL ANALYZE VERBOSE
> and set MAX_FSM_PAGES = 150000
>
> So there is no any bloat except pg_shdepend indexes which should not
> affect to query speed.

OK, what was the number of unused pointer items in the VACUUM output?

The query performance is still the same as when the tables were bloated?
What are the outputs of iostat/vmstat/dstat/top when running the query?

regards
Tomas