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(at)candle(dot)pha(dot)pa(dot)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. |