Date: Fri, 02 May 2008 23:30:44 +0200
To: "Alexy Khrabrov" <deliverable@gmail.com>,
	"Craig James" <craig_james@emolecules.com>
Subject: Re: two memory-consuming postgres processes
From: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <A728C858-76CF-473F-917B-3C9EA4AA75CF@gmail.com>
	<dcc563d10805021230i5916561k3962a41e2afa894c@mail.gmail.com>
	<27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>
	<dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com>
	<21403.1209759217@sss.pgh.pa.us>
	<72E02D29-848B-467A-AE6B-401568010254@gmail.com>
	<dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
	<481B8160.2090106@emolecules.com>
	<C25E5DBA-B649-4483-B1A2-2A881522B891@gmail.com>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uajp1iotcigqcu@apollo13.peufeu.com>
In-Reply-To: <C25E5DBA-B649-4483-B1A2-2A881522B891@gmail.com>
User-Agent: Opera Mail/9.24 (Linux)


> 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).