From: hubert depesz lubaczewski <depesz@gmail.com>
To: Aaron Turner <synfinatic@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 10+hrs vs 15min because of just one index
Date: Fri, 10 Feb 2006 10:00:34 +0100
Message-ID: <9e4684ce0602100100x3d75554apb9decc3d08af52bd@mail.gmail.com> (raw)
In-Reply-To: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com>
References: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com>
On 2/10/06, Aaron Turner <synfinatic@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: depesz@gmail.com, synfinatic@gmail.com
Subject: Re: 10+hrs vs 15min because of just one index
In-Reply-To: <9e4684ce0602100100x3d75554apb9decc3d08af52bd@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox