Re: Increasing pattern index query speed

Richard,

> These are the same but the times are different. I'd be very surprised if
> you can reproduce these times reliably.

I re-tried today again and got same results: in production database pattern
query is many times slower that equality query.
toode and rid base contain only single product starting with 99000010
So both queries should scan exactly same numbers of rows.

> Can I give you some wider-ranging suggestions Andrus?
> 1. Fix the vacuuming issue in your hash-join question.

I have ran VACUUM FULL VERBOSE ANALYSE and set max_fsm_pages=150000
So issue is fixed before those tests.

> 2. Monitor the system to make sure you know if/when disk activity is high.

I optimized this system. Now there are short (some seconds) sales queries
about after every 5 - 300 seconds which cause few disk activity and add few
new rows to some tables.
I havent seen that this activity affects to this test result.

> 3. *Then* start to profile individual queries and look into their plans.
> Change the queries one at a time and monitor again.

How to change pattern matching query to faster ?

Andrus.

Btw.

I tried to reproduce this big difference in test server in 8.3 using sample
data script below and got big difference but in opposite direction.

explain analyze SELECT sum(1)
FROM orders
JOIN orders_products USING (order_id)
JOIN products USING (product_id)
WHERE orders.order_date>'2006-01-01' and ...

different where clauses produce different results:

AND orders_products.product_id = '3370000000000000' -- 880 .. 926 ms
AND orders_products.product_id like '3370000000000000%' -- 41 ..98 ms

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

Test data creation script:

begin;
CREATE OR REPLACE FUNCTION Counter() RETURNS int IMMUTABLE AS
$_$
SELECT 3500000;
$_$ LANGUAGE SQL;

CREATE TEMP TABLE orders (order_id INTEGER NOT NULL, order_date DATE NOT
NULL);
CREATE TEMP TABLE products (product_id CHAR(20) NOT NULL, product_name
char(70) NOT NULL, quantity numeric(12,2) default 1);
CREATE TEMP TABLE orders_products (order_id INTEGER NOT NULL, product_id
CHAR(20),
id serial, price numeric(12,2) default 1 );

INSERT INTO products SELECT (n*power( 10,13))::INT8::CHAR(20),
'product number ' || n::TEXT FROM generate_series(0,13410) AS n;

INSERT INTO orders
SELECT n,'2005-01-01'::date + (4000.0 * n/Counter() * '1 DAY'::interval)
FROM generate_series(0, Counter()/3 ) AS n;

SET work_mem TO 2097151;

INSERT INTO orders_products SELECT
generate_series/3 as order_id,
( (1+ (generate_series % 13410))*power( 10,13))::INT8::CHAR(20) AS
product_id
FROM generate_series(1, Counter());

ALTER TABLE orders ADD PRIMARY KEY (order_id);
ALTER TABLE products ADD PRIMARY KEY (product_id);
ALTER TABLE orders_products ADD PRIMARY KEY (id);

ALTER TABLE orders_products ADD FOREIGN KEY (product_id) REFERENCES
products(product_id);
ALTER TABLE orders_products ADD FOREIGN KEY (order_id) REFERENCES
orders(order_id) ON DELETE CASCADE;

CREATE INDEX orders_date ON orders( order_date );
CREATE INDEX order_product_pattern_idx ON orders_products( product_id
bpchar_pattern_ops );

COMMIT;
SET work_mem TO DEFAULT;
ANALYZE;