public inbox for [email protected]
help / color / mirror / Atom feedRe: Docs off on ILIKE indexing?
3+ messages / 3 participants
[nested] [flat]
* Re: Docs off on ILIKE indexing?
@ 2006-01-17 14:44 Magnus Hagander <[email protected]>
2006-01-17 20:26 ` Re: [HACKERS] Docs off on ILIKE indexing? Jim C. Nasby <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Magnus Hagander @ 2006-01-17 14:44 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers <[email protected]>
> > 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [HACKERS] Docs off on ILIKE indexing?
2006-01-17 14:44 Re: Docs off on ILIKE indexing? Magnus Hagander <[email protected]>
@ 2006-01-17 20:26 ` Jim C. Nasby <[email protected]>
2006-01-18 21:28 ` Re: [HACKERS] Docs off on ILIKE indexing? Bruce Momjian <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Jim C. Nasby @ 2006-01-17 20:26 UTC (permalink / raw)
To: Magnus Hagander <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-docs
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 [email protected]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [HACKERS] Docs off on ILIKE indexing?
2006-01-17 14:44 Re: Docs off on ILIKE indexing? Magnus Hagander <[email protected]>
2006-01-17 20:26 ` Re: [HACKERS] Docs off on ILIKE indexing? Jim C. Nasby <[email protected]>
@ 2006-01-18 21:28 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2006-01-18 21:28 UTC (permalink / raw)
To: Jim C. Nasby <[email protected]>; +Cc: Magnus Hagander <[email protected]>; Tom Lane <[email protected]>; pgsql-docs
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 [email protected]
> 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
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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 — 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 — 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>
Attachments:
[text/plain] /bjm/diff (2.1K, 2-%2Fbjm%2Fdiff)
download | inline:
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 — 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 — 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>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-18 21:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-17 14:44 Re: Docs off on ILIKE indexing? Magnus Hagander <[email protected]>
2006-01-17 20:26 ` Jim C. Nasby <[email protected]>
2006-01-18 21:28 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox