472,353 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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

Nov 12 '05 #1
17 6071
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

Nov 12 '05 #2
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

Nov 12 '05 #3
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

Nov 12 '05 #4
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

Nov 12 '05 #5
> 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

Nov 12 '05 #6
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

Nov 12 '05 #7
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

Nov 12 '05 #8
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

Nov 12 '05 #9
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

Nov 12 '05 #10
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

Nov 12 '05 #11
> 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

Nov 12 '05 #12
"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)

Nov 12 '05 #13
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.

Nov 12 '05 #14
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

Nov 12 '05 #15
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

Nov 12 '05 #16
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

Nov 12 '05 #17
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

Nov 12 '05 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: kurtk | last post by:
I have install Apache and php5. I can execute php with no problem. However, I would like index.php to be recognized and executed the way that...
1
by: python | last post by:
Is there an Index server available in Python? For example: I have large intranet with several servers and I would like to index documents like ...
15
by: Steve M | last post by:
Hello, I'm trying to figure out the index position of a tuple member. I know the member name, but I need to know the members index position. I...
10
by: deko | last post by:
I understand it's possible to make a composite Primary Key by holding down the control key and selecting multiple fields, then right-clicking and...
24
by: Henrik Steffen | last post by:
hello all, on my master-db-server i'm running postgres 7.4.1, and I have got two slave-servers running postgres 7.4.2 running the following...
3
by: Kevin Macdonald | last post by:
I expected Postgresql to use an indexed access method, but in certain cases it is using a sequential scan. Details are below: Table: ...
1
by: rloef | last post by:
I have a multi-million row table with three indexes in MySQL-5.0.15. These indexes have the following number of distinct values: date 415 ...
2
by: Martin v. Löwis | last post by:
I've been working on PEP 353 for some time now. Please comment, in particular if you are using 64-bit systems. Regards, Martin PEP: 353...
6
by: xkenneth | last post by:
Looking to do something similair. I'm working with alot of timestamps and if they're within a couple seconds I need them to be indexed and removed...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.