SELECT from a set of values really slow?
| От | Tim Smith | 
|---|---|
| Тема | SELECT from a set of values really slow? | 
| Дата | |
| Msg-id | oq78d.3057$gs1.2050@newsread2.news.atl.earthlink.net обсуждение исходный текст | 
| Ответы | Re: SELECT from a set of values really slow? Re: SELECT from a set of values really slow? | 
| Список | pgsql-general | 
I've been benchmarking some very simple databases.  By simple, I mean a table
like this:
    CREATE TABLE bench (
        id SERIAL,
        data TEXT )
    CREATE INDEX bench_data_index ON bench (data)
which is filled with 100k records, where the data values for each record are
distinct (the data for record N is "text_item_N").
I generate a random data value known to be in the table and lookup its id,
with "SELECT id FROM bench WHERE data = 'whatever'".
I run this for three seconds.  PostgreSQL does OK: 4300 selects in 2999
msec.  MySQL does a little better, but not much: 5500 selects in 2999 msec.
In my application, this kind of lookup turns out to be the bottleneck on
performance (which is why I'm benchmarking).  On MySQL, I can speed it up
quite a bit by looking up more than one thing at a time:
    SELECT id FROM bench WHERE data IN ('X', 'Y')
for example, gets about 4050 selects in 3 seconds, so that's 8100 records
looked up, compared to 5300 when they were done one at a time.  It continues
to improve selecting more at a time.  To my surprise, when I tried this
trick with PostgreSQL, it did not speed things up.  In fact, it *massively*
slowed down--it only is getting 13 selects in 3 seconds, searching for two
at a time.
What's going on here?
PostgreSQL 7.4.2 on SuSE 9.1 Linux.  (The version that comes with SuSE).
--
--Tim Smith
		
	В списке pgsql-general по дате отправления: