Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0370A65021B for ; Wed, 26 Nov 2008 16:21:18 -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 48691-03 for ; Wed, 26 Nov 2008 16:21:15 -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 A9EA66501DC for ; Wed, 26 Nov 2008 16:21:15 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by MXR-13.estpak.ee (Postfix) with ESMTP id F0E27307A6; Wed, 26 Nov 2008 22:21:11 +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 meTuHrszH-7W; Wed, 26 Nov 2008 22:21:11 +0200 (EET) Received: from Relayhost2.neti.ee (Relayhost2 [88.196.174.142]) by MXR-13.estpak.ee (Postfix) with ESMTP id 9BF2D307A1; Wed, 26 Nov 2008 22:21:11 +0200 (EET) X-SMTP-Auth-NETI-Businesmail: no Received: from andrusnotebook (unknown [195.50.198.194]) by Relayhost2.neti.ee (Postfix) with SMTP id 614721FD5DC; Wed, 26 Nov 2008 22:21:12 +0200 (EET) Message-ID: <534B7B28155C4275BD2B9AA8F3B656DD@andrusnotebook> From: "Andrus" To: "Richard Huxton" Cc: , References: <492A7591.3000700@archonet.com> <492D27C8.7090206@archonet.com> <4936D68EF8C84087BBB9212C4019D5D8@andrusnotebook> <492D9E78.9080902@archonet.com> In-Reply-To: <492D9E78.9080902@archonet.com> Subject: Re: Increasing pattern index query speed Date: Wed, 26 Nov 2008 22:20:52 +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/269 X-Sequence-Number: 32000 Richard, >> Results are provided in bottom of the message to which you replied. > > No - the explains there were contrasting a date test BETWEEN versus =. I changed rid.toode statitics target to 100: ALTER TABLE firma2.rid ALTER COLUMN toode SET STATISTICS 100; analyze firma2.rid; Analyze takes 3 seconds and testcase rans fast. I'm planning to monitor results by looking log file for queries which take longer than 10 seconds. Do you still need results ? If yes, which query and how many times should I run? > Ah, I think I understand. The test case was *missing* this clause. I added this clause to testcase. Also added char(70) colums containing padding characters to all three tables. Cannot still reproduce this issue in testcase in fast devel 8.3 notebook. In testcase order_products contains product_id values in a very regular order, maybe this affects the results. No idea how to use random() to generate random products for every order. Andrus.