Re: index with LIKE
От | Henrik Steffen |
---|---|
Тема | Re: index with LIKE |
Дата | |
Msg-id | 038b01c453d5$ae913d70$9800a8c0@henrik обсуждение исходный текст |
Ответ на | Re: index with LIKE (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: index with LIKE
|
Список | pgsql-general |
hi tom, here the results of EXPLAIN ANALYZE SELECT * FROM basiseintrag WHERE kundennummer LIKE '0101%' AND firma LIKE 'top con%'; run on all three servers: main server: QUERY PLAN ------------------------------------------------------------------------ --------------------------------------------------------------- Index Scan using basiseintrag_firma_idx on basiseintrag (cost=0.00..6.01 rows=1 width=265) (actual time=0.315..0.393 rows=2 loops=1) Index Cond: ((firma >= 'top con'::text) AND (firma < 'top coo'::text)) Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top con%'::text)) Total runtime: 0.784 ms (4 rows) slave 1: QUERY PLAN ------------------------------------------------------------------------ ---------------------------------------- Seq Scan on basiseintrag (cost=0.00..22214.97 rows=1 width=263) (actual time=484.111..605.169 rows=1 loops=1) Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top con%'::text)) Total runtime: 605.424 ms (3 rows) slave 2: QUERY PLAN ------------------------------------------------------------------------ ---------------------------------------- Seq Scan on basiseintrag (cost=0.00..24064.96 rows=1 width=264) (actual time=569.324..589.362 rows=1 loops=1) Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top con%'::text)) Total runtime: 589.589 ms (3 rows) and now this is with enable_seqscan set to off on slave 1: QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------ Seq Scan on basiseintrag (cost=100000000.00..100024064.96 rows=1 width=264) (actual time=1028.468..1092.537 rows=1 loops=1) Filter: ((kundennummer ~~ '0101%'::text) AND (firma ~~ 'top con%'::text)) Total runtime: 1093.320 ms (3 rows) -- 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 по дате отправления: