Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.8.8/8.8.8) with ESMTP id WAA19132 for ; Sun, 20 Sep 1998 22:26:37 -0400 (EDT) (envelope-from maillist@candle.pha.pa.us) Received: (from maillist@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id VAA15742; Sun, 20 Sep 1998 21:39:33 -0400 (EDT) From: Bruce Momjian Message-Id: <199809210139.VAA15742@candle.pha.pa.us> Subject: Re: [SQL] How to Make Case InSensitive??? In-Reply-To: <3600C4BF.C99176ED@mandanet.ch> from Jerome Knobl at "Sep 17, 1998 10:13:51 am" To: jknobl@mandanet.ch (Jerome Knobl) Date: Sun, 20 Sep 1998 21:39:33 -0400 (EDT) Cc: herouth@oumail.openu.ac.il, pgsql-sql@postgreSQL.org, henry@zoo.utoronto.ca X-Mailer: ELM [version 2.4ME+ PL43 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > 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. |