I just realized that I was behind by one release, so I upgraded to pgsql
7.3.2, then added some more random data (18000 rows in the table now)
and ran 'VACUUM FULL ANALYZE' again. Here's the subsequent results:
test=> EXPLAIN SELECT id, password FROM test WHERE LOWER(email) = 'pwcm6@pgaxd6hhuteforp966cz';
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using test_email_lc_idx on test (cost=0.00..292.25 rows=91 width=16)
Index Cond: (lower((email)::text) = 'pwcm6@pgaxd6hhuteforp966cz'::text)
(2 rows)
test=> EXPLAIN SELECT id, password FROM test WHERE email = 'pwcm6@pgaxd6hhuteforp966cz';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using test_email_idx on test (cost=0.00..6.00 rows=1 width=16)
Index Cond: (email = 'pwcm6@pgaxd6hhuteforp966cz'::character varying)
(2 rows)
test=> drop INDEX test_email_lc_idx ;
DROP INDEX
test=> EXPLAIN SELECT id, password FROM test WHERE LOWER(email) = 'pwcm6@pgaxd6hhuteforp966cz';
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on test (cost=0.00..449.71 rows=91 width=16)
Filter: (lower((email)::text) = 'pwcm6@pgaxd6hhuteforp966cz'::text)
(2 rows)
But the index on lower(email) still seems abnormally slow. Does anyone
have any insight on what may be causing this?
