X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AFFAA528D1 for ; Sun, 12 Jun 2005 14:58:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44759-01 for ; Sun, 12 Jun 2005 17:58:04 +0000 (GMT) Received: from mars.interactivemediafactory.net (mars.imfeurope.net [194.2.222.161]) by svr1.postgresql.org (Postfix) with ESMTP id E141B528AE for ; Sun, 12 Jun 2005 14:58:02 -0300 (ADT) Received: from JC-8600.directinfos.com (localhost [127.0.0.1]) by mars.interactivemediafactory.net (8.12.11/8.12.11) with ESMTP id j5CHvuD1097657; Sun, 12 Jun 2005 19:57:57 +0200 (CEST) (envelope-from jc@directinfos.com) Message-Id: <6.2.0.14.0.20050612195157.039cd170@pop.interactivemediafactory.net> X-Mailer: QUALCOMM Windows Eudora Version 6.2.0.14 Date: Sun, 12 Jun 2005 19:57:54 +0200 To: Yves Vindevogel From: Jacques Caron Subject: Re: Updates on large tables are extremely slow Cc: pgsql-performance@postgresql.org In-Reply-To: <579bc853ef3957c736c2773f13c444f7@implements.be> References: <579bc853ef3957c736c2773f13c444f7@implements.be> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/264 X-Sequence-Number: 12901 Hi, At 19:40 12/06/2005, Yves Vindevogel wrote: >Hi, > >I'm trying to update a table that has about 600.000 records. >The update query is very simple : update mytable set pagesdesc = - >pages ; > >(I use pagesdesc to avoid problems with sort that have one field in >ascending order and one in descending order. That was a problem I had a >week ago) An index on (-pages) would probably do exactly what you want without having to add another column. >The query takes about half an hour to an hour to execute. Depending on the total size of the table and associated indexes and on your exact setup (especially your hardare), this could be quite normal: the exuctor goes through all rows in the table, and for each, creates a copy with the additional column, updates indexes, and logs to WAL. You might want to look into moving your WAL files (pg_xlog) to a separate disk, increase WAL and checkpoint buffers, add more RAM, add more disks... But as I said, you might not even need to do that, just use an index on an expression... Jacques.