Re: best way to manage indexes

On 24/12/2009 6:10 AM, Jamie Kahgee wrote:
> what would be considered "best practice" for my situation?
>
> I have a table *member*, with column *name *that I want to put an index
> on, because it is searched quiet frequently. When I create my sql search
> string, the name will consist only of alpha-numeric characters and be
> compared against lowercase matches.

As already noted, it's worth looking into full-text search.

> SELECT *
>    FROM member
>   WHERE lower(regexp_replace(member_name, '[^[:alnum:]]', '', 'g')) ~*
> 'search_string'
>      OR lower(metaphone(name, 4)) = lower(metaphone('search_string', 4));
>
> is it better to create an index that matches my search?
> create index member_name_idx on member (lower(regexp_replace(name,
> '[^[:alnum:]]', '', 'g')));

You can't really build an index on the regex match expression ( ~* )
above, because the results of the expression depend on `search_string'
via the non-btree-indexable operator ~* . Btree indexes can only be used
for equality, inequality, greater-than or less-than operators.

If you can use one of "=", "!=", "<" or ">" as your test expression
instead, then you could usefully build a functional index. In that case,
you could wrap the expression that mangles 'member_name' in an immutable
SQL function. You'd then create the index on that, and use that function
in your queries, eg:

    WHERE simplify_member(member_name) = 'search_string'

The function makes life MUCH easier on the planner, since it can easily
tell when your search expressions match the functional index. It also
makes maintenance easier. If you decide to change the function (say, to
add to the allowed char list) despite it being marked immutable, you
will have to drop and re-create the index.

However, if you can't use a btree-indexable operator when comparing
against your search string, the index can't be used anyway. You'd have
to put something together using GiST, if it's even possible at all.

> do I need two indexes?  one for both search parameters (regexp & metaphone)?

Yes.

--
Craig Ringer