On Mon, Feb 22, 2010 at 22:51, Yang Zhang <yanghatespam@gmail.com> wrote:
> vmstat showed no swapping-out for a while, and then suddenly it
> started spilling a lot. Checking psql's memory stats showed that it
> was huge -- apparently, it's trying to store its full result set in
> memory. As soon as I added a LIMIT 10000, everything worked
> beautifully and finished in 4m (I verified that the planner was still
> issuing a Sort).
Well im half surprised no one has recommend using a cursor. Have you
looked in to that? I bet that would fix most of your problems here.
>
> I'm relieved that Postgresql itself does not, in fact, suck, but
> slightly disappointed in the behavior of psql. I suppose it needs to
> buffer everything in memory to properly format its tabular output,
> among other possible reasons I could imagine.
Well AFAIK it will dump everything you asked for. So if you said
select * from 1G table; It should take at least 1G and potentially
quite a bit more formatting and overhead.