From: | Tom Duffey <tduffey(at)trillitech(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query memory usage |
Date: | 2010-05-16 00:42:26 |
Message-ID: | 01EC967B-8013-48AF-BFEA-FD814D962F32@trillitech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-05-16 01:00:39 | Re: Query memory usage |
Previous Message | Tom Lane | 2010-05-16 00:28:41 | Re: Query memory usage |