From: Richard Huxton <dev@archonet.com>
To: Andrus <kobruleht2@hot.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Increasing pattern index query speed
Date: Mon, 24 Nov 2008 09:36:17 +0000
Message-ID: <492A7591.3000700@archonet.com> (raw)
In-Reply-To: <gg9pg5$1edv$1@news.hub.org>
References: <gg9pg5$1edv$1@news.hub.org>
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
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
Subject: Re: Increasing pattern index query speed
In-Reply-To: <492A7591.3000700@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