Re: Slow join over three tables

On Wed, 27 Apr 2016 22:40:43 +1200
David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:

Hi David

> > ...
> > Planning time: 15.968 ms
> > Execution time: 4313.755 ms
> >
> > Both the (rid, adverse) and the (id, age, gender, created) indexes are now used.
> >
>
> Seems the (rid, adverse) is not being used. report_adverses_rid_idx
> is your (rid) index.

Ah ... good catch, I blame it on the late evening over here ;)

> > Yet ... this is "only" 1 second faster, still 4 seconds to complete this query (the join(s)). Is this truly the most that I could get out of it ... or could further tweaking to PostgreSQL's configuration be of any help here?
>
> EXPLAIN ANALYZE also has quite a bit of timing overhead, so it might
> not be taking quite as long as you think.
>
> How long does it take with EXPLAIN (ANALYZE, TIMING OFF) ... ?

Hmmm, this takes roughly the same amount of time:

Planning time: 39.889 ms
Execution time: 4532.642 ms

> Or perhaps just run the query, as there's only 448 rows anyway.

Also, did not speed up much ... query time pulled from the log statement: 4305.686 ms.

> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

Cheers,
Tim