Message-ID: <492A7591.3000700@archonet.com>
Date: Mon, 24 Nov 2008 09:36:17 +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: pgsql-performance@postgresql.org
Subject: Re: Increasing pattern index query speed
References: <gg9pg5$1edv$1@news.hub.org>
In-Reply-To: <gg9pg5$1edv$1@news.hub.org>
Content-Type: text/plain; charset=windows-1257
Content-Transfer-Encoding: 7bit

Andrus wrote:
> Both queries return same result (19) and return same data.
> Pattern query is a much slower (93 sec) than  equality check (13 sec).
> How to fix this ?
> Using 8.1.4, utf-8 encoding, et-EE locale

They're different queries. The fact that they return the same results is
a coincidence.

This

>   WHERE rid.toode = '99000010' 

Is a different condition to this

>   WHERE rid.toode like '99000010%'

You aren't going to get the same plans.

Anyway, I think the problem is in the dok JOIN rid bit look:

> "Aggregate  (cost=43.09..43.10 rows=1 width=0) (actual
> time=12674.675..12674.679 rows=1 loops=1)"
> "  ->  Nested Loop  (cost=29.57..43.08 rows=1 width=0) (actual
> time=2002.045..12673.645 rows=19 loops=1)"
> "        ->  Nested Loop  (cost=29.57..37.06 rows=1 width=24) (actual
> time=2001.922..12672.344 rows=19 loops=1)"

> "Aggregate  (cost=15.52..15.53 rows=1 width=0) (actual
> time=92966.501..92966.505 rows=1 loops=1)"
> "  ->  Nested Loop  (cost=0.00..15.52 rows=1 width=0) (actual
> time=24082.032..92966.366 rows=19 loops=1)"
> "        ->  Nested Loop  (cost=0.00..9.50 rows=1 width=24) (actual
> time=24081.919..92965.116 rows=19 loops=1)"

These are the same but the times are different. I'd be very surprised if
you can reproduce these times reliably.


Can I give you some wider-ranging suggestions Andrus?
1. Fix the vacuuming issue in your hash-join question.
2. Monitor the system to make sure you know if/when disk activity is high.
3. *Then* start to profile individual queries and look into their plans.
Change the queries one at a time and monitor again.

Otherwise, it's very difficult to figure out whether changes you make
are effective.

HTH
-- 
  Richard Huxton
  Archonet Ltd