Re: two memory-consuming postgres processes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




I created several indices for the primary table, yes. Sure I can do a table for a volatile column, but then I'll have to create a new such table for each derived column -- that's why I tried to add a column to the existing table. Yet seeing this is really slow, and I need to to many derived analyses like this -- which are later scanned in other computations, so should persist -- I indeed see no other way but to procreate derived tables with the same key, one column per each...

OK, so in that case, if you could do all of your derived column calculations in one query like this :

CREATE TABLE derived AS SELECT ... FROM ... (perform all your derived calculations here)

	or :

BEGIN;	<-- this is important to avoid writing xlog
CREATE TABLE derived AS ...
INSERT INTO derived SELECT ... FROM ... (perform all your derived calculations here)
COMMIT;

Basically, updating the entire table several times to add a few simple columns is a bad idea. If you can compute all the data you need in one query, like above, it will be much faster. Especially if you join one large table to several smaller ones, and as long as the huge data set doesn't need to be sorted (check the query plan using EXPLAIN). Try to do as much as possible in one query to scan the large dataset only once.

Note that the above will be faster than updating the entire table since it needs to write much less data : it doesn't need to delete the old rows, and it doesn't need to write the transaction log, since if the transaction rolls back, the table never existed anyway. Also since your newly created table doesn't have any indexes, they won't need to be updated.

If you really need to update an entire table multiple times, you will need to :

- Use hardware that can handle disk writes at a decent speed (that isn't a characteristic of a laptop drive) - use MyIsam, yes (but if you need to make complex queries on the data afterwards, it could suck).



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux