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 =
(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
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
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?
sfpug by date
|Next:||From: Josh Berkus||Date: 2012-02-15 22:26:03|
|Subject: Streaming link for tonight.|
|Previous:||From: Josh Berkus||Date: 2012-01-30 01:35:45|
|Subject: February SFPUG|