X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9E20B9DC81F for ; Tue, 14 Feb 2006 12:03:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55659-06 for ; Tue, 14 Feb 2006 12:03:40 -0400 (AST) X-Greylist: whitelisted by SQLgrey- Received: from smtp2-g19.free.fr (smtp2-g19.free.fr [212.27.42.28]) by postgresql.org (Postfix) with ESMTP id 343489DC823 for ; Tue, 14 Feb 2006 12:03:40 -0400 (AST) Received: from imp6-g19.free.fr (imp6-g19.free.fr [212.27.42.6]) by smtp2-g19.free.fr (Postfix) with ESMTP id 1CB946CC25; Tue, 14 Feb 2006 17:03:39 +0100 (CET) Received: by imp6-g19.free.fr (Postfix, from userid 33) id 7E5A42E0C6; Tue, 14 Feb 2006 17:03:38 +0100 (MET) Received: from wormhole.x-echo.com (wormhole.x-echo.com [193.252.148.11]) by imp6-g19.free.fr (IMP) with HTTP for ; Tue, 14 Feb 2006 17:03:38 +0100 Message-ID: <1139933018.43f1ff5a5e542@imp6-g19.free.fr> Date: Tue, 14 Feb 2006 17:03:38 +0100 From: martial.bizel@free.fr To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: out of memory References: <1139930651.43f1f61b04773@imp2-g19.free.fr> <15115.1139931169@sss.pgh.pa.us> In-Reply-To: <15115.1139931169@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.5 X-Originating-IP: 193.252.148.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.63 required=5 tests=[AWL=0.080, NO_REAL_NAME=0.55] X-Spam-Score: 0.63 X-Spam-Level: X-Archive-Number: 200602/157 X-Sequence-Number: 17143 Thanks for your response, I've made this request : SELECT query_string, DAY.ocu from search_data.query_string, (SELECT SUM(occurence) as ocu, query FROM daily.queries_detail_statistics WHERE date >= '2006-01-01' AND date <= '2006-01-30' AND portal IN (1,2) GROUP BY query ORDER BY ocu DESC LIMIT 1000) as DAY WHERE DAY.query=id; and after few minutes, i've error "out of memory" with this execution plan : Nested Loop (cost=8415928.63..8418967.13 rows=1001 width=34) -> Subquery Scan "day" (cost=8415928.63..8415941.13 rows=1000 width=16) -> Limit (cost=8415928.63..8415931.13 rows=1000 width=12) -> Sort (cost=8415928.63..8415932.58 rows=1582 width=12) Sort Key: sum(occurence) -> HashAggregate (cost=8415840.61..8415844.56 rows=1582 width=12) -> Seq Scan on queries_detail_statistics (cost=0.00..8414056.00 rows=356922 width=12) Filter: ((date >= '2006-01-01'::date) AND (date <= '2006-01-30'::date) AND (((portal)::text = '1'::text) OR ((portal)::text = '2'::text))) -> Index Scan using query_string_pkey on query_string (cost=0.00..3.01 rows=1 width=34) Index Cond: ("outer".query = query_string.id) (10 rows) if HashAgg operation ran out of memory, what can i do ? thanks a lot martial > martial.bizel@free.fr writes: > > I've error "out of memory" with these traces : > > Doing what? > > > AggContext: -1976573952 total in 287 blocks; 25024 free (414 chunks); > > -1976598976 used > > DynaHashTable: 503439384 total in 70 blocks; 6804760 free (257 chunks); > > 496634624 used > > I'd guess that a HashAgg operation ran out of memory ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >