Re: [PATCHES] LIKE vs regex queries

> I have a database with about 250,000 entries in a table, PG 7.5, One
> of the fields is text. LIKE queries on this field execute much faster
> than the equivalent regex queries.
>
> Is this what you would expect? Should I prefer LIKE to regex?
> (I'm a regex fan, but the performance hit seems steep.)
>
> I've pasted sample output in below.
>
> order=# select dnum from item where description LIKE '%Ushio%';
> Time: 855.540 ms
>
> order=# select dnum from item where description ~ 'Ushio';
> Time: 2409.043 ms

From the source code, the computed automaton for ~ seems to be cached, so
this is not an explanation. However the RE engine is quite generic.

The code for LIKE is quite hardwired to the special case, as there is only
_ and % to be taken care of.

So it seems that if a LIKE regular expression is enough, then it is a much
better choice. SIMILAR TO is translated to ~, so it should be slow.

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr