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 70FD29DC835 for ; Wed, 15 Feb 2006 13:38:22 -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 73689-01 for ; Wed, 15 Feb 2006 13:38:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 243D39DCC19 for ; Wed, 15 Feb 2006 13:38:18 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 15 Feb 2006 17:38:17 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 15 Feb 2006 11:38:17 -0600 Subject: Re: out of memory From: Scott Marlowe To: martial.bizel@free.fr Cc: pgsql-performance@postgresql.org In-Reply-To: <1140023901.43f3625d51661@imp2-g19.free.fr> References: <1140018930.43f34ef260de0@imp2-g19.free.fr> <1140022257.22740.225.camel@state.g2switchworks.com> <1140023901.43f3625d51661@imp2-g19.free.fr> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1140025097.22740.229.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 15 Feb 2006 11:38:17 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.161 required=5 tests=[AWL=0.160, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.161 X-Spam-Level: X-Archive-Number: 200602/206 X-Sequence-Number: 17192 On Wed, 2006-02-15 at 11:18, martial.bizel@free.fr wrote: > Here the result with hashAgg to false : > Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual > time=1028044.781..1030251.260 rows=1000 loops=1) > -> Subquery Scan "day" (cost=2487858.08..2487870.58 rows=1000 width=16) > (actual time=1027996.748..1028000.969 rows=1000 loops=1) > -> Limit (cost=2487858.08..2487860.58 rows=1000 width=12) (actual > time=1027996.737..1027999.199 rows=1000 loops=1) > -> Sort (cost=2487858.08..2487866.47 rows=3357 width=12) > (actual time=1027996.731..1027998.066 rows=1000 loops=1) > Sort Key: sum(occurence) > -> GroupAggregate (cost=2484802.05..2487661.48 rows=3357 > width=12) (actual time=810623.035..914550.262 rows=19422774 loops=1) > -> Sort (cost=2484802.05..2485752.39 rows=380138 > width=12) (actual time=810612.248..845427.013 rows=36724340 loops=1) > Sort Key: query > -> Index Scan using test_date on > queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12) (actual > time=25.393..182029.205 rows=36724340 loops=1) > Index Cond: ((date >= '2006-01-01'::date) > AND (date <= '2006-01-30'::date)) > Filter: (((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) (actual time=2.244..2.246 rows=1 loops=1000) > Index Cond: ("outer".query = query_string.id) > Total runtime: 1034357.390 ms OK, in the index scan using test_date, you get 36724340 when the planner expects 380138. That's off by a factor of about 10, so I'm guessing that your statistics aren't reflecting what's really in your db. You said before you'd run analyze, so I'd try increasing the stats target on that column and rerun analyze to see if things get any better.