Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

sfpug by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group