Re: SELECT from a set of values really slow?
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: SELECT from a set of values really slow? |
Дата | |
Msg-id | opsfo5hwnucq72hf@musicbox обсуждение исходный текст |
Ответ на | Re: SELECT from a set of values really slow? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
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 <tgl@sss.pgh.pa.us> wrote: > Tim Smith <reply_in_group@mouse-potato.com> 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 >
В списке pgsql-general по дате отправления: