Message-ID: <492DA98D.4050704@archonet.com>
Date: Wed, 26 Nov 2008 19:54:53 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081105)
MIME-Version: 1.0
To: Andrus <kobruleht2@hot.ee>
CC: mweilguni@sime.com, pgsql-performance@postgresql.org
Subject: Re: Increasing pattern index query speed
References: <gg9pg5$1edv$1@news.hub.org> <492A7591.3000700@archonet.com>
	<C2F601D897194339A0D2327188C781D6@andrusnotebook>
	<492D27C8.7090206@archonet.com>
	<4936D68EF8C84087BBB9212C4019D5D8@andrusnotebook>
	<492D9E78.9080902@archonet.com>
	<B0DF162C81E84748A71F9FA153ED7BA2@andrusnotebook>
In-Reply-To: <B0DF162C81E84748A71F9FA153ED7BA2@andrusnotebook>
Content-Type: text/plain; charset=windows-1257
Content-Transfer-Encoding: 7bit

Andrus wrote:
> Richard,
> 
>> And the results were?
> 
> Results are provided in bottom of the message to which you replied.

No - the explains there were contrasting a date test BETWEEN versus =.

>> One problem at a time. Let's get the pattern-matching speed problems on
>> your live server sorted, then we can look at different queries.
> 
> First message in this thread described the issue with query having
> additional condition
> 
> AND dok.kuupaev BETWEEN '2008-11-21' AND  '2008-11-21'

Ah, I think I understand. The test case was *missing* this clause.

> It seems that this problem occurs when pattern matching and BETWEEN
> conditions are used in same query.
> 
> According to Scott Garey great recommendation I added
> 
> ALTER TABLE rid ALTER COLUMN toode SET STATISTICS 1000;
> 
> This fixes testcase in live server, see my other message.
> Is it OK to run
> 
> ALTER TABLE rid ALTER COLUMN toode SET STATISTICS 1000
> 
> in prod database or should I try to decrease 1000 to smaller value ?
> rid is big increasing table and is changed frequently, mostly by adding
> rows.

This will try to track the 1000 most-common values of "toode", whereas
the default is to try to track the most common 10 values. Tracking more
values means the planner has more accurate information but makes ANALYSE
take longer to run, and also makes planning each query take slightly longer.

Try 100, 200, 500 and see if they work *for a range of queries* -
there's no point in having it much higher than it needs to be.

-- 
  Richard Huxton
  Archonet Ltd