Re: Hash join on int takes 8..114 seconds

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