Re: LIKE op with B-Tree Index?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Oct 16, 2012 at 3:15 AM, Sam Wong <sam@xxxxxxxxxxxx> 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'
>
> 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=0.00..39.84 rows=1450
> width=294) (actual time=0.018..0.018 rows=1 loops=1)
>   Index Cond: ((shipment_id >= '12345678'::text) AND (shipment_id <
> '12345679'::text))
>   Filter: (shipment_id ~~ '12345678%'::text)
>   Buffers: shared hit=4
>
> Analyze Q2 gives this:
> Index Scan using shipments_pkey on shipments  (cost=0.00..39.83 rows=1
> width=294) (actual time=0.027..0.027 rows=1 loops=1)
>   Index Cond: ((shipment_id >= '12345678'::text) AND (shipment_id <
> '12345679'::text))
>   Buffers: shared hit=4
>
> ====Problem Description====
> 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.
>
> ====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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux