indices and query for searching, ordering by lower case

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: indices and query for searching, ordering by lower case
Date: 2012-02-02 18:27:57
Message-ID: 4F2AD5AD.5090600@pukkasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sfpug


I have a query that lists people alphabetically like:

select ... from tbl order by lower(lastname), lower(firstname)

Basically every query on the table (or when joining to it) orders the
results by name in this way. We also frequently search for people.
Currently we use a two-stage strategy that I think can be improved. We
first try for a direct match ("brian"):

WHERE lower(firstname) = 'brian' OR lower(lastname) = 'brian' OR email =
'brian'

(Emails are forced lower case and are indexed) If that doesn't return
results, we fall back to an ILIKE:

WHERE lastname ilike '%brian%' or firstname ilike '%brian%' or email
ilike '%brian%'

If we have more than one search term like "john smith", we generate a
set of ilike comparisons for each word.

My question is two parts:

1. Are two queries, one direct and a fallback using ILIKE/etc better or
should I instead use something like full text search with perhaps a
multi-column index?

2. What's the best way to index these columns for sorting? I have
created an index each on lower(firstname) and lower(lastname) which
helps with the direct match but isn't being used in the ILIKE query.

I also tested a multi-column index on (lower(lastname),
lower(firstname)) and the index is used twice but the planner cost is
significantly higher and takes longer. Are two lower() indices best or
is there a single multi-column index that would be better knowing that
the query always sorts on both columns?

Thanks!

Brian

Browse sfpug by date

  From Date Subject
Next Message Josh Berkus 2012-02-15 22:26:03 Streaming link for tonight.
Previous Message Josh Berkus 2012-01-30 01:35:45 February SFPUG