From: | Victor Yegorov <vyegorov(at)gmail(dot)com> |
---|---|
To: | Tim van der Linden <tim(at)shisaa(dot)jp> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow join over three tables |
Date: | 2016-04-27 06:14:27 |
Message-ID: | CAGnEboiE7WkPisKRJaQ8qKUunA9TWjEXna8pj78dp-VwZWLLJA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2016-04-27 2:27 GMT+03:00 Tim van der Linden <tim(at)shisaa(dot)jp>:
> The plan:
>
> Sort (cost=105773.63..105774.46 rows=333 width=76) (actual
> time=5143.162..5143.185 rows=448 loops=1)
> Sort Key: r.created
> Sort Method: quicksort Memory: 60kB
> -> Nested Loop (cost=1.31..105759.68 rows=333 width=76) (actual
> time=54.784..5142.872 rows=448 loops=1)
> Join Filter: (d.rid = a.rid)
> -> Nested Loop (cost=0.87..94657.59 rows=14005 width=72) (actual
> time=0.822..2038.952 rows=14199 loops=1)
> -> Index Scan using report_drugs_drug_idx on report_drugs d
> (cost=0.44..500.28 rows=14005 width=31) (actual time=0.669..3.900
> rows=14199 loops=1)
> Index Cond: (drug = ANY
> ('{359,360,361,362,363}'::integer[]))
> -> Index Scan using reports_id_key on reports r
> (cost=0.43..6.71 rows=1 width=41) (actual time=0.143..0.143 rows=1
> loops=14199)
> Index Cond: (id = d.rid)
> -> Index Scan using report_adverses_rid_idx on report_adverses a
> (cost=0.44..0.78 rows=1 width=12) (actual time=0.218..0.218 rows=0
> loops=14199)
> Index Cond: (rid = r.id)
> Filter: (adverse = ANY ('{"back pain -
> nonspecific","nonspecific back pain","back pain"}'::text[]))
> Rows Removed by Filter: 5
> Planning time: 13.994 ms
> Execution time: 5143.235 ms
>
Can you post output of `EXPLAIN (analyze, buffers)`, please?
It'd be good to check how many buffers are hit/read during Index Scans.
I have had a somewhat similar case, when due to misconfigured autovacuum
(not aggressive enough for the increased volume)
I got bloat accumulated in the indexes. Perhaps, REINDEX can help here.
--
Victor Y. Yegorov
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2016-04-27 08:29:23 | Re: psql color hostname prompt |
Previous Message | Jeff Janes | 2016-04-27 05:45:14 | Re: Does this perf output seem 'normal'? |