Re: Slow join over three tables

On 27 April 2016 at 22:29, Tim van der Linden <tim@shisaa.jp> wrote:
>  Sort  (cost=372968.28..372969.07 rows=317 width=41) (actual time=9308.174..9308.187 rows=448 loops=1)
>    Sort Key: r.created
>  Sort  (cost=66065.73..66066.59 rows=344 width=41) (actual time=4313.679..4313.708 rows=448 loops=1)
>    Sort Key: r.created
>    Sort Method: quicksort  Memory: 60kB
>    ->  Nested Loop  (cost=1.44..66051.23 rows=344 width=41) (actual time=43.987..4313.435 rows=448 loops=1)
>          Join Filter: (d.rid = a.rid)
>          ->  Nested Loop  (cost=1.00..54700.19 rows=14319 width=28) (actual time=0.772..1158.338 rows=14200 loops=1)
>                ->  Index Scan using report_drugs_drug_idx on report_drugs d  (cost=0.44..507.78 rows=14319 width=8)
(actualtime=0.579..4.327 rows=14200 loops=1) 
>                      Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
>                ->  Index Only Scan using reports_id_age_gender_created_idx on reports r  (cost=0.56..3.77 rows=1
width=20)(actual time=0.081..0.081 rows=1 loops=14200) 
>                      Index Cond: (id = d.rid)
>                      Heap Fetches: 0
>          ->  Index Scan using report_adverses_rid_idx on report_adverses a  (cost=0.44..0.78 rows=1 width=21) (actual
time=0.222..0.222rows=0 loops=14200) 
>                Index Cond: (rid = r.id)
>                Filter: (adverse = ANY ('{"back pain - nonspecific","nonspecific back pain","back pain"}'::text[]))
>                Rows Removed by Filter: 5
>  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.

> Yet ... this is "only" 1 second faster, still 4 seconds to complete this query (the join(s)). Is this truly the most
thatI 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) ... ?

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

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