From: Richard Huxton <dev@archonet.com>
To: Andrus <kobruleht2@hot.ee>
Cc: mweilguni@sime.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Increasing pattern index query speed
Date: Wed, 26 Nov 2008 20:33:13 +0000
Message-ID: <492DB289.8020703@archonet.com> (raw)
In-Reply-To: <534B7B28155C4275BD2B9AA8F3B656DD@andrusnotebook>
References: <gg9pg5$1edv$1@news.hub.org>
<492A7591.3000700@archonet.com>
<C2F601D897194339A0D2327188C781D6@andrusnotebook>
<492D27C8.7090206@archonet.com>
<4936D68EF8C84087BBB9212C4019D5D8@andrusnotebook>
<492D9E78.9080902@archonet.com>
<534B7B28155C4275BD2B9AA8F3B656DD@andrusnotebook>
Andrus wrote:
> 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.
Sensible. I don't know if 10 seconds is the right value for your
database, but there will be a point that filters out most of your
traffic but still gives enough to find problems.
> Do you still need results ?
> If yes, which query and how many times should I run?
If changing the statistics seems to help, you're not going to want to go
back just to repeat tests.
>> 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.
Ideally you don't even want random products. You want a distribution of
products that matches the same "shape" as you have in your production
database.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, kobruleht2@hot.ee, mweilguni@sime.com
Subject: Re: Increasing pattern index query speed
In-Reply-To: <492DB289.8020703@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox