Re: Query memory usage

On May 15, 2010, at 4:51 PM, Tom Lane wrote:

> Tom Duffey <tduffey(at)trillitech(dot)com> writes:
>> I have a table with several hundred million rows of timestamped
>> values. Using pg_dump we are able to dump the entire table to disk
>> no
>> problem. However, I would like to retrieve a large subset of data
>> from this table using something like:
>
>> COPY (SELECT * FROM history WHERE timestamp > '2009-01-01') TO
>> STDOUT;
>
>> Executing this query causes our server to consume all available swap
>> and crash.
>
> What's being done on the client side with the data? AFAIK that
> operation really shouldn't consume a lot of memory on the server side.
> It would help if you'd be more specific about which process is
> consuming
> swap space.

I am executing the query in psql at the command line and piping the
result to a file, e.g.,

psql < get_data.sql > data.sql

Tom