Re: [GENERAL] Generic search

> >hygea=> explain select * from comuni where nome = 'A%';
> >NOTICE: QUERY PLAN:
> >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84)
> >^^^^^
>
> The question was about LIKE, not =. Because LIKE uses regexp-style matching and
> we have no substring index functionality, it cannot use the index. If you're
> always matching on the first character, you can do something like fulltextindex
> does and use triggers and a second (indexed) table to be able to match on the
> first character only.

If the start of the search string is anchored. From the FAQ:

When using wild-card operators like <I>LIKE</I> or <I>~,</I> indices can
only be used if the beginning of the search is anchored to the start of
the string. So, to use indices, <I>LIKE</I> searches can should not
begin with <I>%,</I> and <I>~</I>(regular expression searches) should
start with <I>^.</I>

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026