Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A7F4364FD2A for ; Wed, 12 Nov 2008 20:13:31 -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 47832-06 for ; Wed, 12 Nov 2008 20:13:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (unknown [82.208.29.208]) by mail.postgresql.org (Postfix) with ESMTP id 59BC6650225 for ; Wed, 12 Nov 2008 20:13:21 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 58B477AC019; Thu, 13 Nov 2008 01:13:19 +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 JQkNzjcsULBi; Thu, 13 Nov 2008 01:13:19 +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 23A8A7AC008; Thu, 13 Nov 2008 01:13:19 +0100 (CET) Message-ID: <491B7123.3090309@fuzzy.cz> Date: Thu, 13 Nov 2008 01:13:23 +0100 From: Tomas Vondra User-Agent: Thunderbird 2.0.0.9 (X11/20080213) MIME-Version: 1.0 To: Scott Marlowe CC: firerox@centrum.cz, pgsql-performance Subject: Re: slow full table update References: <200811101728.3048@centrum.cz> <200811101729.14977@centrum.cz> <200811101730.15902@centrum.cz> <200811122347.21602@centrum.cz> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200811/115 X-Sequence-Number: 31846 > This is the critical point. You have this line: > > There were 132969 unused item pointers. > > Which says there's 132k or so dead rows in your table. Which means > vacuum / autovacuum isn't keeping up. Did you try and stop the update > several times? Each time it starts then gets killed it creates dead > rows. Try to run just ANALYZE on the table and then run the SELECT relpages, reltuples FROM pg_class WHERE relname = 'table' again. It should report about 20k of pages, i.e. 160MB. That might slow the things down ;-) > Try doing a vacuum full followed by a reindex OR a cluster on this > table and see if that helps. Well, maybe the vacuum will fix the problem - have you executed the query that took 167ms (according to the explain analyze output posted by you) over a clean table? But I doubt the growth from 6.000 to 20.000 alone might cause degradation from 170ms to several minutes ... regards Tomas