Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id CCCCE1337B93
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 10 Mar 2011 14:05:02 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 01496-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 10 Mar 2011 18:04:53 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id A0FA1133616B
	for <pgsql-performance@postgresql.org>;
	Thu, 10 Mar 2011 14:04:53 -0400 (AST)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <gcdpp-pgsql-performance@m.gmane.org>)
	id 1PxkEF-0000QN-7I
	for pgsql-performance@postgresql.org; Thu, 10 Mar 2011 19:04:51 +0100
Received: from 198.239.99.92 ([198.239.99.92])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Thu, 10 Mar 2011 19:04:51 +0100
Received: from forkandwait by 198.239.99.92 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Thu, 10 Mar 2011 19:04:51 +0100
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-performance@postgresql.org
From: fork <forkandwait@gmail.com>
Subject: Re: Tuning massive UPDATES and GROUP BY's?
Date: Thu, 10 Mar 2011 18:04:39 +0000 (UTC)
Lines: 25
Message-ID: <loom.20110310T185007-149@post.gmane.org>
References: <loom.20110310T163020-437@post.gmane.org>
	<AANLkTi=L9vaRA+n=i75-NkBfbcBM--JdrBXjL1UexNcY@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: sea.gmane.org
User-Agent: Loom/3.14 (http://gmane.org/)
X-Loom-IP: 198.239.99.92 (Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.9.2.15) Gecko/20110303 Firefox/3.6.15)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.735 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_NUMERIC_HELO=1.164,
	T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201103/110
X-Sequence-Number: 42789

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.