Re: index on lower(column) is very slow

On Fri, Mar 07, 2003 at 09:08:34AM -0500, Greg Stark wrote:
> Try "explain analyze" which will actually run the query and print timing
> information.
>
> Also, note that the number of records returned is probably a big factor here.
> The case-sensitive version is only returning 1 record whereas postgres expects
> the case=insensitive version to return 91 records. Try the case-sensitive
> version on a value that has a comparable number of records to for a better
> test.

I inserted a row with the unique value 'asdf(at)asdf(dot)com', and here are the
results (after running 'VACUUM ANALYZE'):

test=> EXPLAIN ANALYZE SELECT id, password FROM test WHERE email = 'asdf(at)asdf(dot)com';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using test_email_idx on test (cost=0.00..6.00 rows=1 width=16) (actual time=0.48..0.49 rows=1 loops=1)
Index Cond: (email = 'asdf(at)asdf(dot)com'::character varying)
Total runtime: 0.65 msec
(3 rows)

test=> EXPLAIN ANALYZE SELECT id, password FROM test WHERE lower(email) = 'asdf(at)asdf(dot)com';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using test_email_lc_idx on test (cost=0.00..292.28 rows=91 width=16) (actual time=0.47..0.47 rows=1 loops=1)
Index Cond: (lower((email)::text) = 'asdf(at)asdf(dot)com'::text)
Total runtime: 0.63 msec
(3 rows)

test=> DROP INDEX test_email_lc_idx;
DROP INDEX
test=> EXPLAIN ANALYZE SELECT id, password FROM test WHERE lower(email) = 'asdf(at)asdf(dot)com';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..449.74 rows=91 width=16) (actual time=109.09..109.39 rows=1 loops=1)
Filter: (lower((email)::text) = 'asdf(at)asdf(dot)com'::text)
Total runtime: 109.60 msec
(3 rows)

I'm not sure why the planner thinks there are 91 rows? But now I can
see that the index is working, and that's all that matters in the end.
Thanks for the tip!