Skip site navigation (1) Skip section navigation (2)

Re: like/ilike improvements

From: mark(at)mark(dot)mielke(dot)cc
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>,ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: like/ilike improvements
Date: 2007-05-22 18:44:25
Message-ID: 20070522184425.GA13399@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Tue, May 22, 2007 at 12:12:51PM -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > ... It turns out (according to the analysis) that the 
> > only time we actually need to use NextChar is when we are matching an 
> > "_" in a like/ilike pattern.
> I thought we'd determined that advancing bytewise for "%" was also risky,
> in two cases:
> 1. Multibyte character set that is not UTF8 (more specifically, does not
> have a guarantee that first bytes and not-first bytes are distinct)
> 2. "_" immediately follows the "%".

Have you considered a two pass approach? First pass - match on bytes.
Only if you find a match with the first pass, start a second pass to
do a 'safe' check?

Are there optimizations to recognize whether the index was created as
lower(field) or upper(field), and translate ILIKE to the appropriate
one?

Cheers,
mark

-- 
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-05-22 19:04:32
Subject: Re: like/ilike improvements
Previous:From: Andrew - SupernewsDate: 2007-05-22 17:40:36
Subject: Re: like/ilike improvements

pgsql-patches by date

Next:From: Tom LaneDate: 2007-05-22 19:04:32
Subject: Re: like/ilike improvements
Previous:From: Andrew - SupernewsDate: 2007-05-22 17:40:36
Subject: Re: like/ilike improvements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group