Tom Duffey wrote:
> Hi Everyone,
>
> I have a table with several hundred million rows of timestamped
> values. Using pg_dump we are able to dump the entire table to disk no
> problem. However, I would like to retrieve a large subset of data
> from this table using something like:
>
> COPY (SELECT * FROM history WHERE timestamp > '2009-01-01') TO STDOUT;
>
> Executing this query causes our server to consume all available swap
> and crash. Can anyone help me figure out what needs to be done to
> allow this query to execute? How long it takes doesn't really matter
> as long as it can be performed reliably. The database currently lives
> on a Red Hat EL 5.3 server with 16GB RAM and 4GB swap running
> PostgreSQL 8.3.7. Possibly relevant lines from postgresql.conf:
>
> shared_buffers = 4GB
> work_mem = 32MB
> maintenance_work_mem = 1GB
> effective_cache_size = 8GB
>
> Tom
>
Is there an index on the time stamp? If not, create one - it will make
possible the select without having to read the entire table (only the index)
-- Karl