On 03/26/2012 11:18 AM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.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
