explain.depesz.com

A tool for finding a real cause for slow queries.

Result: qRC

options
# exclusive inclusive rows x rows loops node
1. 0.181 343.804 ↓ 22.0 22 1
→

Sort (cost=2272.73..2272.74 rows=1 width=502) (actual time=343.754..343.804 rows=22 loops=1)

  • Sort Key: t.pnum
  • Sort Method: quicksort Memory: 19kB
2. 0.128 343.623 ↓ 22.0 22 1
→

Subquery Scan t (cost=2272.60..2272.72 rows=1 width=502) (actual time=343.267..343.623 rows=22 loops=1)

  • Filter: ((t.pnum > 0) AND (t.pnum <= 3))
3. 0.329 343.495 ↓ 22.0 22 1
→

WindowAgg (cost=2272.60..2272.71 rows=1 width=133) (actual time=343.249..343.495 rows=22 loops=1)

4. 0.195 343.166 ↓ 22.0 22 1
→

Sort (cost=2272.60..2272.61 rows=1 width=133) (actual time=343.116..343.166 rows=22 loops=1)

  • Sort Key: ((((date_part('epoch'::text, (min(x1.start) - now())) / 3600::double precision)) + (acos(((0.782696471294001::double precision * sin(radians((COALESCE(x1.latitude, 51.5001::real))::double precision))) + ((0.622403593999841::double precision * cos(radians((COALESCE(x1.latitude, 51.5001::real))::double precision))) * cos(((-0.00223410867356201)::double precision - radians((COALESCE(x1.longitude, (-0.1262)::real))::double precision)))))) * 3960::double precision))), sp.soe_id
  • Sort Method: quicksort Memory: 19kB
5. 0.431 342.971 ↓ 22.0 22 1
→

Nested Loop (cost=2135.79..2272.59 rows=1 width=133) (actual time=341.605..342.971 rows=22 loops=1)

  • Join Filter: (((dp.nhs_duration * 60))::double precision <= date_part('epoch'::text, s.duration))
6. 0.533 342.232 ↓ 11.0 22 1
→

Nested Loop (cost=2135.79..2270.59 rows=2 width=143) (actual time=341.406..342.232 rows=22 loops=1)

7. 3.415 336.659 ↓ 5.5 60 1
→

Hash Join (cost=2135.79..2162.90 rows=11 width=93) (actual time=330.824..336.659 rows=60 loops=1)

  • Hash Cond: (((spp.practices_id)::text = (sp.id)::text) AND ((spp.soe_id)::text = (soepp.soe_id)::text))
  • Join Filter: ((((sp.language)::text = 'en'::text) AND (spp.gdc IS NOT NULL)) OR (((sp.language)::text <> 'en'::text) AND ((spp.gdc IS NULL) OR (spp.gdc IS NOT NULL))))
8. 2.487 2.487 ↑ 1.0 911 1
→

Seq Scan on site_practicioners spp (cost=0.00..20.11 rows=911 width=30) (actual time=0.017..2.487 rows=911 loops=1)

9. 2.448 330.757 ↓ 2.9 663 1
→

Hash (cost=2132.31..2132.31 rows=232 width=119) (actual time=330.757..330.757 rows=663 loops=1)

10. 5.197 328.309 ↓ 2.9 663 1
→

Hash Join (cost=2106.02..2132.31 rows=232 width=119) (actual time=320.397..328.309 rows=663 loops=1)

  • Hash Cond: ((soepp.soe_practice_id)::text = (sp.soe_id)::text)
11. 2.895 2.895 ↑ 1.0 1089 1
→

Seq Scan on soe_providers soepp (cost=0.00..19.89 rows=1089 width=20) (actual time=0.031..2.895 rows=1089 loops=1)

12. 0.288 320.217 ↓ 1.7 57 1
→

Hash (cost=2105.60..2105.60 rows=33 width=259) (actual time=320.217..320.217 rows=57 loops=1)

13. 1.297 319.929 ↓ 1.7 57 1
→

Merge Join (cost=2098.86..2105.60 rows=33 width=259) (actual time=318.349..319.929 rows=57 loops=1)

  • Merge Cond: ((sp.soe_id)::text = (x2.soe_practice_id)::text)
  • Join Filter: ((((date_part('epoch'::text, (min(x1.start) - now())) / 3600::double precision)) + (acos(((0.782696471294001::double precision * sin(radians((COALESCE(x1.latitude, 51.5001::real))::double precision))) + ((0.622403593999841::double precision * cos(radians((COALESCE(x1.latitude, 51.5001::real))::double precision))) * cos(((-0.00223410867356201)::double precision - radians((COALESCE(x1.longitude, (-0.1262)::real))::double precision)))))) * 3960::double precision)) < 1000::double precision)
14. 1.011 11.155 ↑ 1.0 121 1
→

Sort (cost=49.93..50.24 rows=123 width=105) (actual time=10.814..11.155 rows=121 loops=1)

  • Sort Key: sp.soe_id
  • Sort Method: quicksort Memory: 28kB
15. 0.748 10.144 ↑ 1.0 121 1
→

Hash Join (cost=36.35..45.66 rows=123 width=105) (actual time=8.319..10.144 rows=121 loops=1)

  • Hash Cond: ((sp.soe_id)::text = (x1.soe_id)::text)
16. 0.787 8.454 ↑ 1.0 120 1
→

Hash Join (cost=28.86..36.20 rows=120 width=90) (actual time=7.350..8.454 rows=120 loops=1)

  • Hash Cond: ((sp.id)::text = (spc.practices_id)::text)
17. 0.358 0.358 ↑ 1.2 126 1
→

Seq Scan on site_practices sp (cost=0.00..5.55 rows=155 width=48) (actual time=0.005..0.358 rows=126 loops=1)

18. 0.382 7.309 ↑ 1.0 120 1
→

Hash (cost=27.36..27.36 rows=120 width=42) (actual time=7.309..7.309 rows=120 loops=1)

19. 0.624 6.927 ↑ 1.0 120 1
→

Subquery Scan spc (cost=24.66..27.36 rows=120 width=42) (actual time=5.944..6.927 rows=120 loops=1)

20. 3.476 6.303 ↑ 1.0 120 1
→

HashAggregate (cost=24.66..26.16 rows=120 width=9) (actual time=5.939..6.303 rows=120 loops=1)

21. 2.827 2.827 ↑ 1.0 911 1
→

Seq Scan on site_practicioners x1 (cost=0.00..20.11 rows=911 width=9) (actual time=0.004..2.827 rows=911 loops=1)

22. 0.401 0.942 ↑ 1.2 126 1
→

Hash (cost=5.55..5.55 rows=155 width=15) (actual time=0.942..0.942 rows=126 loops=1)

23. 0.541 0.541 ↑ 1.2 126 1
→

Seq Scan on site_practices x1 (cost=0.00..5.55 rows=155 width=15) (actual time=0.034..0.541 rows=126 loops=1)

24. 0.566 307.477 ↑ 2.2 58 1
→

Sort (cost=2048.93..2049.24 rows=125 width=154) (actual time=307.314..307.477 rows=58 loops=1)

  • Sort Key: x2.soe_practice_id
  • Sort Method: quicksort Memory: 19kB
25. 75.137 306.911 ↑ 2.2 58 1
→

HashAggregate (cost=2040.51..2043.33 rows=125 width=15) (actual time=306.671..306.911 rows=58 loops=1)

26. 139.580 231.774 ↓ 1.0 22821 1
→

Hash Join (cost=460.14..1928.01 rows=22500 width=15) (actual time=12.699..231.774 rows=22821 loops=1)

  • Hash Cond: ((x1.rel_provider)::bpchar = (x2.rel_provider)::bpchar)
27. 79.700 84.216 ↓ 1.0 22821 1
→

Bitmap Heap Scan on slots x1 (cost=426.64..1585.14 rows=22500 width=17) (actual time=4.644..84.216 rows=22821 loops=1)

  • Recheck Cond: (start > now())
28. 4.516 4.516 ↓ 1.0 22821 1
→

Bitmap Index Scan on start_index (cost=0.00..421.01 rows=22500 width=0) (actual time=4.516..4.516 rows=22821 loops=1)

  • Index Cond: (start > now())
29. 3.786 7.978 ↑ 1.0 1089 1
→

Hash (cost=19.89..19.89 rows=1089 width=16) (actual time=7.978..7.978 rows=1089 loops=1)

30. 4.192 4.192 ↑ 1.0 1089 1
→

Seq Scan on soe_providers x2 (cost=0.00..19.89 rows=1089 width=16) (actual time=0.011..4.192 rows=1089 loops=1)

31. 5.040 5.040 ↓ 0.0 0 60
→

Index Scan using in_slots_rel_provider on slots s (cost=0.00..9.78 rows=1 width=50) (actual time=0.081..0.084 rows=0 loops=60)

  • Index Cond: ((s.rel_provider)::bpchar = (soepp.rel_provider)::bpchar)
  • Filter: ((s.start > '2012-05-04 12:48:00+02'::timestamp with time zone) AND (s.type = 'NewPatient'::text) AND (s.start < ('2012-05-04 12:48:00+02'::timestamp with time zone + '1 mon'::interval)))
32. 0.308 0.308 ↑ 1.0 1 22
→

Index Scan using providers_pkey on providers dp (cost=0.00..0.98 rows=1 width=17) (actual time=0.011..0.014 rows=1 loops=22)

  • Index Cond: ((dp.id)::bpchar = (s.rel_provider)::bpchar)
  • Filter: (dp.nhs_duration > 0)