Thread: Problem with text_pattern_ops

Problem with text_pattern_ops

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

Re: Problem with text_pattern_ops

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

Re: Problem with text_pattern_ops

From
Joseph Shraibman
Date:

Madison Kelly wrote:
> Joseph Shraibman wrote:
>

>   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.

It still does a seqscan.
>

Re: Problem with text_pattern_ops

From
Stephan Szabo
Date:
On Tue, 26 Jul 2005, 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.

text_pattern_ops is an opclass for doing LIKE queries using the index, I
don't believe it's used for equality comparisons.

Re: Problem with text_pattern_ops

From
Joseph Shraibman
Date:

Stephan Szabo wrote:
> On Tue, 26 Jul 2005, 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.
>
>
> text_pattern_ops is an opclass for doing LIKE queries using the index, I
> don't believe it's used for equality comparisons.

Seems like a bug to me.  That operator can't do the operations equal,
less than, and greater than?

Re: Problem with text_pattern_ops

From
Stephan Szabo
Date:
On Tue, 26 Jul 2005, Joseph Shraibman wrote:

>
>
> Stephan Szabo wrote:
> > On Tue, 26 Jul 2005, 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.
> >
> >
> > text_pattern_ops is an opclass for doing LIKE queries using the index, I
> > don't believe it's used for equality comparisons.
>
> Seems like a bug to me.  That operator can't do the operations equal,
> less than, and greater than?

It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization).
The docs seem to say that it does a character by character comparison
rather than one using the collation thus being better for pattern
matching. I'd think letting it do <, <=, =, >=, > would have it giving the
wrong results for such queries (well, in non-C locales).


Re: Problem with text_pattern_ops

From
Joseph Shraibman
Date:

Stephan Szabo wrote:

> It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization).
> The docs seem to say that it does a character by character comparison
> rather than one using the collation thus being better for pattern
> matching. I'd think letting it do <, <=, =, >=, > would have it giving the
> wrong results for such queries (well, in non-C locales).

Well maybe queries of > < shouldn't be allowed because they would return
the wrong results, but surely equals is equals.

Re: Problem with text_pattern_ops

From
Stephan Szabo
Date:
On Tue, 26 Jul 2005, Joseph Shraibman wrote:

> Stephan Szabo wrote:
>
> > It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization).
> > The docs seem to say that it does a character by character comparison
> > rather than one using the collation thus being better for pattern
> > matching. I'd think letting it do <, <=, =, >=, > would have it giving the
> > wrong results for such queries (well, in non-C locales).
>
> Well maybe queries of > < shouldn't be allowed because they would return
> the wrong results, but surely equals is equals.

I don't know how any locales I have access to handle separate accent
characters, but I think in theory at least, that handling may be different
than character by character comparison.