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

Wei Weng wrote:
> 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.

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

Thats 988 * 211286 =~ 200MB of data. Since the explain-analyse completes
in 6.5secs that would mean you're spending 13.5 seconds building the
result-set, transferring it and processing it at the client end.

That will take up at least 400MB of RAM (realistically more) - I'd
suggest you'd be better off with a cursor, unless you really need the
whole thing in one go.

If you do need all the data at once, you'll want a faster CPU and faster
RAM I guess.

--
Richard Huxton
Archonet Ltd