Search Postgresql Archives

Re: Reducing memory usage of insert into select operations?

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

 



Francisco Reyes wrote:
On 8:13 am 07/18/08 Richard Huxton <dev@xxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux