Thread: Problem with text_pattern_ops
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?
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
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. >
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.
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?
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).
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.
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.