Re: index on lower(column) is very slow
| От | valerian |
|---|---|
| Тема | Re: index on lower(column) is very slow |
| Дата | |
| Msg-id | 20030307060108.GA14118@hotpop.com обсуждение исходный текст |
| Ответ на | index on lower(column) is very slow (valerian <valerian2@hotpop.com>) |
| Ответы |
Re: index on lower(column) is very slow
|
| Список | 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@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?
В списке pgsql-general по дате отправления: