Re: like/ilike improvements

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: like/ilike improvements
Date: 2007-09-20 00:14:56
Message-ID: 46F1BB80.9030502@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gokulakannan Somsundaram 2007-09-20 07:11:20 Improving the Performance of Full Table Updates
Previous Message Guillaume Smet 2007-09-19 22:08:59 Re: like/ilike improvements

Browse pgsql-patches by date

  From Date Subject
Next Message ITAGAKI Takahiro 2007-09-20 05:06:25 Various fixes for syslogger
Previous Message Neil Conway 2007-09-19 23:22:57 Re: pg_next_dst_boundary optimization