Re: out of memory - no sort

On Mon, Aug 29, 2011 at 2:15 PM, Don <Donald(dot)Laurine(at)noaa(dot)gov> 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

your problem is probably on the client. libpq unfortunately holds
complete results in memory...you have to work around this.
resolutions:
*) switch to 64 bit psql/libpq if you haven't already
*) use a cursor
*) browse records client side ordering on index/key

merlin