Subject: Re: out of memory
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: martial.bizel@free.fr
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1140018930.43f34ef260de0@imp2-g19.free.fr>
References: <1140018930.43f34ef260de0@imp2-g19.free.fr>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1140022257.22740.225.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 15 Feb 2006 10:50:57 -0600

On Wed, 2006-02-15 at 09:55, martial.bizel@free.fr wrote:
> Good morning,
> 
> 
> 
> 
> I've increased sort_mem until 2Go !!
> and the error "out of memory" appears again.
> 
> Here the request I try to pass with her explain plan,
> 
>  Nested Loop  (cost=2451676.23..2454714.73 rows=1001 width=34)
>    ->  Subquery Scan "day"  (cost=2451676.23..2451688.73 rows=1000 width=16)
>          ->  Limit  (cost=2451676.23..2451678.73 rows=1000 width=12)
>                ->  Sort  (cost=2451676.23..2451684.63 rows=3357 width=12)
>                      Sort Key: sum(occurence)
>                      ->  HashAggregate  (cost=2451471.24..2451479.63 rows=3357
> width=12)
>                            ->  Index Scan using test_date on
> queries_detail_statistics  (cost=0.00..2449570.55 rows=380138 width=12)
>                                  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)
>          Index Cond: ("outer".query = query_string.id)
> (11 rows)

OK, so it looks like something is horrible wrong here.  Try running the
explain analyze query after running the following:

 set enable_hashagg=off;

and see what you get then.