Re: Reducing memory usage of insert into select operations?

Francisco Reyes wrote:
> On 8:13 am 07/18/08 Richard Huxton <dev(at)archonet(dot)com> wrote:
>> Is the partition split done with triggers or rules?
>
> I have a single trigger+function combo that dynamically computes which
> partition the data has to go to.

I'm wondering whether it's memory usage either for the trigger itself,
or for the function (pl/pgsql?). If you're doing something like:
INSERT INTO partitioned_table SELECT * FROM big_table
then that's not only taking place within a single transaction, but
within a single statement.

Without being a hacker, I'd say it's entirely plausible that PG might
clean up triggers at the end of a statement meaning you would need
memory for 200million+ triggers.

Alternatively, it could be a memory-leak somewhere in the pl/pgsql or
trigger code. Wouldn't have to be much to affect this particular case.

What happens if you do the insert/select in stages but all in one
transaction? Do you see PG's memory requirement stay constant or grow in
steps. That will show whether the memory is growing over the duration of
a statement or a transaction.

BEGIN;
INSERT ... SELECT ... WHERE id BETWEEN 0 AND 999999
INSERT ... SELECT ... WHERE id BETWEEN 1000000 AND 1999999
...
COMMIT;

--
Richard Huxton
Archonet Ltd