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

Re: LIKE indexing

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-patches(at)postgresql(dot)org>
Subject: Re: LIKE indexing
Date: 2001-08-20 15:45:02
Message-ID: Pine.LNX.4.30.0108201732120.822-100000@peter.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-patches
Tom Lane writes:

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

Thanks.  That was a mixup with the POSIX regexp style.

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

I had thought a long time about this and I couldn't see a reason why.
The reason is that the LIKE operator for bpchar does take the blanks into
account, so it effectively doesn't care whether the blanks are the result
of padding or explicit input.  E.g.,

peter=# set enable_indexscan to off;
peter=# create table test1 (a char(5));
peter=# insert into test1 values ('four');
INSERT 16560 1
peter=# select * from test1 where a like 'four'::bpchar;
(0 rows)

 * If we had stripped spaces here we would have gotten a false positive.

peter=# select * from test1 where a like 'fou_'::bpchar;
(0 rows)

 * Since the padding here is after the wildcard character and is thus
 * stripped in the analysis, the augmented expression still holds.

peter=# select * from test1 where a like 'fou%'::bpchar;
(1 row)

/* same here */

I would also argue that the notion of a direct binary comparision would
not benefit from space stripping.

> Seems to me you should provide "$<>$" operators for completeness, even
> though they're not essential for btree opclasses.

Will do.

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


Peter Eisentraut   peter_e(at)gmx(dot)net

In response to


pgsql-hackers by date

Next:From: Lincoln YeohDate: 2001-08-20 15:52:03
Subject: RE: User locks code
Previous:From: Bruce MomjianDate: 2001-08-20 15:36:45
Subject: Re: A fixed user id for the postgres user?

pgsql-patches by date

Next:From: Tom LaneDate: 2001-08-20 15:58:05
Subject: Re: LIKE indexing
Previous:From: Bruce MomjianDate: 2001-08-20 14:52:30
Subject: Re: DatabaseMetaData patch

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