Re: two memory-consuming postgres processes

On Fri, 2 May 2008, PFC wrote:
> CREATE TABLE derived AS SELECT ... FROM ... (perform all your derived
> calculations here)

Given what you have said (that you really want all the data in one table)
it may be best to proceed like this:

First, take your original table, create an index on the primary key field,
and CLUSTER on that index.

CREATE TABLE derived AS SELECT ... FROM ... ORDER BY primary key field
CREATE INDEX derived_pk ON derived(primary key field)

Repeat those last two commands ad nauseum.

Then, when you want a final full table, run:

CREATE TABLE new_original AS SELECT * FROM original, derived, derived2,
... WHERE original.pk = derived.pk ...

That should be a merge join, which should run really quickly, and you can
then create all the indexes you want on it.

Matthew

--
When I first started working with sendmail, I was convinced that the cf
file had been created by someone bashing their head on the keyboard. After
a week, I realised this was, indeed, almost certainly the case.
-- Unknown