From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Tim van der Linden <tim(at)shisaa(dot)jp> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow join over three tables |
Date: | 2016-04-27 10:40:43 |
Message-ID: | CAKJS1f8S81ihUFoY_2HO3ztTSXb_Rqf+nUWm4HqLX1g51qtSpA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 27 April 2016 at 22:29, Tim van der Linden <tim(at)shisaa(dot)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) (actual time=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.222 rows=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 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) ... ?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim van der Linden | 2016-04-27 10:49:26 | Re: Slow join over three tables |
Previous Message | Tim van der Linden | 2016-04-27 10:32:35 | Re: Slow join over three tables |