From: fork <forkandwait@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Tuning massive UPDATES and GROUP BY's?
Date: Thu, 10 Mar 2011 18:04:39 +0000 (UTC)
Message-ID: <loom.20110310T185007-149@post.gmane.org> (raw)
References: <loom.20110310T163020-437@post.gmane.org>
<AANLkTi=L9vaRA+n=i75-NkBfbcBM--JdrBXjL1UexNcY@mail.gmail.com>
Merlin Moncure <mmoncure <at> gmail.com> writes:
> > I am loathe to create a new table from a select, since the indexes themselves
> > take a really long time to build.
>
> you are aware that updating the field for the entire table, especially
> if there is an index on it (or any field being updated), will cause
> all your indexes to be rebuilt anyways? when you update a record, it
> gets a new position in the table, and a new index entry with that
> position.
> insert/select to temp, + truncate + insert/select back is
> usually going to be faster and will save you the reindex/cluster.
> otoh, if you have foreign keys it can be a headache.
Hmph. I guess I will have to find a way to automate it, since there will be a
lot of times I want to do this.
> > As the title alludes, I will also be doing GROUP BY's on the data, and would
> > love to speed these up, mostly just for my own impatience...
>
> need to see the query here to see if you can make them go faster.
I guess I was hoping for a blog entry on general guidelines given a DB that is
really only for batch analysis versus transaction processing. Like "put all
your temp tables on a different disk" or whatever. I will post specifics later.
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: forkandwait@gmail.com
Subject: Re: Tuning massive UPDATES and GROUP BY's?
In-Reply-To: <loom.20110310T185007-149@post.gmane.org>
* 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