Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 587AB650440 for ; Fri, 21 Nov 2008 15:10:46 -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 53903-02-3 for ; Fri, 21 Nov 2008 15:10:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp-out.neti.ee (smtp-out.neti.ee [194.126.126.44]) by mail.postgresql.org (Postfix) with ESMTP id EFD4865045E for ; Fri, 21 Nov 2008 15:10:33 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by MXR-4.estpak.ee (Postfix) with ESMTP id C7F6723EAE8; Fri, 21 Nov 2008 21:10:30 +0200 (EET) X-Virus-Scanned: Debian amavisd-new at Relay4.estpak.ee Received: from smtp-out.neti.ee ([127.0.0.1]) by localhost (MXR-4.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id sXZBYnMcEE7c; Fri, 21 Nov 2008 21:10:30 +0200 (EET) Received: from Relayhost1.neti.ee (Relayhost1 [88.196.174.141]) by MXR-4.estpak.ee (Postfix) with ESMTP id 63D642249C2; Fri, 21 Nov 2008 21:10:30 +0200 (EET) X-SMTP-Auth-NETI-Businesmail: no Received: from andrusnotebook (unknown [195.50.198.194]) by Relayhost1.neti.ee (Postfix) with SMTP id F323D2A50C4; Fri, 21 Nov 2008 21:10:30 +0200 (EET) Message-ID: <8D7F64CF7F614E13AC554D6CF86F39B2@andrusnotebook> From: "Andrus" To: "Richard Huxton" , "PFC" Cc: References: <49253FE1.3070706@archonet.com> <0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook> <75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook> <4926E2B2.9050202@archonet.com> <8A78204AA6D44C04B053E6412B244E13@andrusnotebook> In-Reply-To: Subject: Re: Hash join on int takes 8..114 seconds Date: Fri, 21 Nov 2008 21:10:21 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="utf-8"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Windows Mail 6.0.6001.18000 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049 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/188 X-Sequence-Number: 31919 >> How to vacuum full pg_shdepend automatically so that other users can >> work at same time ? > > Your table is horribly bloated. > You must use VACUUM FULL + REINDEX (as superuser) on it, however > unfortunately, it is blocking. > Therefore, you should wait for sunday night to do this, when noone will > notice. Shops are closed late night for a short time, including sunday night. This time may be shorter than time required to complete VACUUM command. I discovered vacuum_cost_delay=2000 option. Will this remove blocking issue and allow vacuum full to work ? > Meanwhile, you can always VACUUM it (as superuser) and REINDEX it. I expect that autovacuum does this automatically. > And while you're at it, VACUUM FULL + reindex the entire database. > To avoid such annoyances in the future, you should ensure that autovacuum > runs properly ; you should investigate this. If you use a cron'ed VACUUM > that does not run as superuser, then it will not be able to VACUUM the > system catalogs, and the problem will come back. autovacuum is turned on in postgresql.conf file log file shows a lot of messages every day that database is vacuumed. I assume that it is running as user postgres. I do'nt understand how autovacuum can avoid this: it does not perform vacuum full so pg_shdepend ja my tables become bloated again and again. Andrus.