X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9BF329DCBAF for ; Fri, 10 Feb 2006 05:00:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32099-07 for ; Fri, 10 Feb 2006 05:00:35 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by postgresql.org (Postfix) with ESMTP id 93F409DC9F8 for ; Fri, 10 Feb 2006 05:00:32 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i21so310138wra for ; Fri, 10 Feb 2006 01:00:34 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=AjgsIYwmMU9lKDMijfIo1mJQxvFrWjHBDIkg4q6E0bDE7KnAU6Q45AJqAVto+18hBanmJA7ojeVLEV14zCoqC0ma1hoHzG5R3C1L7gz5AdkObGsECAy/sI62TFSMJ4CNRZYDaLNVWHzcOAbjVKVu8fl1r/S2EnJv//gw4xVbxfg= Received: by 10.64.232.20 with SMTP id e20mr392761qbh; Fri, 10 Feb 2006 01:00:34 -0800 (PST) Received: by 10.65.254.12 with HTTP; Fri, 10 Feb 2006 01:00:34 -0800 (PST) Message-ID: <9e4684ce0602100100x3d75554apb9decc3d08af52bd@mail.gmail.com> Date: Fri, 10 Feb 2006 10:00:34 +0100 From: hubert depesz lubaczewski To: Aaron Turner Subject: Re: 10+hrs vs 15min because of just one index Cc: pgsql-performance@postgresql.org In-Reply-To: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.188 required=5 tests=[AWL=0.188] X-Spam-Score: 0.188 X-Spam-Level: X-Archive-Number: 200602/96 X-Sequence-Number: 17082 On 2/10/06, Aaron Turner wrote: > So I'm trying to figure out how to optimize my PG install (8.0.3) to > get better performance without dropping one of my indexes. > Basically, I have a table of 5M records with 3 columns: > pri_key (SERIAL) > data char(48) > groupid integer > there is an additional unique index on the data column. > The problem is that when I update the groupid column for all the > records, the query takes over 10hrs (after that I just canceled the > update). Looking at iostat, top, vmstat shows I'm horribly disk IO > bound (for data not WAL, CPU 85-90% iowait) and not swapping. > Dropping the unique index on data (which isn't used in the query), for such a large update i would suggest to go with different scenario: split update into packets (10000, or 50000 rows at the time) and do: update packet vacuum table for all packets. and then reindex the table. should work much nicer. depesz