Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0BA72133655C for ; Sat, 20 Nov 2010 00:43:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 74026-07 for ; Sat, 20 Nov 2010 04:43:20 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id A89501335B36 for ; Sat, 20 Nov 2010 00:43:20 -0400 (AST) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 3398915E4104; Sat, 20 Nov 2010 05:43:18 +0100 (CET) Received: from 89.103.152.5 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Sat, 20 Nov 2010 05:43:18 +0100 Message-ID: In-Reply-To: <1290217475-sup-5126@alvh.no-ip.org> References: <1289921093-sup-1911@alvh.no-ip.org> <1290217475-sup-5126@alvh.no-ip.org> Date: Sat, 20 Nov 2010 05:43:18 +0100 Subject: Re: autovacuum blocks the operations of other manual vacuum From: tv@fuzzy.cz To: "pgsql-performance" Cc: "kuopo" User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/324 X-Sequence-Number: 41348 > Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: >> However, when I analyze the table A, the autovacuum or vacuum on the >> table B cannot find any removable row version (the number of >> nonremoveable row versions and pages keeps increasing). After the >> analysis finishes, the search operations on the table B is still >> inefficient. If I call full vacuum right now, then I can have quick >> response time of the search operations on the table B again. Hi, I don't know how to fix the long VACUUM/ANALYZE, but have you tried to minimize the growth using HOT? HOT means that if you update only columns that are not indexed, and if the update can fit into the same page (into an update chain), this would not create a dead row. Are there any indexes on the small table? How large is it? You've mentioned there are about 2049 rows - that might be just a few pages so the indexes would not be very efficient anyway. Try to remove the indexes, and maybe create the table with a smaller fillfactor (so that there is more space for the updates). That should be much more efficient and the table should not grow. You can see if HOT works through pg_stat_all_tables view (columns n_tup_upd and n_tup_hot_upd). regards Tomas