Thread: 'like' refuses to use an index???
Hi, everybody!
I just ran into a weird problem on 7.3.4.
Here is a simple testcase:
rapidb=# create table nametab (name text);
CREATE TABLE
rapidb=# create index name_idx on nametab(name);
CREATE INDEX
rapidb=# set enable_seqscan=false;
SET
rapidb=# set enable_sort=false;
SET
rapidb=# explain select * from nametab where name like 'blah%';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on nametab (cost=100000000.00..100000022.50 rows=5 width=32)
Filter: (name ~~ 'blah%'::text)
(2 rows)
rapidb=# explain select * from nametab where name like 'blah%' order by name;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using name_idx on nametab (cost=0.00..54.50 rows=5 width=32)
Filter: (name ~~ 'blah%'::text)
(2 rows)
See - the first query wants to use seqscan, even though I am explicitly
telling it not to.
The second query does use the index for sorting (good), but still not
for the condition.
Does anyone have any idea what could be wrong here?
I'd greatly appreciate that...
Thanks a lot!
Dima.
P.S. I don't think this has anything to do with the table being empty -
first of all this is just a simple testcase, my real table has about 120
million rows (and I just analyzed it a few minutes ago).... also the
problem seems to only be with 'like' - if you replace 'like' with '=' in
the above query then it *will* use the index, even though the table is
still empty
Dima Tkach <dmitry@openratings.com> writes:
> Does anyone have any idea what could be wrong here?
You didn't initdb in C locale ...
regards, tom lane
Tom Lane wrote: >Dima Tkach <dmitry@openratings.com> writes: > > >>Does anyone have any idea what could be wrong here? >> >> > >You didn't initdb in C locale ... > > regards, tom lane > > Ouch! Is there any way to fix that without recreating the database? Also, are you sure about this? Because the text comparison operators do seem to work fine... name like 'blah%' does not work, but name >= 'blah' and name < 'blai' *does*... aren't these locale-dependent too? Thanks a lot for your help! Dima
Quoting Dima Tkach <dmitry@openratings.com>:
> Tom Lane wrote:
>
> >Dima Tkach <dmitry@openratings.com> writes:
> >
> >
> >>Does anyone have any idea what could be wrong here?
> >>
> >>
> >
> >You didn't initdb in C locale ...
> >
> > regards, tom lane
> >
> >
> Ouch!
> Is there any way to fix that without recreating the database?
> Also, are you sure about this? Because the text comparison operators do
> seem to work fine...
>
> name like 'blah%' does not work, but name >= 'blah' and name < 'blai'
> *does*... aren't these locale-dependent too?
>
> Thanks a lot for your help!
>
> Dima
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
I wanted to know this too because I notice that using like with wildcards
appears to be similar to a regular expression in that the index is not used.
This is what I have...
ethernet=# select version();
version
----------------------------------------------------------------------
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
(1 row)
ethernet=# \d vendors
Table "public.vendors"
Column | Type | Modifiers
---------+-----------------------+-----------
header | character(6) |
company | character varying(80) |
Indexes:
"vender_id_idx" btree (header)
ethernet=# explain select * from vendors where header like '000423';
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 width=68)
Index Cond: (header = '000423'::bpchar)
Filter: (header ~~ '000423'::text)
(3 rows)
Ok, that made sense-
ethernet=# explain select * from vendors where header like '%000423%';
QUERY PLAN
----------------------------------------------------------
Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68)
Filter: (header ~~ '%000423%'::text)
(2 rows)
This didn't make sense until I did...
ethernet=# explain select * from vendors where header like '0004%';
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 width=68)
Index Cond: ((header >= '0004'::bpchar) AND (header < '0005'::bpchar))
Filter: (header ~~ '0004%'::text)
(3 rows)
which again made sense because of the header's size but both-
ethernet=# explain select * from vendors where header ~* '0004';
QUERY PLAN
-----------------------------------------------------------
Seq Scan on vendors (cost=0.00..151.15 rows=58 width=68)
Filter: (header ~* '0004'::text)
(2 rows)
ethernet=# explain select * from vendors where header ~* '000423';
QUERY PLAN
----------------------------------------------------------
Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68)
Filter: (header ~* '000423'::text)
(2 rows)
are sequentially scanned which means that regex's do not use indexes. Is that
right also?
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com
Nah... This is a different story - for teh index to be useful, the *beginning* of your search string must be known. So "like '00423%" and "~ '^00423'" should both work, but "like '%423'" and "~ '00423'" both won't - it's like searching a telephone book for somebody, whose last name ends with "erry" (as opposed to begins with "Perr"). Dima Keith C. Perry wrote: >I wanted to know this too because I notice that using like with wildcards >appears to be similar to a regular expression in that the index is not used. >This is what I have... > >ethernet=# select version(); > version >---------------------------------------------------------------------- > PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 >(1 row) > >ethernet=# \d vendors > Table "public.vendors" > Column | Type | Modifiers >---------+-----------------------+----------- > header | character(6) | > company | character varying(80) | >Indexes: > "vender_id_idx" btree (header) > > >ethernet=# explain select * from vendors where header like '000423'; > QUERY PLAN >--------------------------------------------------------------------------------- > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 width=68) > Index Cond: (header = '000423'::bpchar) > Filter: (header ~~ '000423'::text) >(3 rows) > > >Ok, that made sense- > >ethernet=# explain select * from vendors where header like '%000423%'; > QUERY PLAN >---------------------------------------------------------- > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > Filter: (header ~~ '%000423%'::text) >(2 rows) > >This didn't make sense until I did... > >ethernet=# explain select * from vendors where header like '0004%'; > QUERY PLAN >--------------------------------------------------------------------------------- > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 width=68) > Index Cond: ((header >= '0004'::bpchar) AND (header < '0005'::bpchar)) > Filter: (header ~~ '0004%'::text) >(3 rows) > >which again made sense because of the header's size but both- > >ethernet=# explain select * from vendors where header ~* '0004'; > QUERY PLAN >----------------------------------------------------------- > Seq Scan on vendors (cost=0.00..151.15 rows=58 width=68) > Filter: (header ~* '0004'::text) >(2 rows) > >ethernet=# explain select * from vendors where header ~* '000423'; > QUERY PLAN >---------------------------------------------------------- > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > Filter: (header ~* '000423'::text) >(2 rows) > >are sequentially scanned which means that regex's do not use indexes. Is that >right also? > > >
> You didn't initdb in C locale ... The documentation section on localization could use some enhancements and maybe some more examples. The 'c' locale isn't very well defined in the docs, except to say that it is 'special' and is the default if no other locale is defined. That section doesn't mention that you need that locale to get 'like' to use an index, for example. However, I think RH always sets the LANG environmental variable, so that's going to be picked up by initdb, which means that the C locale will NOT be used unless specifically asked for. Other OS packages may also force the choice of a default LANG value. Dumb question of the hour: How does one find out what locale a DB is initialized in? -- Mike Nolan
Mike Nolan <nolan@gw.tssi.com> writes:
> However, I think RH always sets the LANG environmental variable, so
> that's going to be picked up by initdb, which means that the C locale
> will NOT be used unless specifically asked for. Other OS packages may
> also force the choice of a default LANG value.
Yeah. There have been some discussions on pgsql-hackers about
defaulting to C locale instead of honoring LANG, but we haven't done
anything.
> Dumb question of the hour: How does one find out what locale a
> DB is initialized in?
In 7.4, you can just "show lc_collate". In prior versions you need
to use pg_controldata to see what's stored in pg_control.
BTW, 7.4 also has a specialized index opclass that can be used to create
LIKE-compatible indexes even if you are using a non-C locale.
regards, tom lane
Quoting Dima Tkach <dmitry@openratings.com>: > Nah... > This is a different story - for teh index to be useful, the *beginning* > of your search string must be known. > So "like '00423%" and "~ '^00423'" should both work, but "like '%423'" > and "~ '00423'" both won't - it's like searching a telephone book for > somebody, whose last name ends with "erry" (as opposed to begins with > "Perr"). > > Dima > > > Keith C. Perry wrote: > > >I wanted to know this too because I notice that using like with wildcards > >appears to be similar to a regular expression in that the index is not used. > > >This is what I have... > > > >ethernet=# select version(); > > version > >---------------------------------------------------------------------- > > PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 > >(1 row) > > > >ethernet=# \d vendors > > Table "public.vendors" > > Column | Type | Modifiers > >---------+-----------------------+----------- > > header | character(6) | > > company | character varying(80) | > >Indexes: > > "vender_id_idx" btree (header) > > > > > >ethernet=# explain select * from vendors where header like '000423'; > > QUERY PLAN > > >--------------------------------------------------------------------------------- > > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 > width=68) > > Index Cond: (header = '000423'::bpchar) > > Filter: (header ~~ '000423'::text) > >(3 rows) > > > > > >Ok, that made sense- > > > >ethernet=# explain select * from vendors where header like '%000423%'; > > QUERY PLAN > >---------------------------------------------------------- > > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > > Filter: (header ~~ '%000423%'::text) > >(2 rows) > > > >This didn't make sense until I did... > > > >ethernet=# explain select * from vendors where header like '0004%'; > > QUERY PLAN > > >--------------------------------------------------------------------------------- > > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 > width=68) > > Index Cond: ((header >= '0004'::bpchar) AND (header < '0005'::bpchar)) > > Filter: (header ~~ '0004%'::text) > >(3 rows) > > > >which again made sense because of the header's size but both- > > > >ethernet=# explain select * from vendors where header ~* '0004'; > > QUERY PLAN > >----------------------------------------------------------- > > Seq Scan on vendors (cost=0.00..151.15 rows=58 width=68) > > Filter: (header ~* '0004'::text) > >(2 rows) > > > >ethernet=# explain select * from vendors where header ~* '000423'; > > QUERY PLAN > >---------------------------------------------------------- > > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > > Filter: (header ~* '000423'::text) > >(2 rows) > > > >are sequentially scanned which means that regex's do not use indexes. Is > that > >right also? > > > > > > > > Ahhh, so it is!! So let me ask you this. In order to build an index that would be able to handle something like "lastname like '%erry'", would you need that full text search patch in contrib (tsearch?) or could you do it with an index on a function? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Quoting Mike Nolan <nolan@gw.tssi.com>: > > You didn't initdb in C locale ... > > The documentation section on localization could use some enhancements > and maybe some more examples. > > The 'c' locale isn't very well defined in the docs, except to say that it > is 'special' and is the default if no other locale is defined. That > section doesn't mention that you need that locale to get 'like' to > use an index, for example. > > However, I think RH always sets the LANG environmental variable, so > that's going to be picked up by initdb, which means that the C locale > will NOT be used unless specifically asked for. Other OS packages may > also force the choice of a default LANG value. > > Dumb question of the hour: How does one find out what locale a > DB is initialized in? > -- > Mike Nolan > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > According to the doc its the pg_controldata utility but when I run it, I get an error: "could not open file "-D/global/pg_control" for reading: No such file or directory" I wonder if that is because I didn't use a locale when I initialized the database. (My locale -a return "C") -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Keith C. Perry wrote: >Ahhh, so it is!! So let me ask you this. In order to build an index that would >be able to handle something like "lastname like '%erry'", would you need that >full text search patch in contrib (tsearch?) or could you do it with an index on >a function? > > I suppose, if this is all you need, you could just write a function, that returns it input reversed, and then index by that function...
Quoting Tom Lane <tgl@sss.pgh.pa.us>: > Mike Nolan <nolan@gw.tssi.com> writes: > > However, I think RH always sets the LANG environmental variable, so > > that's going to be picked up by initdb, which means that the C locale > > will NOT be used unless specifically asked for. Other OS packages may > > also force the choice of a default LANG value. > > Yeah. There have been some discussions on pgsql-hackers about > defaulting to C locale instead of honoring LANG, but we haven't done > anything. > > > Dumb question of the hour: How does one find out what locale a > > DB is initialized in? > > In 7.4, you can just "show lc_collate". In prior versions you need > to use pg_controldata to see what's stored in pg_control. Show the pg_controldata program work in 7.4? If not, shouldn't it be removed from the docs in favor of the show method of finding out the locale. > BTW, 7.4 also has a specialized index opclass that can be used to create > LIKE-compatible indexes even if you are using a non-C locale. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
> Mike Nolan <nolan@gw.tssi.com> writes: > > However, I think RH always sets the LANG environmental variable, so > > that's going to be picked up by initdb, which means that the C locale > > will NOT be used unless specifically asked for. Other OS packages may > > also force the choice of a default LANG value. > > Yeah. There have been some discussions on pgsql-hackers about > defaulting to C locale instead of honoring LANG, but we haven't done > anything. Hmm. is the 'C' locale going to be faster than SQL_ASCII? Second dumb question: What is there about SQL_ASCII that a 'like' cannot use indexes? -- Mike Nolan
"Keith C. Perry" <netadmin@vcsn.com> writes:
> Show the pg_controldata program work in 7.4? If not, shouldn't it be removed
> from the docs in favor of the show method of finding out the locale.
It still works, but you're right, the docs should suggest the easier
way... fixed ...
regards, tom lane
Mike Nolan writes: > Hmm. is the 'C' locale going to be faster than SQL_ASCII? The first is a locale, the second is an encoding. They are not related.
El Mar 30 Dic 2003 19:06, Tom Lane escribió:
> Mike Nolan <nolan@gw.tssi.com> writes:
> > However, I think RH always sets the LANG environmental variable, so
> > that's going to be picked up by initdb, which means that the C locale
> > will NOT be used unless specifically asked for. Other OS packages may
> > also force the choice of a default LANG value.
>
> Yeah. There have been some discussions on pgsql-hackers about
> defaulting to C locale instead of honoring LANG, but we haven't done
> anything.
Which would the loses be in using C locale, for example, for Spanish speeking
people, where we have acute letters and ñ (charset iso-8859-1)? Would it be
only ordering?
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Tom Lane wrote: > BTW, 7.4 also has a specialized index opclass that can be used to create > LIKE-compatible indexes even if you are using a non-C locale. > Where is that documented? Is it automatic for text fields?
Joseph Shraibman writes: > Tom Lane wrote: >> BTW, 7.4 also has a specialized index opclass that can be used to >> create LIKE-compatible indexes even if you are using a non-C locale. >> > Where is that documented? http://www.postgresql.org/docs/current/static/indexes-opclass.html > Is it automatic for text fields? No.
Hi,
Tom Lane wrote, On 12/30/2003 11:06 PM:
> BTW, 7.4 also has a specialized index opclass that can be used to create
> LIKE-compatible indexes even if you are using a non-C locale.
I do some tests:
PostgreSQL 7.4.1
on a varchar(255) column:
#CREATE INDEX crossref_oidx ON crossref (cr_origmark varchar_pattern_ops);
#ANALYZE crossref;
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark ilike 'gie%'
Seq Scan on crossref ...
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark like 'GIE%';
Index Scan using crossref_oidx on crossref ...
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark ilike 'GIE%';
Seq Scan on crossref ...
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark like
upper('gie%');
Index Scan using crossref_oidx on crossref ...
In case of ilike, index is not used :(
If like used the condition is:
Index Cond: (((cr_origmark)::text ~>=~ 'GIE'::character varying) AND
((cr_origmark)::text ~<~ 'GIF'::character varying))
However ilike use the old way:
Filter: ((cr_origmark)::text ~~* 'GIE%'::text)
is this bug, or this is the right way?
C.