From: Alan Hodgson <ahodgson@simkin.ca>
To: pgsql-performance@postgresql.org
Subject: Re: 121+ million record table perf problems
Date: Fri, 18 May 2007 12:08:41 -0700
Message-ID: <200705181208.41163@hal.medialogik.com> (raw)
In-Reply-To: <464DF598.20706@commandprompt.com>
References: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
<464DF598.20706@commandprompt.com>
On Friday 18 May 2007 11:51, "Joshua D. Drake" <jd@commandprompt.com> wrote:
> > The update query that started this all I had to kill after 17hours. It
> > should have updated all 121+ million records. That brought my select
> > count down to 19 minutes, but still a far cry from acceptable.
You're going to want to drop all your indexes before trying to update 121
million records. Updates in PostgreSQL are really quite slow, mostly due
to all the index updates. Drop indexes, do the updates, create a primary
key, cluster the table on that key to free up the dead space, then recreate
the rest of the indexes. That's about as fast as you can get that process.
Of course, doing anything big on one disk is also going to be slow, no
matter what you do. I don't think a table scan should take 19 minutes,
though, not for 121 million records. You should be able to get at least
60-70MB/sec out of anything modern. I can only assume your disk is
thrashing doing something else at the same time as the select.
--
"We can no more blame our loss of freedom on Congressmen than we can
prostitution on pimps. Both simply provide broker services for their
customers." -- Dr. Walter Williams
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: ahodgson@simkin.ca
Subject: Re: 121+ million record table perf problems
In-Reply-To: <200705181208.41163@hal.medialogik.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