'like' refuses to use an index???

[Dima Tkach]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Dima Tkach <dm****@openratings.com> writes:

Does anyone have any idea what could be wrong here?

You didn't initdb in C locale ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Dima Tkach]

Tom Lane wrote:

Dima Tkach <dm****@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 ma*******@postgresql.org

[Keith C. Perry]

Quoting Dima Tkach <dm****@openratings.com>:

Tom Lane wrote:

Dima Tkach <dm****@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 ma*******@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dima Tkach]

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?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Mike Nolan]

> 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

[Tom Lane]

Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Keith C. Perry]

Quoting Dima Tkach <dm****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Keith C. Perry]

Quoting Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Dima Tkach]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Keith C. Perry]

Quoting Tom Lane <tg*@sss.pgh.pa.us>:

Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mike Nolan]

> Mike Nolan <no***@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

"Keith C. Perry" <ne******@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[CoL]

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.

[Peter Eisentraut]

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martin Marques]

El Mar 30 Dic 2003 19:06, Tom Lane escribió:

Mike Nolan <no***@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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joseph Shraibman]

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?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Peter Eisentraut]

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/curre...s-opclass.html
Is it automatic for text fields?

No.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend