Re: index with LIKE

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(at)topconcepts(dot)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)