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@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026