Re: Query memory usage

[Tom Duffey <tduffey@xxxxxxxxxxxxxx>]

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

Tom Duffey <tduffey@xxxxxxxxxxxxxx> 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