Re: Query memory usage

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

> Tom Duffey <tduffey(at)trillitech(dot)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