Re: Anything I can do to speed up this query?

On þri, 2006-12-05 at 15:56 -0500, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;

> 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.

> Test=> show shared_buffers ;
> shared_buffers
> ----------------
> 60800

do you mean that you want the data to be cached, or do you need it to be
fast the first time?
if you want it to be cached you might be better served
with a lower shared buffers, to leave more memory tothe OS cache.

If you just need more speed reading from the disks,
you probably just need a faster disk subsystem, although
should should make sure the table does not contain
a lot of dead rows, by doing a VACUUM FULL or a CLUSTER
once before trying again.

If Field1, Field2 ... FieldN are a small subset of the
tables row-width, you should consider vertical
partitioning, to minimize IO needed for this particular
query, although this will not help much if the subset is
not fixed.

gnari