Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2E62D64FE45 for ; Mon, 24 Nov 2008 16:33:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 21706-09 for ; Mon, 24 Nov 2008 16:33:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp-out.neti.ee (smtp-out.neti.ee [194.126.126.44]) by mail.postgresql.org (Postfix) with ESMTP id EF99664FF27 for ; Mon, 24 Nov 2008 16:33:53 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by MXR-13.estpak.ee (Postfix) with ESMTP id 6B73F30638; Mon, 24 Nov 2008 22:33:50 +0200 (EET) X-Virus-Scanned: amavisd-new at !change-mydomain-variable!.example.com Received: from smtp-out.neti.ee ([127.0.0.1]) by localhost (MXR-1.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 2wMH6fEDXT-f; Mon, 24 Nov 2008 22:33:50 +0200 (EET) Received: from Relayhost2.neti.ee (Relayhost2 [88.196.174.142]) by MXR-13.estpak.ee (Postfix) with ESMTP id F33143059C; Mon, 24 Nov 2008 22:33:49 +0200 (EET) X-SMTP-Auth-NETI-Businesmail: no Received: from andrusnotebook (unknown [195.50.198.194]) by Relayhost2.neti.ee (Postfix) with SMTP id 00B391FBF78; Mon, 24 Nov 2008 22:33:50 +0200 (EET) Message-ID: From: "Andrus" To: "Richard Huxton" Cc: References: <492A7591.3000700@archonet.com> In-Reply-To: <492A7591.3000700@archonet.com> Subject: Re: Increasing pattern index query speed Date: Mon, 24 Nov 2008 22:33:37 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="windows-1257"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Windows Mail 6.0.6001.18000 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 200811/243 X-Sequence-Number: 31974 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;