On Fri, Nov 28, 2008 at 8:08 PM, Grzegorz Jaskiewicz
<gj@pointblue.com.pl> wrote:
> work_mem = 128140 # min 64, size in KB
> maintenance_work_mem = 65535 # min 1024, size in KB
Incidentally it's very odd to see maintenance_work_mem less than
work_mem. work_mem is used by regular queries and maintenance_work_mem
by things like create index.
> AggContext: 1981800448 total in 250 blocks; 3768 free (117 chunks);
> 1981796680 used
> TupleHashTable: 1158668312 total in 150 blocks; 465512 free (580
> chunks); 1158202800 used
Er, yeah. This is a known problem and it would be really nice to fix
it. However nobody's come up with any bright ideas for fixing it and
disabling hash aggregates doesn't seem worth it. The problem only
occurs when Postgres dramaticalyl misestimates the number of hash
buckets needed for hash aggregates.
Can you post the EXPLAIN for your query? you might have to analyze
your tables again, and possibly raise the stats target for the columns
you're aggregating.
Failing that you could set enable_hashaggregate to false for this query.
--
greg