On 03/26/2012 11:18 AM, Tom Lane wrote:
Andrew Dunstan<and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
