Reply-To: "Sam Wong" <sam@hellosam.net>
From: "Sam Wong" <sam@hellosam.net>
To: <pgsql-performance@postgresql.org>
Subject: LIKE op with B-Tree Index?
Date: Tue, 16 Oct 2012 16:15:21 +0800
Message-ID: <002501cdab76$62e38160$28aa8420$@hellosam.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Ac2rdAdMy2+FjyNoQmObarL4cPPACQ==
Content-Language: en-us

Hi communities,

I am investigating a performance issue involved with LIKE 'xxxx%' on an
index in a complex query with joins.=20

The problem boils down into this simple scenario---:
=3D=3D=3D=3DScenario=3D=3D=3D=3D
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%'
=A1=A1=A1=A1
Q2.
SELECT * FROM shipments WHERE shipment_id >=3D '12345678' AND =
shipment_id <
'12345679'

shipments is a table with million rows and 20 columns. Shipment_id is =
the
primary key with text and non-null field.

CREATE TABLE cod.shipments
(
  shipment_id text NOT NULL,
  -- other columns omitted
  CONSTRAINT shipments_pkey PRIMARY KEY (shipment_id)
)

Analyze Q1 gives this:
Index Scan using shipments_pkey on shipments  (cost=3D0.00..39.84 =
rows=3D1450
width=3D294) (actual time=3D0.018..0.018 rows=3D1 loops=3D1)
  Index Cond: ((shipment_id >=3D '12345678'::text) AND (shipment_id <
'12345679'::text))
  Filter: (shipment_id ~~ '12345678%'::text)
  Buffers: shared hit=3D4

Analyze Q2 gives this:
Index Scan using shipments_pkey on shipments  (cost=3D0.00..39.83 =
rows=3D1
width=3D294) (actual time=3D0.027..0.027 rows=3D1 loops=3D1)
  Index Cond: ((shipment_id >=3D '12345678'::text) AND (shipment_id <
'12345679'::text))
  Buffers: shared hit=3D4

=3D=3D=3D=3DProblem Description=3D=3D=3D=3D
In Q1, the planner thought there will be 1450 rows, and Q2 gave a much
better estimate of 1.
The problem is when I combine such condition with a join to other table,
postgres will prefer a merge join (or hash) rather than a nested loop.

=3D=3D=3D=3DQuestion=3D=3D=3D=3D
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?

Many Thanks,
Sam