Re: Out of swap space & memory

"Kevin Bartz" <kbartz(at)loyaltymatrix(dot)com> writes:
> Hi Lincoln! Thanks for your reply. On the problematic SELECT INTO, EXPLAIN
> says:

> test=#
> QUERY PLAN
> ------------------------------------------------------------------
> HashAggregate (cost=42.50..42.50 rows=1000 width=356)
> -> Seq Scan on hp_raw (cost=0.00..20.00 rows=1000 width=356)
> (2 rows)

It's fairly obvious that you have never vacuumed or analyzed hp_raw,
since those numbers are the fallback defaults when a table is completely
unknown to the planner :-(

> Correct me if I'm wrong, but I'm guessing that what I should infer from this
> is that Postgres is indeed using hash aggregation. I just tried set
> enable_hashagg = off, per your suggestion, and the SELECT INTO is grouping
> away without blowup this time. Unfortunately, there's no end in sight. I've
> let it sit and crank for an hour now, and it's still going.

If you are running with the default value of sort_mem, it wouldn't be
surprising for a sort-based aggregation to take awhile :-(. I would
suggest cranking sort_mem up to something large (several hundred meg,
maybe) for this specific operation.

regards, tom lane