Re: Increasing pattern index query speed - Mailing list pgsql-performance
From | Andrus |
---|---|
Subject | Re: Increasing pattern index query speed |
Date | |
Msg-id | C2F601D897194339A0D2327188C781D6@andrusnotebook Whole thread Raw |
In response to | Re: Increasing pattern index query speed (Richard Huxton <dev@archonet.com>) |
Responses |
Re: Increasing pattern index query speed
Re: Increasing pattern index query speed Re: Increasing pattern index query speed |
List | pgsql-performance |
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;
pgsql-performance by date: