Docs off on ILIKE indexing?

Started by Magnus Haganderalmost 20 years ago5 messages

Magnus Hagander

mha@sollentuna.net

almost 20 years ago

http://www.postgresql.org/docs/8.1/static/indexes-types.html

says:
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the pattern is a
constant and is anchored to the beginning of the string - for example,
col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

But really, does it use indexes for ILIKE? (And I assume the same holds
for case insensitive regexp matching)

(If it does, can someone enlighten me on what I have to do - I have a
system with C locale that refuses to do it for ILIKE, but works just
fine for LIKE. My workaronud for now is to create an index on lower(foo)
and then use WHERE lower(foo) LIKE 'bar%' which works fine - but it does
require an extra index..)

So. Am I off, or are the docs? Or is it just me who can't read ;-)

//Magnus

tgl@sss.pgh.pa.us

almost 20 years ago

In reply to: Magnus Hagander (#1)

Re: Docs off on ILIKE indexing?

"Magnus Hagander" <mha@sollentuna.net> writes:

http://www.postgresql.org/docs/8.1/static/indexes-types.html
says:
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the pattern is a
constant and is anchored to the beginning of the string - for example,
col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

But really, does it use indexes for ILIKE?

That's pretty poorly phrased. For ILIKE it'll only work if there's a
prefix of the pattern that's not letters (and hence is unaffected by
the case-folding issue).

regards, tom lane

Magnus Hagander

mha@sollentuna.net

almost 20 years ago

In reply to: Tom Lane (#2)

Re: Docs off on ILIKE indexing?

http://www.postgresql.org/docs/8.1/static/indexes-types.html
says:
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the

pattern is a

constant and is anchored to the beginning of the string -

for example,

col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

But really, does it use indexes for ILIKE?

That's pretty poorly phrased. For ILIKE it'll only work if
there's a prefix of the pattern that's not letters (and hence
is unaffected by the case-folding issue).

Ahh. That explains it. Perfectly logical.
And yes, that's pretty poorly phrased - at least I didn't understand it
:-)

//Magnus

Import Notes

Resolved by subject fallback

jnasby@pervasive.com

almost 20 years ago

In reply to: Magnus Hagander (#3)

Re: [HACKERS] Docs off on ILIKE indexing?

On Tue, Jan 17, 2006 at 03:44:30PM +0100, Magnus Hagander wrote:

http://www.postgresql.org/docs/8.1/static/indexes-types.html
says:
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the

pattern is a

constant and is anchored to the beginning of the string -

for example,

col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

But really, does it use indexes for ILIKE?

That's pretty poorly phrased. For ILIKE it'll only work if
there's a prefix of the pattern that's not letters (and hence
is unaffected by the case-folding issue).

Ahh. That explains it. Perfectly logical.
And yes, that's pretty poorly phrased - at least I didn't understand it
:-)

I was going to submit a patch on this, and the best way seems to be
adding a note to 'ILIKE', specifying that it will only work if there's a
prefix to the pattern that isn't letters. Is there a standard way to tag
a word indicating that there's a note?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

pgman@candle.pha.pa.us

almost 20 years ago

In reply to: Jim C. Nasby (#4)

1 attachment(s)

Re: [HACKERS] Docs off on ILIKE indexing?

I have applied the following patch to clarify the current behavior.
Thanks.

---------------------------------------------------------------------------

Jim C. Nasby wrote:

On Tue, Jan 17, 2006 at 03:44:30PM +0100, Magnus Hagander wrote:

http://www.postgresql.org/docs/8.1/static/indexes-types.html
says:
The optimizer can also use a B-tree index for queries involving the
pattern matching operators LIKE, ILIKE, ~, and ~*, if the

pattern is a

constant and is anchored to the beginning of the string -

for example,

col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

But really, does it use indexes for ILIKE?

That's pretty poorly phrased. For ILIKE it'll only work if
there's a prefix of the pattern that's not letters (and hence
is unaffected by the case-folding issue).

Ahh. That explains it. Perfectly logical.
And yes, that's pretty poorly phrased - at least I didn't understand it
:-)

I was going to submit a patch on this, and the best way seems to be
adding a note to 'ILIKE', specifying that it will only work if there's a
prefix to the pattern that isn't letters. Is there a standard way to tag
a word indicating that there's a note?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Attachments:

/bjm/difftext/plainDownload

Index: doc/src/sgml/indices.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v
retrieving revision 1.55
diff -c -c -r1.55 indices.sgml
*** doc/src/sgml/indices.sgml	7 Nov 2005 17:36:44 -0000	1.55
--- doc/src/sgml/indices.sgml	18 Jan 2006 21:24:06 -0000
***************
*** 141,157 ****
  
    <para>
     The optimizer can also use a B-tree index for queries involving the
!    pattern matching operators <literal>LIKE</>,
!    <literal>ILIKE</literal>, <literal>~</literal>, and
!    <literal>~*</literal>, <emphasis>if</emphasis> the pattern is a constant
!    and is anchored to the beginning of the string &mdash; for example,
!    <literal>col LIKE 'foo%'</literal> or <literal>col ~ '^foo'</literal>,
!    but not <literal>col LIKE '%bar'</literal>.  However, if your server does
!    not use the C locale you will need to create the index with a
!    special operator class to support indexing of pattern-matching queries.
!    See <xref linkend="indexes-opclass"> below.
    </para>
  
    <para>
     <indexterm>
      <primary>index</primary>
--- 141,161 ----
  
    <para>
     The optimizer can also use a B-tree index for queries involving the
!    pattern matching operators <literal>LIKE</> and <literal>~</literal>
!    <emphasis>if</emphasis> the pattern is a constant and is anchored to
!    the beginning of the string &mdash; for example, <literal>col LIKE
!    'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
!    <literal>col LIKE '%bar'</literal>. However, if your server does not
!    use the C locale you will need to create the index with a special
!    operator class to support indexing of pattern-matching queries. See
!    <xref linkend="indexes-opclass"> below. It is also possible to use
!    B-tree indexes for <literal>ILIKE</literal> and
!    <literal>~*</literal>, but only if the pattern starts with
!    non-alphabetic characters, i.e. characters that are not affected by
!    upper/lower case conversion.
    </para>
  
+ 
    <para>
     <indexterm>
      <primary>index</primary>