Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TO2nm-0004A9-R0 for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:47:02 +0000 Received: from [202.181.174.115] (helo=doravmmail.chowchi.com) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TO2nf-0003An-Hb for pgsql-performance@postgresql.org; Tue, 16 Oct 2012 08:47:02 +0000 Received: (qmail 92008 invoked from network); 16 Oct 2012 16:46:51 +0800 Received: from unknown (HELO doravmmail.chowchi.com) (202.181.174.115) by doravmmail.chowchi.com with SMTP; 16 Oct 2012 16:46:51 +0800 X-Virus-Scanned: amavisd-new at chowchi.com Received: from doravmmail.chowchi.com ([202.181.174.115]) by doravmmail.chowchi.com (doravmmail.chowchi.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id cqlrnXDvlzAT for ; Tue, 16 Oct 2012 16:46:46 +0800 (HKT) Received: from longwt3 (123202019132.ctinets.com [123.202.19.132]) by doravmmail.chowchi.com (Postfix) with ESMTPA id 576F93488D2 for ; Tue, 16 Oct 2012 16:46:46 +0800 (HKT) From: "Sam Wong" To: Subject: LIKE op with B-Tree Index? Date: Tue, 16 Oct 2012 16:46:42 +0800 Message-ID: <002d01cdab7a$c46add30$4d409790$@hellosam.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01CDABBD.D28F2EA0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac2reqodjctguKMHSdS8ehL/lFjCKQ== Content-Language: en-us X-Host-Lookup-Failed: Reverse DNS lookup failed for 202.181.174.115 (failed) X-Pg-Spam-Score: -1.1 (-) X-Archive-Number: 201210/207 X-Sequence-Number: 48166 This is a multipart message in MIME format. ------=_NextPart_000_002E_01CDABBD.D28F2EA0 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable Hi communities, =20 I am investigating a performance issue involved with LIKE 'xxxx%' on an index in a complex query with joins.=20 =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. =20 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' =20 shipments is a table with million rows and 20 columns. Shipment_id is = the primary key with text and non-null field. =20 CREATE TABLE cod.shipments ( shipment_id text NOT NULL, -- other columns omitted CONSTRAINT shipments_pkey PRIMARY KEY (shipment_id) ) =20 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 =20 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 =20 =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. =20 =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? =20 Many Thanks, Sam =20 (The email didn=A1=AFt seems to go through without subscription. = Resending) ------=_NextPart_000_002E_01CDABBD.D28F2EA0 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable

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

=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

 

(The email didn=A1=AFt seems to go = through without subscription. = Resending)

------=_NextPart_000_002E_01CDABBD.D28F2EA0--