Message-ID: <492D27C8.7090206@archonet.com>
Date: Wed, 26 Nov 2008 10:41:12 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081105)
MIME-Version: 1.0
To: Andrus <kobruleht2@hot.ee>
CC: pgsql-performance@postgresql.org
Subject: Re: Increasing pattern index query speed
References: <gg9pg5$1edv$1@news.hub.org> <492A7591.3000700@archonet.com>
	<C2F601D897194339A0D2327188C781D6@andrusnotebook>
In-Reply-To: <C2F601D897194339A0D2327188C781D6@andrusnotebook>
Content-Type: text/plain; charset=windows-1257
Content-Transfer-Encoding: 7bit

Andrus wrote:
> 
> So patter index is 10 .. 20 times (!) faster always.
> No idea why.

Because you don't have a normal index on the product_id column? You
can't use xxx_pattern_ops indexes for non-pattern tests.

> Test data creation script:

The only change to the script was the obvious char(nn) => varchar(nn)
and I didn't use TEMP tables (so I could see what I was doing). Then, I
created the "standard" index on order_products.product_id.

EXPLAIN ANALYSE from my slow dev box are listed below. Database is in
LATIN9 encoding with locale=C.


 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2993.69..2993.70 rows=1 width=0) (actual
time=2.960..2.960 rows=1 loops=1)
   ->  Nested Loop  (cost=10.81..2993.23 rows=182 width=0) (actual
time=0.972..2.901 rows=189 loops=1)
         ->  Index Scan using products_pkey on products
(cost=0.00..8.27 rows=1 width=18) (actual time=0.017..0.019 rows=1 loops=1)
               Index Cond: ((product_id)::text = '3370000000000000'::text)
         ->  Nested Loop  (cost=10.81..2983.14 rows=182 width=18)
(actual time=0.951..2.785 rows=189 loops=1)
               ->  Bitmap Heap Scan on orders_products
(cost=10.81..942.50 rows=251 width=22) (actual time=0.296..0.771
rows=261 loops=1)
                     Recheck Cond: ((product_id)::text =
'3370000000000000'::text)
                     ->  Bitmap Index Scan on
order_product_pattern_eq_idx  (cost=0.00..10.75 rows=251 width=0)
(actual time=0.230..0.230 rows=261 loops=1)
                           Index Cond: ((product_id)::text =
'3370000000000000'::text)
               ->  Index Scan using orders_pkey on orders
(cost=0.00..8.12 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=261)
                     Index Cond: (orders.order_id =
orders_products.order_id)
                     Filter: (orders.order_date > '2006-01-01'::date)
 Total runtime: 3.051 ms
(13 rows)


    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.56..25.57 rows=1 width=0) (actual time=8.244..8.245
rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..25.55 rows=1 width=0) (actual
time=1.170..8.119 rows=378 loops=1)
         ->  Nested Loop  (cost=0.00..17.17 rows=1 width=4) (actual
time=0.043..4.167 rows=522 loops=1)
               ->  Index Scan using order_product_pattern_eq_idx on
orders_products  (cost=0.00..8.88 rows=1 width=22) (actual
time=0.029..1.247 rows=522 loops=1)
                     Index Cond: (((product_id)::text >=
'3370000000000000'::text) AND ((product_id)::text <
'3370000000000001'::text))
                     Filter: ((product_id)::text ~~
'3370000000000000%'::text)
               ->  Index Scan using products_pkey on products
(cost=0.00..8.27 rows=1 width=18) (actual time=0.004..0.004 rows=1
loops=522)
                     Index Cond: ((products.product_id)::text =
(orders_products.product_id)::text)
         ->  Index Scan using orders_pkey on orders  (cost=0.00..8.37
rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=522)
               Index Cond: (orders.order_id = orders_products.order_id)
               Filter: (orders.order_date > '2006-01-01'::date)
 Total runtime: 8.335 ms
(12 rows)


-- 
  Richard Huxton
  Archonet Ltd