On Jun 22, 2006, at 2:23 PM, Tom Lane wrote:
"Relyea, Mike" <Mike.Relyea@xxxxxxxxx> writes:
I've zipped the results of EXPLAIN INSERT INTO "tblSummary" SELECT *
FROM "qrySummary"; for my case. It's a zip file that I've renamed to
.txt in order to get around the attachment being blocked by
certain mail
servers.
Egad, what a mess :-(. By my count you have 89 hash joins, 24 sorts,
and 8 hash aggregations in there. In total these will feel authorized
to use 121 times work_mem. Since you've got work_mem set to 256 meg,
an out-of-memory condition doesn't seem that surprising. You need to
make work_mem drastically smaller for this query. Or else break it
down
into multiple steps.
Except won't the sorts pull in all data from their underlying node
before proceeding, which should free the memory from those underlying
nodes? If so, it looks like it's not nearly as bad, only taking about
20x work_mem (which of course still isn't great...)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
