Re: Increasing pattern index query speed

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