From: | valerian <valerian2(at)hotpop(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: index on lower(column) is very slow |
Date: | 2003-03-07 06:01:08 |
Message-ID: | 20030307060108.GA14118@hotpop.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)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(at)pgaxd6hhuteforp966cz'::text)
(2 rows)
test=> EXPLAIN SELECT id, password FROM test WHERE email = 'pwcm6(at)pgaxd6hhuteforp966cz';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using test_email_idx on test (cost=0.00..6.00 rows=1 width=16)
Index Cond: (email = 'pwcm6(at)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(at)pgaxd6hhuteforp966cz';
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on test (cost=0.00..449.71 rows=91 width=16)
Filter: (lower((email)::text) = 'pwcm6(at)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2003-03-07 06:23:07 | Re: Why are queries with subselects so slow? |
Previous Message | Bruce Momjian | 2003-03-07 05:43:24 | Re: Two questions about stored procedures |