Re: Query memory usage

On May 15, 2010, at 8:00 PM, Tom Lane wrote:

> Tom Duffey <tduffey@trillitech.com> writes:
>> On May 15, 2010, at 7:28 PM, Tom Lane wrote:
>>> Well, I tried executing a large "copy (select ...)" query and
>>> couldn't
>>> see any memory bloat at all in either the backend or psql.  So
>>> there's
>>> something relevant that you haven't told us.
>
>> I hope you are right!  The actual query is different because I was
>> not
>> aware until right before I posted this question that you can have a
>> WHERE clause with COPY.  Here is the actual query I ran:
>
>> SELECT point_id || E'\t' || status || E'\t' || value || E'\t' ||
>> timestamp
>> FROM point_history
>> WHERE timestamp > NOW() - interval '18 months';
>
> Ermm ... is that the whole query, or did you wrap it in COPY (...) TO
> STDOUT?  The former case will cause psql to eat memory, because it
> tries
> to buffer the whole result of an ordinary query.  In the latter case
> psql will just stream the data through to the output file.


That's the whole query.  If I understand your reply correctly it
sounds like psql was the culprit and that I should try again using
COPY (...) TO STDOUT, no?

Tom