Skip site navigation (1) Skip section navigation (2)

Re: Odd out of memory problem.

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Odd out of memory problem.
Date: 2012-03-26 15:48:18
Message-ID: 4F708FC2.60301@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers

On 03/26/2012 11:18 AM, Tom Lane wrote:
> Andrew Dunstan<andrew(at)dunslane(dot)net>  writes:
>> I'm really perplexed as to why this fairly simple query should cause an
>> out of memory error:
>>      select loid, max(pageno) from ldata group by loid order by 2 desc
>>      limit 10;
> Looks like the group by/aggregate step is eating lots of memory:
>
>>               AggContext: 864018432 total in 127 blocks; 3400 free (110
>>      chunks); 864015032 used
>>                 TupleHashTable: 619175960 total in 95 blocks; 821528 free
>>      (331 chunks); 618354432 used
> A guess is that there are a huge number of distinct values of "loid" but
> the planner fails to realize that and tries to use a hash aggregation.
> Could we see EXPLAIN output for this query?

Currently it shows:

Limit  (cost=19443025.87..19443025.89 rows=10 width=8
   ->  Sort  (cost=19443025.87..19446451.29 rows=1370168 width=8)
         Sort Key: (max(pageno))
         ->  GroupAggregate  (cost=18537785.99..19413417.03 rows=1370168 
width=8)
               ->  Sort  (cost=18537785.99..18823953.97 rows=114467192 
width=8)
                     Sort Key: loid
                     ->  Seq Scan on ldata  (cost=0.00..1651163.92 
rows=114467192 width=8)



The table might have been analysed since I ran the query, though.

To answer Hans' question, we have seen the problem in other contexts. We 
first noticed this problem in a failure to restore large objects when 
running pg_restore.  The database has 43,998,486 LOs on 114,467,137 
pages. The largest of these is 2160 pages. We're currently running a 
test to see if we can successfully restore LOs by doing them in smaller 
batches rather than in a single transaction. However, this one seemed 
even odder than the LO problem.


cheers

andrew




In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-03-26 16:11:25
Subject: Re: Odd out of memory problem.
Previous:From: Tom LaneDate: 2012-03-26 15:18:26
Subject: Re: Odd out of memory problem.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group