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@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)