From: fork <forkandwait@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Tuning massive UPDATES and GROUP BY's?
Date: Fri, 11 Mar 2011 19:06:39 +0000 (UTC)
Message-ID: <loom.20110311T195504-383@post.gmane.org> (raw)
References: <loom.20110310T163020-437@post.gmane.org>
<AANLkTiks9aTwVyN-JW1Ep2cVoJabBF_fZfkk_43GRcNv@mail.gmail.com>
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!
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.20110311T195504-383@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