Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id ECB3A1337BDE
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 11 Mar 2011 15:07:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 00868-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 11 Mar 2011 19:07:03 +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 D2BCB1337BD3
	for <pgsql-performance@postgresql.org>;
	Fri, 11 Mar 2011 15:07:02 -0400 (AST)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <gcdpp-pgsql-performance@m.gmane.org>)
	id 1Py7fw-00051x-C0
	for pgsql-performance@postgresql.org; Fri, 11 Mar 2011 20:07:00 +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>; Fri, 11 Mar 2011 20:07:00 +0100
Received: from forkandwait by 198.239.99.92 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Fri, 11 Mar 2011 20:07:00 +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: Fri, 11 Mar 2011 19:06:39 +0000 (UTC)
Lines: 47
Message-ID: <loom.20110311T195504-383@post.gmane.org>
References: <loom.20110310T163020-437@post.gmane.org>
	<AANLkTiks9aTwVyN-JW1Ep2cVoJabBF_fZfkk_43GRcNv@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/122
X-Sequence-Number: 42801

Marti Raudsepp <marti <at> juffo.org> writes:

> If you don't mind long recovery times in case of a crash, set
> checkpoint_segments to ~100 and checkpoint_completion_target=0.9; this
> will improve write throughput significantly.

Sounds good.

> Also, if you don't mind CORRUPTing your database after a crash,
> setting fsync=off and full_page_writes=off gives another significant
> boost.

I probably won't do this... ;)

> UPDATE on a table with many indexes will probably be slower. If you
> want to speed up this part, use INSERT INTO x SELECT and take this
> chance to partition your table, 

Like the following?  Will it rebuild the indexes in a sensical way?

BEGIN;
CREATE TABLE tempfoo as SELECT *, foo + bar AS newcol FROM bar;
TRUNCATE foo;
ALTER TABLE foo ADD COLUMN newcol;
INSERT INTO foo SELECT * FROM tempfoo;
DROP TABLE tempfoo;
END;

> such that each individual partition
> and most indexes will fit in your cache. 

Is there a rule of thumb on tradeoffs in a partitioned table?  About half the
time, I will want to do GROUP BY's that use the partition column, but about half
the time I won't.  (I would use the partition column whatever I am most likely
to cluster by in a single big table, right?)

For example, I might intuitively partition by age5 (into 20 tables like tab00,
tab05, tab10, etc). Often a query would be "SELECT ... FROM PARENTTABLE GROUP BY
age5, race, etc", but often it would be "GROUP BY state" or whatever with no
age5 component.

I know I can experiment ;), but it takes a while to load anything, and i would
rather stand on the shoulders.

Thanks so much for all your helps!