Joseph Shraibman wrote:
I have this index:
"directory_lower_username_seg_key" unique, btree (lower(username)
text_pattern_ops, seg)
... but my query refuses to use that index.
[local]:owl=>explain select * from directory where lower(username) =
'jks@xxxxxxxxxxxxxxx';
QUERY PLAN
---------------------------------------------------------------
Seq Scan on directory (cost=0.00..860.75 rows=102 width=575)
Filter: (lower(username) = 'jks@xxxxxxxxxxxxxxx'::text)
(2 rows)
[local]:owl=>CREATE INDEX directory_lower_username_seg_key1 ON directory
(lower(username),seg);
CREATE INDEX
[local]:owl=>explain select * from directory where lower(username) =
'jks@xxxxxxxxxxxxxxx';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Index Scan using directory_lower_username_seg_key1 on directory
(cost=0.00..377.01 rows=102 width=575)
Index Cond: (lower(username) = 'jks@xxxxxxxxxxxxxxx'::text)
(2 rows)
[local]:owl=>select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
(1 row)
So what gives?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
What happens if you 'SET enable_seqscan TO OFF' and try the query
again? I've had a couple of instances where the planner just doesn't
like my index but once it is told to use it I get a nice performance boost.
By the way, I'm still kind of a beginner so if someone else has a
more enlightened suggestion, try their ideas first.
Madison
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
