Re: Re: LIKE and indexes?

From: "Alexander Jerusalem" <alexander(dot)jerusalem(at)pop(dot)chello(dot)at>

> Thanks for your answer, Alex!
>
> I've done an EXPLAIN and I saw that no index was used although my SQL
> statement had a wild card only at the end as you pointed out ('blah%'). My
> guess is that this is because of the locale support.

Try like '^blah%' - maybe the additional hint will help (but maybe not). How
many rows did PG think it was going to access in the explain?

- Richard Huxton

> Regards,
> Alexander Jerusalem
>
>
> At 01:22 15.03.01, adb wrote:
> >When in doubt, try the explain command
> >
> >Not exactly sure about postgres but in general LIKE can
> >only use an index in the case of LIKE "Something%"
> >
> >LIKE "%Something" or LIKE "%Something%"
> >won't use an index since it would have to scan the entire
> >index to find all matches.
> >
> >Alex.
> >
> >
> >
> >On Wed, 14 Mar 2001, Alexander Jerusalem wrote:
> >
> > > Hi,
> > >
> > > Can anyone telle me if and when a LIKE query uses an index? I've
compiled
> > > postgres with locale support. Does that have any influence indexes?
> > >
> > > thanks,
> > >
> > > Alexander Jerusalem
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>