Re: Increasing pattern index query speed

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