From: Sam Wong <sam@hellosam.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE op with B-Tree Index?
Date: Wed, 17 Oct 2012 09:01:09 +0800
Message-ID: <002401cdac02$e5399280$afacb780$@hellosam.net> (raw)
In-Reply-To: <CAHyXU0xa2uodxLDqqc8KnA=vW_G8P9wDpOHeeMp55psYr_J7PA@mail.gmail.com>
References: <002501cdab76$62e38160$28aa8420$@hellosam.net>
<CAHyXU0xa2uodxLDqqc8KnA=vW_G8P9wDpOHeeMp55psYr_J7PA@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam@hellosam.net
Subject: Re: LIKE op with B-Tree Index?
In-Reply-To: <002401cdac02$e5399280$afacb780$@hellosam.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox