Re: [GENERAL] Reducing memory usage of insert into select operations? [Solved]

Martijn van Oosterhout wrote:
> Can you make them not deferred?
How?

I found the issue.
I had the foreign key in the master table instead of the children.
Deleted RI from master table and put into the inherited partitions.
My whole 230 million rows merged in about an hour!
And I even had two of those running at the same time. (one setup with 14
partitions per month and another with 5 partitions per month to test
difference in performance).

It was so fast I even had to do a count(*) to make sure both actually
merged.
That is 117K rows per second for rows that were about 33 bytes long.
That only comes down to about 3 MB/sec+overhead, but still 117K rows/sec
is not too shabby.

In case it is of interest to anyone..
2 AMD dual core, 2GHz CPUs
12GB of RAM
shared_buffers 3GB
work_mem 64MB
256 check_point segments
10 min checkpoing_timeout
LSI controller with 128MB cache with BBU. Write cache enabled.

Many thanks to all that offered suggestions in the troubleshooting.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general