On Tue, May 22, 2007 at 12:12:51PM -0400, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.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@mielke.cc / markm@ncf.ca / markm@nortel.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
bindthem...
http://mark.mielke.cc/