ilike_8.3devel.txt

** Environment **

cityvox=# select version(); 
                                                version                                                
-------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

cityvox=# show lc_collate;
 lc_collate  
-------------
 fr_FR.UTF-8
(1 row)

cityvox=# show lc_ctype;
  lc_ctype   
-------------
 fr_FR.UTF-8
(1 row)

cityvox=# \l
        List of databases
   Name    |  Owner   | Encoding 
-----------+----------+----------
 cityvox   | postgres | UTF8
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(4 rows)

cityvox=# show shared_buffers ;
 shared_buffers 
----------------
 128MB
(1 row)

cityvox=# show work_mem ;      
 work_mem 
----------
 32MB
(1 row)

** Seqscan on the table **

cityvox=# select count(*) from evenement;
 count  
--------
 128780
(1 row)

Time: 33.181 ms
cityvox=# select count(*) from evenement;
 count  
--------
 128780
(1 row)

Time: 33.152 ms

** Query with LIKE **

cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%' OR e.mots_cle LIKE '%hocus pocus%';
 numeve 
--------
(0 rows)

Time: 148.927 ms
cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%' OR e.mots_cle LIKE '%hocus pocus%';
 numeve 
--------
(0 rows)

Time: 148.931 ms

** Query with ILIKE **

cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%' OR e.mots_cle ILIKE '%hocus pocus%';
  numeve   
-----------
 900024298
     87578
    161108
(3 rows)

Time: 597.008 ms
cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%' OR e.mots_cle ILIKE '%hocus pocus%';
  numeve   
-----------
 900024298
     87578
    161108
(3 rows)

Time: 597.340 ms

cityvox=# EXPLAIN ANALYZE SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%' OR e.mots_cle ILIKE '%hocus pocus%';
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on evenement e  (cost=0.00..5821.35 rows=1 width=4) (actual time=122.567..598.229 rows=3 loops=1)
   Filter: (((libgeseve)::text ~~* '%hocus pocus%'::text) OR ((mots_cle)::text ~~* '%hocus pocus%'::text))
 Total runtime: 598.263 ms
(3 rows)

Time: 598.899 ms

** Query with only one condition with ILIKE **

cityvox=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%';
  numeve   
-----------
 900024298
     87578
(2 rows)

Time: 399.534 ms