Re: Tuning massive UPDATES and GROUP BY's?

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!