X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0818B3A3DC2 for ; Mon, 17 Jan 2005 18:08:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34638-02 for ; Mon, 17 Jan 2005 18:08:37 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id EFF1B3A3D7B for ; Mon, 17 Jan 2005 18:08:37 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j0HI7Ku1003958; Mon, 17 Jan 2005 13:07:20 -0500 (EST) To: Kaloyan Iliev Iliev Cc: pgsql-performance@postgresql.org Subject: Re: Performance problem from migrating between versions! In-reply-to: <41EBFDBF.9040105@faith.digsys.bg> References: <41EBDBBB.3070301@faith.digsys.bg> <2253.1105978380@sss.pgh.pa.us> <41EBFDBF.9040105@faith.digsys.bg> Comments: In-reply-to Kaloyan Iliev Iliev message dated "Mon, 17 Jan 2005 20:02:39 +0200" Date: Mon, 17 Jan 2005 13:07:20 -0500 Message-ID: <3957.1105985240@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/211 X-Sequence-Number: 9901 Kaloyan Iliev Iliev writes: > It worked. I have read in the docs what this "enable_hashagg" do, but I > couldn't understand it. What does it change? Your original 7.4 query plan has several HashAgg steps in it, which are doing aggregate/GROUP BY operations. The planner thinks that they will use only nominal amounts of memory because there are only a few distinct groups in each case. Evidently that is wrong and at least one of them is dealing with so many groups as to run out of memory. So the next question is have you ANALYZEd all of these tables recently? I wouldn't recommend turning off hashagg as a permanent solution, it was just a quickie to verify my suspicion of where the memory was going. regards, tom lane