Re: Problem with text_pattern_ops

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(at)selectacast(dot)net';
> QUERY PLAN
> ---------------------------------------------------------------
> Seq Scan on directory (cost=0.00..860.75 rows=102 width=575)
> Filter: (lower(username) = 'jks(at)selectacast(dot)net'::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(at)selectacast(dot)net';
> 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(at)selectacast(dot)net'::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