Re: Why are selects so slow on large tables, even

On Wed, 2002-03-27 at 09:52, Robert Wille wrote:
> The suggested fixes have helped a lot, but it is still rather slow. The time
> varies and can be upwards of 10 to 20 seconds on a ~47M row table. Is this
> normal?

You haven't given us enough information to be able to tell. What
hardware is this running on? What (exact) queries are you executing?
What is the schema of any relevant database tables, indexes, views,
etc.? What does EXPLAIN produce for the query? What configuration
changes have you made? (e.g. increasing the size of the shared buffers).
What OS is this running on, and how has the OS been tuned? Are you
running a single query, multiple sequentual queries, or multiple
concurrent queries?

For my own curiosity, what are the results if you execute the same query
using a hash index -- i.e.

CREATE INDEX your_index ON your_table USING hash (your_column);

(You'll need to drop the existing btree index -- once you've got the
results, I'd recommend removing the hash index and re-instating the
btree one for production use.)

> Similar queries on an indexed varchar column in Oracle with about
> 1/2 as many rows execute at least a hundred times faster.

Keep in mind that the time taken to return a single SELECT query is a
very incomplete measure of RDBMS performance; nevertheless, I doubt that
Postgres is, on average, 100x slower than Oracle. Can you post the
Oracle equivalent of EXPLAIN for the queries you're running to derive
this 100x figure?

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC