From: | Tim van der Linden <tim(at)shisaa(dot)jp> |
---|---|
To: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow join over three tables |
Date: | 2016-04-27 10:32:35 |
Message-ID: | 20160427193235.6ddcbbe792bdd532a83224fc@shisaa.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 27 Apr 2016 09:14:27 +0300
Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:
Hi Victor
> > ...
>
> Can you post output of `EXPLAIN (analyze, buffers)`, please?
> It'd be good to check how many buffers are hit/read during Index Scans.
Happy to, here it is:
Sort (cost=107727.85..107728.71 rows=344 width=41) (actual time=5357.453..5357.482 rows=448 loops=1)
Sort Key: r.created
Sort Method: quicksort Memory: 60kB
Buffers: shared hit=75462 read=37183
-> Nested Loop (cost=1.31..107713.36 rows=344 width=41) (actual time=53.866..5357.211 rows=448 loops=1)
Join Filter: (d.rid = a.rid)
Buffers: shared hit=75459 read=37183
-> Nested Loop (cost=0.87..96362.32 rows=14319 width=28) (actual time=0.569..2214.900 rows=14200 loops=1)
Buffers: shared hit=39949 read=17072
-> Index Scan using report_drugs_drug_idx on report_drugs d (cost=0.44..507.78 rows=14319 width=8) (actual time=0.567..4.406 rows=14200 loops=1)
Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
Buffers: shared hit=14 read=131
-> Index Scan using reports_id_key on reports r (cost=0.43..6.68 rows=1 width=20) (actual time=0.155..0.155 rows=1 loops=14200)
Index Cond: (id = d.rid)
Buffers: shared hit=39935 read=16941
-> Index Scan using report_adverses_rid_idx on report_adverses a (cost=0.44..0.78 rows=1 width=21) (actual time=0.221..0.221 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
Buffers: shared hit=35510 read=20111
Planning time: 13.962 ms
Execution time: 5357.520 ms
> --
> Victor Y. Yegorov
Cheers,
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2016-04-27 10:40:43 | Re: Slow join over three tables |
Previous Message | Tim van der Linden | 2016-04-27 10:29:18 | Re: Slow join over three tables |