index with LIKE
От | Henrik Steffen |
---|---|
Тема | index with LIKE |
Дата | |
Msg-id | 009001c44def$744ca8c0$9800a8c0@henrik обсуждение исходный текст |
Ответы |
Re: index with LIKE
|
Список | pgsql-general |
hello all, on my master-db-server i'm running postgres 7.4.1, and I have got two slave-servers running postgres 7.4.2 running the following query on the master-server (7.4.1) delivers: explain analyze select * from foo where bar like '0101%' and foobar like 'top%'; Index Scan using foo_foobar_idx on foo (cost...) Index Cond: ((foobar>='top::text) and (firma < 'toq'::text)) Filter: ((bar ~~ '0101%'::text) and (firma ~~ 'top%'::text)) Total runtime: 1.519 ms the same query on both slaves (7.4.2)(mirrored from master, same structure of table etc.) yields: Seq Scan on foo (cost ....) Filter: ((bar ~~ '0101%'::text) AND (foobar ~~ 'top%'::text)) Total runtime: 722.331 ms Is there such a difference in 7.4.1 to 7.4.2 ? A retardation of 722 ms is not acceptable for me, and I don't see why the indexes are not used. Do you have an idea? I have run VACUUM ANALYZE several times, and REINDEX TABLE foo and even DROPped and reCREATEd the index. Didn't help. BTW if I run: explain analyze select * from foo where bar like '0101%' and foobar>='top'::text and foobar<'toq'::text; the index is utilized as it is supposed to Any hint appreciated, thank you -- Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany -------------------------------------------------------- http://www.topconcepts.de Tel. +49 1805 9977 501* mail: steffen@topconcepts.de Fax. +49 1805 9977 502* -------------------------------------------------------- SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de -------------------------------------------------------- Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 -------------------------------------------------------- *) EUR 0,12/Min. (CNS24)
В списке pgsql-general по дате отправления: