[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    AW: AW: [HACKERS] Like vs '=' bug with indexing
From:       Zeugswetter Andreas SB  <ZeugswetterA () wien ! spardat ! at>
Date:       2001-01-31 13:07:09
[Download RAW message or body]


> > > I am reposting this because I'm not sure it actually
> > > made it to the list.
> > 
> > It did make it to the list, but can you give more
> > details whether or which of the 
> > following you use:
> > 	--enable-locale ?
> > 	--enable-multibyte ?
> > 	LANG=?
> > 	LC_COLLATE=?
> 
> I did not change any of the language or local
> information. My config like is:
> 
> ./configure --with-tcl --with-odbc
> --with-maxbackends=128 --prefix=/usr/local/pgsql 
> 
> > 
> > > I have a function to transform text into a
> > > pseudo-metaphone variable, take this example:
> > 
> > > Here is the problem: Depending on whether there is
> > an
> > > index or not, 'like' behaves differently. Here is
> > a
> > > transcript:
> > > 
> > > cddbsql=# select song, metatext(song) from cdsongs
> > > where metatext(song) like metatext('born to run')
> > > limit 1 ;
> > >     song     | metatext
> > > -------------+----------
> > >  Born To Run | brntorn
> > > (1 row)
> > >  
> > > cddbsql=# create index cdsongs_meta_song on
> > cdsongs
> > > (metatext(song)) ;
> > > CREATE
> > > cddbsql=# select song, metatext(song) from cdsongs
> > > where metatext(song) like metatext('born to run')
> > > limit 1 ;
> > >  song | metatext
> > > ------+----------
> > > (0
> > > rows)
> > 
> > While I do see, that this is bogous, I do not really understand why you 
> > use like in this case when your metatext function does not return any 
> > wildcards. A simple = should lead to the same result. Can you check that
> > with the index in place ? 
> 
> Oddly enough when I use '=' it works, but adding that
> means I have to special case when someone adds '%' at
> the end of the word, which means I will have to parse
> the string comming in. If I add the '%' sign to all
> queries, then I will not get the results intended.

Ah, I wonder wether it might be trailing blanks, that are involved here.
Is your return type of metatext() sql type text ? text and varchar are trailing
blank sensitive. Still sounds strange, that the seq scan ind index scan behaviors 
are different.

> > > This happens in both 7.0 and 7.1.

[prev in list] [next in list] [prev in thread] [next in thread]