Re: like/ilike improvements

Guillaume Smet wrote:
> On 9/19/07, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>> Can you run the same set of tests in a single byte encoding like latin1?
>>
>
> Here are the results (each query was executed several times before this result):
>
> ** 8.1 **
> cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve
> ILIKE '%hocus pocus%';
> numeve
> -----------
> 900024298
> 87578
> (2 rows)
>
> Time: 135.877 ms
>
> ** 8.2 **
>
> cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve
> ILIKE '%hocus pocus%';
> numeve
> -----------
> 900024298
> 87578
> (2 rows)
>
> Time: 111.595 ms
>
> ** 8.3 **
>
> cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve
> ILIKE '%hocus pocus%';
> numeve
> -----------
> 900024298
> 87578
> (2 rows)
>
> Time: 160.582 ms
>
> Results are quite surprising but there's no error, I checked them
> several times...
>
>
>
>

No, what this suggests to me is that it might have been a mistake to
make the single byte case work like the multi-byte case, by pre-lowering
the string, as we did back in May. It confirms my suspicion that the
lower() code is the culprit. It should really be lightning fast.

Can you retry both sets of tests but this time in C locale? The lower()
code works differently in C locale, and it might be that we need to look
at tweaking just one case.

cheers

andrew