Re: index on lower(column) is very slow

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?

In response to

Responses

Browse pgsql-general by date

  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