Skunk Projects..

"Significant progress doesn't come from the formal planning process of an American corporation. It comes from a couple of guys doing something that hasn't been set down on a list."

– William McGowan, executive

Careful with case insensitive searches

I was looking at optimizing a slow login script and narrowed it down to the following slow query:

% select p.party_id from parties p where email ilike ' demoemail@demo.com'

ilike does a case insensitive match on the email column.
Although email is indexed, the case insensitive version is not so
the database therefore has to look at the whole table to
narrow down the possible match.

Next step is to add a funcational index:

% create index parties_lower_email on parties (lower(email));

The query was still slow. (the postgres documentation alluded that ilike would
use the functional index, but when I asked for the query explanation, it didnt').


% explain
% select p.party_id from parties p where email ilike ' demoemail@demo.com' QUERY PLAN ------------------------------------------------------------
Seq Scan on parties p (cost=0.00..20766.76 rows=1 width=29)
Filter: ((email)::text ~~* ' demoemail@demo.com'::text)
(2 rows)

Postgres (7.4) was still doing a sequential scan (10 seconds)

The query became lightning fast when I queried on lower(email) directly:

% select p.party_id from parties p where lower(email) like ' demoemail@demo.com '


And the explain plan showed it was now using the index.

% explain
% select p.party_id from parties p where lower(email) like 'demoemail@demo.com'
QUERY PLAN --------------------------------------------------------------------------------------
Index Scan using parties_lower_email on parties p (cost=0.00..232.62 rows=58 width=29)
Index Cond: (lower((email)::text) = 'demoemail@demo.com '::text)
Filter: (lower((email)::text) ~~ 'demoemail@demo.com::text)


2 lessons:
a) If you are restraining by some variation of a column, make sure you have a functional index
b) Make sure it is used