Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E3D0064FD39 for ; Sun, 23 Nov 2008 14:47:24 -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 81465-07 for ; Sun, 23 Nov 2008 14:47:22 -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 F101564FCDD for ; Sun, 23 Nov 2008 14:47:21 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 6BF2A7AC013 for ; Sun, 23 Nov 2008 19:47:15 +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 A-g39OZr3cZu for ; Sun, 23 Nov 2008 19:47:15 +0100 (CET) Received: from [192.168.1.1] (ip-89-102-139-23.karneval.cz [89.102.139.23]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTP id 2A1787AC012 for ; Sun, 23 Nov 2008 19:47:15 +0100 (CET) Message-ID: <4929A533.5030203@fuzzy.cz> Date: Sun, 23 Nov 2008 19:47:15 +0100 From: Tomas Vondra User-Agent: Thunderbird 2.0.0.9 (X11/20080213) MIME-Version: 1.0 CC: pgsql-performance@postgresql.org Subject: Re: Hash join on int takes 8..114 seconds References: <49253FE1.3070706@archonet.com> <0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook> <75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.581 tagged_above=0 required=5 tests=MISSING_HEADERS=1.581 X-Spam-Level: * X-Archive-Number: 200811/218 X-Sequence-Number: 31949 > Risky to try in prod server. Requires creating randomly distributed > product_id testcase to measure > difference. > > What should I do next? I guess you have backups - take them, restore the database on a different machine (preferably with the same / similar hw config) and tune the queries on it. After restoring all the tables / indexes will be 'clean' (not bloated), so you'll see if performing VACUUM FULL / CLUSTER is the right solution or if you have to change the application internals. Sure, the times will be slightly different but the performance problems should remain the same. regards Tomas