On Tue, May 22, 2007 at 12:12:51PM -0400, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> 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 -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | 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/ ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend