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! -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
