Postgres Pro
Downloads
Home   >   mailing lists

Thread: best way to manage indexes

best way to manage indexes

From
Jamie Kahgee
Date:
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.

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')));
What if we decide we want to allow more characters in the search later - just have to remember to update the index?

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

perhaps there is a solution that I haven't thought of.  any input would be appreciated!

Thanks,
Jamie K.

Re: best way to manage indexes

From
Scott Marlowe
Date:
On Wed, Dec 23, 2009 at 3:10 PM, Jamie Kahgee <jamie.kahgee@gmail.com> 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.

SNIP

> perhaps there is a solution that I haven't thought of.  any input would be
> appreciated!

look up full text search.

Re: best way to manage indexes

From
Craig Ringer
Date:
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