Skip site navigation (1) Skip section navigation (2)

Re: LIKE indexing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: LIKE indexing
Date: 2001-08-20 04:33:26
Message-ID: 28370.998282006@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Here's the patch for review.

A few gripes:

+    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
+    anchored to the beginning of the string, e.g., <literal>col LIKE
+    'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
+    <literal>col LIKE 'bar'</literal>.  However, if your server does

The "but not" part is wrong: col LIKE 'bar' works perfectly fine as
an indexable LIKE query.  Perhaps you meant "but not col LIKE '%foo'".

While it's okay to treat text and varchar alike, I object to treating
bpchar as equivalent to the other two.  Shouldn't the bpchar versions of
these functions strip trailing spaces before comparing?

Seems to me you should provide "$<>$" operators for completeness, even
though they're not essential for btree opclasses.  I think that these
operators may be useful for more than just this one purpose, so we
shouldn't set up artificial roadblocks.

I don't like the fact that you added expected-output rows to opr_sanity;
seems like tweaking the queries to allow $<$ etc as expected names would
be more appropriate.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: GrantDate: 2001-08-20 05:07:11
Subject: Suggestion for To Do List - Client timeout please.
Previous:From: Tom LaneDate: 2001-08-20 04:08:46
Subject: Re: LIKE indexing

pgsql-patches by date

Next:From: Marko KreenDate: 2001-08-20 10:21:38
Subject: pgcrypto update
Previous:From: Tom LaneDate: 2001-08-20 04:08:46
Subject: Re: LIKE indexing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group