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 по дате отправления: