Re: Why are selects so slow on large tables, even
От | Neil Conway |
---|---|
Тема | Re: Why are selects so slow on large tables, even |
Дата | |
Msg-id | 1017272901.8890.11.camel@jiro обсуждение исходный текст |
Ответ на | Re: Why are selects so slow on large tables, even whenindexed? ("Robert Wille" <rwille@iarchives.com>) |
Список | pgsql-general |
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@rogers.com> PGP Key ID: DB3C29FC
В списке pgsql-general по дате отправления: