Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EFCE164FDA8 for ; Wed, 12 Nov 2008 16:54:45 -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 10494-10 for ; Wed, 12 Nov 2008 16:54:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id B0B9664FD2A for ; Wed, 12 Nov 2008 16:54:38 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id mACKs9if020664; Wed, 12 Nov 2008 15:54:09 -0500 (EST) To: Richard Huxton cc: firerox@centrum.cz, tv@fuzzy.cz, pgsql-performance@postgresql.org Subject: Re: slow full table update In-reply-to: <491B1653.3040600@archonet.com> References: <200811101729.14977@centrum.cz> <200811101730.11057@centrum.cz> <200811101730.15902@centrum.cz> <61593.89.102.139.23.1226335300.squirrel@sq.gransy.com> <200811121725.30238@centrum.cz> <63509.89.102.139.23.1226508479.squirrel@sq.gransy.com> <200811121825.26487@centrum.cz> <491B1653.3040600@archonet.com> Comments: In-reply-to Richard Huxton message dated "Wed, 12 Nov 2008 17:45:55 +0000" Date: Wed, 12 Nov 2008 15:54:08 -0500 Message-ID: <20663.1226523248@sss.pgh.pa.us> From: Tom Lane 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/108 X-Sequence-Number: 31839 Richard Huxton writes: > firerox@centrum.cz wrote: >> I try explain query with this result >> for 10.000 rows > update songs set views = 0 where sid > 20000 and sid < 30000 >> >> Bitmap Heap Scan on songs (cost=151.59..6814.29 rows=8931 width=526) (actual time=4.848..167.855 rows=8945 loops=1) > This query says t is taking 167 milli-seconds, not 10 minutes as your > first message said. Is this query actually slow? The explain plan tree only shows the time to fetch/compute the new rows, not to actually perform the update, update indexes, or fire triggers. If there is a big discrepancy then the extra time must be going into one of those steps. 8.1 does show trigger execution time separately, so the most obvious problem (unindexed foreign key reference) seems to be excluded, unless the OP just snipped that part of the output ... regards, tom lane