Re: Query memory usage

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.

regards, tom lane