Skip site navigation (1) Skip section navigation (2)

Re: Increasing pattern index query speed

From: Richard Huxton <dev(at)archonet(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: mweilguni(at)sime(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: Increasing pattern index query speed
Date: 2008-11-26 20:33:13
Message-ID: 492DB289.8020703@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Ryan HansenDate: 2008-11-26 22:09:55
Subject: Memory Allocation
Previous:From: AndrusDate: 2008-11-26 20:20:52
Subject: Re: Increasing pattern index query speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group