Anything I can do to speed up this query?

From: Wei Weng <wweng(at)kencast(dot)com>
To: PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
Subject: Anything I can do to speed up this query?
Date: 2006-12-05 20:56:15
Message-ID: 1165352175.8448.8.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table that has roughly 200,000 entries and many columns.

The query is very simple:

SELECT Field1, Field2, Field3... FieldN FROM TargetTable;

TargetTable has an index that is Field1.

The thing is on this machine with 1Gig Ram, the above query still takes
about 20 seconds to finish. And I need it to run faster, ideally around
5 seconds.

I already increased the following configurations:

Test=> show sort_mem;
sort_mem
----------
262144
(1 row)

Test=> show shared_buffers ;
shared_buffers
----------------
60800
(1 row)

And the EXPLAIN ANALYZE result is also as follows:

QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on TargetTable (cost=0.00..28471.72 rows=210872 width=988)
(actual time=0.037..6084.385 rows=211286 loops=1)
Total runtime: 6520.499 ms

what else can I improve? Thanks in advance

Wei

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Glen Parker 2006-12-05 20:56:32 Re: HELP: Urgent, Vacuum problem
Previous Message Tom Lane 2006-12-05 20:49:15 Re: Unable to restart postgres - database system was interrupted