From: Richard Huxton <dev@archonet.com>
To: Andrus <kobruleht2@hot.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Thu, 20 Nov 2008 10:45:53 +0000
Message-ID: <49253FE1.3070706@archonet.com> (raw)
In-Reply-To: <gg1sv3$27fr$1@news.hub.org>
References: <gg1sv3$27fr$1@news.hub.org>
Andrus wrote:
> Query below seems to use indexes everywhere in most optimal way.
> dokumnr column is of type int
>
> Speed of this query varies rapidly:
>
> In live db fastest response I have got is 8 seconds.
> Re-running same query after 10 seconds may take 60 seconds.
> Re-running it again after 10 seconds may take 114 seconds.
>
> Any idea how to speed it up ?
>
> Is it possible to optimize it, will upgrading to 8.3.5 help or should I
> require to add more RAM, disk or CPU speed ?
At a quick glance, the plans look the same to me. The overall costs are
certainly identical. That means whatever is affecting the query times it
isn't the query plan.
> "Aggregate (cost=234278.53..234278.54 rows=1 width=0) (actual
> time=62164.496..62164.500 rows=1 loops=1)"
> "Total runtime: 62164.789 ms"
> "Aggregate (cost=234278.53..234278.54 rows=1 width=0) (actual
> time=40185.499..40185.503 rows=1 loops=1)"
> "Total runtime: 40186.102 ms"
> "Aggregate (cost=234278.53..234278.54 rows=1 width=0) (actual
> time=29650.398..29650.402 rows=1 loops=1)"
> "Total runtime: 29650.696 ms"
> "Aggregate (cost=234278.53..234278.54 rows=1 width=0) (actual
> time=11131.392..11131.396 rows=1 loops=1)"
> "Total runtime: 11131.694 ms"
So - what other activity is happening on this machine? Either other
queries are taking up noticeable resources, or some other process is (it
might be disk activity from checkpointing, logging some other application).
--
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: Hash join on int takes 8..114 seconds
In-Reply-To: <49253FE1.3070706@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