Re: [SQL] How to Make Case InSensitive???

> I working with a table of about 70'000 records and I made 2 tests about
> the optimisation of ~* :
>
> With ~* :
> ----------
> % time -p psql mayer98 -c "select * from artistes where nom ~* 'picasso';"
>
>   ref|nom                         |nom_court    |type|nationalite
> -----+----------------------------+-------------+----+-----------
> 76160|PICASSO Pablo, 1881-1973    |PICASSO      |   0|E
> 76161|PICASSO Loulou, *1958       |PICASSOLoulou|   0|F
> 76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo |   6|E
> (3 rows)
>
> real 11.52 *
> user 0.02
> sys 0.01
>
> With upper(...) and like upper(%...%)
> --------------------------------------
> % time -p psql mayer98 -c "select * from artistes where upper(nom) like
> upper('%PICASSO%');"
>   ref|nom                         |nom_court    |type|nationalite
> -----+----------------------------+-------------+----+-----------
> 76160|PICASSO Pablo, 1881-1973    |PICASSO      |   0|E
> 76161|PICASSO Loulou, *1958       |PICASSOLoulou|   0|F
> 76162|(A) PICASSO Pablo, 1881-1973|PICASSOPablo |   6|E
> (3 rows)
>
> real 4.44 *
> user 0.03
> sys 0.00
>
> Relsult: it seem to be 2.6 time more optimised to use upper(...) like
> upper('%...%') ???
>
> JK
>

This is interesting.  Does anyone have an idea why this is happening?
We do need a faster regular expression library, and I am hoping the
original author, Henry Spencer, will complete the rewrite he is doing
soon.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |