Re: Query memory usage

On May 15, 2010, at 7:28 PM, Tom Lane wrote:

> Tom Duffey <tduffey(at)trillitech(dot)com> writes:
>> On May 15, 2010, at 4:51 PM, Tom Lane wrote:
>>> What's being done on the client side with the data?
>
>> 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
>
> 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.
>
> Could we see the full schema (eg via psql \dt) for the table being
> copied?

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';

And here is the table schema:

prod=> \dt point_history
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------------
public | point_history | table | prod
(1 row)

prod=> \d point_history
Table "public.point_history"
Column | Type | Modifiers
-----------+-----------------------------+-----------
point_id | integer | not null
value | real | not null
status | integer | not null
timestamp | timestamp without time zone | not null
Indexes:
"point_history_pkey" PRIMARY KEY, btree (point_id, "timestamp")
Foreign-key constraints:
"$1" FOREIGN KEY (point_id) REFERENCES point(id)

Tom