From: "Sam Wong" <sam@hellosam.net>
Cc: <pgsql-performance@postgresql.org>
References: <002501cdab76$62e38160$28aa8420$@hellosam.net>
	<CAHyXU0xa2uodxLDqqc8KnA=vW_G8P9wDpOHeeMp55psYr_J7PA@mail.gmail.com>
In-Reply-To: 
 <CAHyXU0xa2uodxLDqqc8KnA=vW_G8P9wDpOHeeMp55psYr_J7PA@mail.gmail.com>
Subject: Re: LIKE op with B-Tree Index?
Date: Wed, 17 Oct 2012 09:01:09 +0800
Message-ID: <002401cdac02$e5399280$afacb780$@hellosam.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQGusd8cGOrUTWmqx97JblJZXDKWdAK6kM8Wl+TLtoA=
Content-Language: en-us

> On Wednesday, October 17, 2012 4:30, Merlin Moncure wrote,
> 
> On Tue, Oct 16, 2012 at 3:15 AM, Sam Wong <sam@hellosam.net> wrote:
> > Hi communities,
> >
> > I am investigating a performance issue involved with LIKE 'xxxx%' on
> > an index in a complex query with joins.
> >
> > The problem boils down into this simple scenario---:
> > ====Scenario====
> > My database locale is C, using UTF-8 encoding. I tested this on 9.1.6
and 9.
> > 2.1.
> >
> > Q1.
> > SELECT * FROM shipments WHERE shipment_id LIKE '12345678%'
> >
> > Q2.
> > SELECT * FROM shipments WHERE shipment_id >= '12345678' AND
> > shipment_id < '12345679'
> >
> > ...snip...
> >
> > ====Question====
> > Is Q1 and Q2 equivalent? From what I see and the result they seems to
> > be the same, or did I miss something? (Charset: C, Encoding: UTF-8) If
> > they are equivalent, is that a bug of the planner?
> 
> They are most certainly not equivalent.  What if the shipping_id is
> 12345678Z?
> 
> merlin
>
But '12345678Z' is indeed >= '12345678' AND < '12345679'. Just like 'apple'
< 'apples' < 'apply' in a dictionary.

A quick test:
vitalink=# select * from ss;
    id
-----------
 12345678
 12345678Z
 12345679
(3 rows)

vitalink=# select * from ss  WHERE id >= '12345678' AND id < '12345679';
    id
-----------
 12345678
 12345678Z
(2 rows)

Sam