Re: BUG #2225: Backend crash -- BIG table

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>> pointspp=# explain select trid, count(*) from pptran group by trid
>> having count(*) > 1;
>> QUERY PLAN
>> --------------------------------------------------------------------------
>> HashAggregate  (cost=1311899.28..1311902.78 rows=200 width=18)
>> Filter: (count(*) > 1)
>> ->  Seq Scan on pptran  (cost=0.00..1039731.02 rows=36289102 width=18)
>> (3 rows)

>>> Failing that, how many rows should the above return?

>> That is exactly what I am trying to find out. I can only guess that, but
>> it should not be more than a couple of 10k rows.

The problem is that the HashAgg will have to maintain a counter for
every distinct value of trid, not just those that occur more than
once.  So if there are a huge number of one-time-only values you could
still blow out memory (and HashAgg doesn't currently know how to spill
to disk).

That "rows=200" estimate looks suspiciously like a default.  Has this
table been ANALYZEd recently?  I'd expect the planner not to choose
HashAgg if it has a more realistic estimate of the number of groups.

            regards, tom lane