Re: Odd out of memory problem.

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 03/26/2012 12:11 PM, Tom Lane wrote:
>> That plan should not create a tuple hash table, so I think it's almost
>> certain that the plan changed. It might be interesting to remove the
>> pg_statistic rows for the table and then see what plan you get.

> Yeah, that gets us:

> Limit (cost=2223492.78..2223492.81 rows=10 width=8)
> -> Sort (cost=2223492.78..2223493.28 rows=200 width=8)
> Sort Key: (max(pageno))
> -> HashAggregate (cost=2223485.96..2223488.46 rows=200 width=8)
> -> Seq Scan on ldata (cost=0.00..1651154.64
> rows=114466264 width=8)

Hm. This illustrates that it's not too prudent to rely on a default
numdistinct estimate to decide that a hash aggregation is safe :-(.
We had probably better tweak the cost estimation rules to not trust
that. Maybe, if we have a default estimate, we should take the worst
case estimate that the column might be unique? That could still burn
us if the rowcount estimate was horribly wrong, but those are not nearly
as shaky as numdistinct estimates ...

>> [ scratches head... ] I don't understand how or why pg_restore would be
>> executing such a query.

> It's not. I was explaining that we have seen memory failures in *other*
> contexts, not just this query. The restore fails after many hours on a
> call to lo_write().

Seems probably unrelated then. Have you got a memory-usage dump for
that case?

regards, tom lane