Re: out of memory - no sort

On 08/30/11 7:28 AM, Don wrote:
> I am trying a simple access of a table and get an out of memory
> error. How do I avoid this issue. It seems I have some configuration
> set wrong.
>
> Our system has 24GB of memory and is dedicated to the postgres database.
>
> Back ground information
>
> aquarec=> explain analyze verbose select * from ens_memb;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------
> Seq Scan on ens_memb (cost=0.00..719893.12 rows=32216212 width=62)
> (actual time=4.954..37513.377 rows=32216154 loops=1)
> Output: id, shefpc, bwmon, ewmon, pb, tb, cdate, vdate, source,
> tyr, val
> Total runtime: 39588.386 ms

wild guess (since you didn't show the error), your system doesn't have
enough memory available to store all 32 million rows of your result
set. This could be happening on the CLIENT ('psql') side or the server
side (and in fact, if both are on the same system, I believe that query
as written will require two copies of the result set in memory)

--
john r pierce N 37, W 122
santa cruz ca mid-left coast