** Environment **

cityvox=# select version();
                                               version                                               
-----------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.10 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 
----------------
 16384
(1 row)

cityvox=# show work_mem;
 work_mem 
----------
 32768
(1 row)

** Seqscan on the table **

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

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

Time: 57.317 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: 188.312 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: 188.235 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: 227.048 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: 226.586 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..6743.01 rows=1 width=4) (actual time=45.907..226.702 rows=3 loops=1)
   Filter: (((libgeseve)::text ~~* '%hocus pocus%'::text) OR ((mots_cle)::text ~~* '%hocus pocus%'::text))
 Total runtime: 226.736 ms
(3 rows)

Time: 227.216 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: 177.318 ms