Re: [GENERAL] SELECT from a set of values really slow?

[Pierre-Frédéric Caillaud]

test=> insert into bench (id,data) select id, 'text_item_'||id::text from  
dummy where id<=100000 order by id;
INSERT 0 100001
test=> CREATE INDEX bench_data_index ON bench (data);
CREATE INDEX
test=> explain select * from bench where data = 'test_item_1';
 Index Scan using bench_data_index on bench  (cost=0.00..1478.85 rows=501  
width=36)
   Index Cond: (data = 'test_item_1'::text)
(2 lignes)

test=> explain select * from bench where data in ( 'test_item_1',  
'test_item_2' );
 Seq Scan on bench  (cost=0.00..2190.01 rows=998 width=36)
   Filter: ((data = 'test_item_1'::text) OR (data = 'test_item_2'::text))
(2 lignes)

test=> vacuum analyze bench;
VACUUM
test=> explain select * from bench where data in ( 'test_item_1',  
'test_item_2' );
 Index Scan using bench_data_index, bench_data_index on bench   
(cost=0.00..7.91 rows=2 width=22)
   Index Cond: ((data = 'test_item_1'::text) OR (data =  
'test_item_2'::text))
(2 lignes)

=> once you analyze, it works...
***********************************************
With 1 item :
test=>explain analyze select * from bench where data in ( 'test_item_1' );
 Total runtime: 0.127 ms
With 11 items :
test=>explain analyze select * from bench where data in ( 'test_item_1',  
'test_item_2', 'test_item_55', 'test_item_64', 'test_item_1005',  
'test_item_78541', 'test_item_96521', 'test_item_8574', 'test_item_89652',  
'test_item_14527', 'test_item_48652' );
 Total runtime: 0.352 ms

***********************************************
With a Join... see on psql-performance

On Sun, 10 Oct 2004 16:00:10 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
Tim Smith <[EMAIL PROTECTED]> writes:
    SELECT id FROM bench WHERE data IN ('X', 'Y')

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?
Likely it's switching from index to sequential scan because of a poor
estimate of how many rows will be returned.  Have you ever ANALYZEd
the test table?  Without either ANALYZE stats or a unique index,
the planner will certainly not think that the column is unique.
                        regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
               http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]