explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YGC1

Settings
# exclusive inclusive rows x rows loops node
1. 32.220 5,841.489 ↓ 12.6 51,080 1

Hash Left Join (cost=104,487.38..178,048.91 rows=4,063 width=2,113) (actual time=4,905.759..5,841.489 rows=51,080 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
2. 9.307 5,809.260 ↓ 12.6 51,080 1

Hash Left Join (cost=104,486.27..177,963.31 rows=4,063 width=2,113) (actual time=4,905.729..5,809.260 rows=51,080 loops=1)

  • Hash Cond: ((ipr.primary_sponsor_id)::text = (itpa.tpa_id)::text)
3. 17.291 5,799.794 ↓ 12.6 51,080 1

Nested Loop Left Join (cost=104,471.24..177,893.44 rows=4,063 width=2,120) (actual time=4,905.558..5,799.794 rows=51,080 loops=1)

4. 17.753 5,782.503 ↓ 12.6 51,080 1

Nested Loop Left Join (cost=104,470.81..149,112.12 rows=4,063 width=2,120) (actual time=4,905.555..5,782.503 rows=51,080 loops=1)

5. 1,113.785 5,764.750 ↓ 12.6 51,080 1

Hash Right Join (cost=104,470.39..120,268.45 rows=4,063 width=2,107) (actual time=4,905.549..5,764.750 rows=51,080 loops=1)

  • Hash Cond: (sc.sample_collection_id = tp.sample_collection_id)
6. 78.740 78.740 ↑ 1.0 358,778 1

Seq Scan on sample_collection sc (cost=0.00..9,210.78 rows=358,778 width=6) (actual time=0.015..78.740 rows=358,778 loops=1)

7. 2,936.552 4,572.225 ↓ 12.6 51,080 1

Hash (cost=103,359.60..103,359.60 rows=4,063 width=2,109) (actual time=4,572.225..4,572.225 rows=51,080 loops=1)

  • Buckets: 1024 Batches: 1024 (originally 16) Memory Usage: 6569kB
8. 243.400 1,635.673 ↓ 12.6 51,080 1

Hash Left Join (cost=61,654.05..103,359.60 rows=4,063 width=2,109) (actual time=809.759..1,635.673 rows=51,080 loops=1)

  • Hash Cond: (tp.report_id = tvr.report_id)
  • Filter: ((tp.report_id = 0) OR (tp.report_id IS NULL) OR (tvr.signed_off = 'N'::bpchar))
  • Rows Removed by Filter: 101
9. 11.101 1,175.022 ↓ 3.2 51,181 1

Hash Left Join (cost=40,013.36..70,656.70 rows=15,806 width=2,103) (actual time=592.486..1,175.022 rows=51,181 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
10. 10.797 1,163.892 ↓ 7.3 51,181 1

Hash Left Join (cost=39,989.55..70,043.94 rows=7,035 width=1,889) (actual time=592.450..1,163.892 rows=51,181 loops=1)

  • Hash Cond: (((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text) AND (tp.prescribed_id = isrd.prescribed_id))
11. 11.406 1,153.084 ↓ 7.3 51,181 1

Hash Left Join (cost=39,988.20..69,989.82 rows=7,035 width=1,771) (actual time=592.432..1,153.084 rows=51,181 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (isr.incoming_visit_id)::text)
12. 16.247 1,141.667 ↓ 7.3 51,181 1

Hash Left Join (cost=39,986.96..69,962.18 rows=7,035 width=641) (actual time=592.415..1,141.667 rows=51,181 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tpa.tpa_id)::text)
13. 30.730 1,125.278 ↓ 7.3 51,181 1

Hash Left Join (cost=39,971.92..69,852.19 rows=7,035 width=648) (actual time=592.263..1,125.278 rows=51,181 loops=1)

  • Hash Cond: (tp.prescribed_id = oh.prescribed_id)
14. 366.808 1,092.618 ↓ 7.3 51,181 1

Hash Left Join (cost=39,746.22..69,546.80 rows=7,035 width=644) (actual time=590.322..1,092.618 rows=51,181 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (pr.patient_id)::text)
15. 9.518 247.156 ↓ 7.3 51,181 1

Hash Left Join (cost=3,252.75..27,782.21 rows=7,035 width=607) (actual time=17.222..247.156 rows=51,181 loops=1)

  • Hash Cond: (tp.prescribed_id = thr.prescribed_id)
16. 7.998 237.630 ↓ 7.3 51,181 1

Hash Left Join (cost=3,230.47..27,733.55 rows=7,035 width=389) (actual time=17.209..237.630 rows=51,181 loops=1)

  • Hash Cond: (tp.prescribed_id = tcr.prescribed_id)
17. 26.981 229.622 ↓ 7.3 51,181 1

Hash Join (cost=3,207.75..27,684.43 rows=7,035 width=171) (actual time=17.188..229.622 rows=51,181 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
18. 187.034 201.303 ↑ 1.1 161,937 1

Bitmap Heap Scan on tests_prescribed tp (cost=3,135.66..26,882.41 rows=175,863 width=96) (actual time=15.837..201.303 rows=161,937 loops=1)

  • Recheck Cond: ((conducted)::text = ANY ('{N,P,C,V,RC,RV,RP,MA,TS,CC,CR}'::text[]))
  • Rows Removed by Index Recheck: 242817
19. 14.269 14.269 ↑ 1.1 162,110 1

Bitmap Index Scan on tests_prescribed_conducted_idx (cost=0.00..3,091.70 rows=175,863 width=0) (actual time=14.269..14.269 rows=162,110 loops=1)

  • Index Cond: ((conducted)::text = ANY ('{N,P,C,V,RC,RV,RP,MA,TS,CC,CR}'::text[]))
20. 0.264 1.338 ↓ 15.6 1,217 1

Hash (cost=71.11..71.11 rows=78 width=91) (actual time=1.338..1.338 rows=1,217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 107kB
21. 0.225 1.074 ↓ 15.6 1,217 1

Hash Left Join (cost=2.80..71.11 rows=78 width=91) (actual time=0.045..1.074 rows=1,217 loops=1)

  • Hash Cond: (d.sample_type_id = st.sample_type_id)
22. 0.542 0.839 ↓ 15.6 1,217 1

Hash Join (cost=1.32..68.88 rows=78 width=94) (actual time=0.026..0.839 rows=1,217 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
23. 0.281 0.281 ↓ 1.0 1,956 1

Seq Scan on diagnostics d (cost=0.00..59.47 rows=1,947 width=56) (actual time=0.002..0.281 rows=1,956 loops=1)

24. 0.004 0.016 ↓ 15.0 15 1

Hash (cost=1.31..1.31 rows=1 width=76) (actual time=0.016..0.016 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
25. 0.012 0.012 ↓ 15.0 15 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.31 rows=1 width=76) (actual time=0.010..0.012 rows=15 loops=1)

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 10
26. 0.001 0.010 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=5) (actual time=0.010..0.010 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
27. 0.009 0.009 ↑ 1.0 21 1

Seq Scan on sample_type st (cost=0.00..1.21 rows=21 width=5) (actual time=0.005..0.009 rows=21 loops=1)

28. 0.000 0.010 ↓ 0.0 0 1

Hash (cost=22.35..22.35 rows=30 width=222) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
29. 0.009 0.010 ↓ 0.0 0 1

Hash Right Join (cost=10.68..22.35 rows=30 width=222) (actual time=0.010..0.010 rows=0 loops=1)

  • Hash Cond: (cim.impression_id = tcr.impression_id)
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on histo_impression_master cim (cost=0.00..11.00 rows=100 width=222) (never executed)

31. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=10.30..10.30 rows=30 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
32. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on test_cytology_results tcr (cost=0.00..10.30 rows=30 width=8) (actual time=0.001..0.001 rows=0 loops=1)

33. 0.001 0.008 ↓ 0.0 0 1

Hash (cost=22.02..22.02 rows=20 width=222) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
34. 0.007 0.007 ↓ 0.0 0 1

Hash Right Join (cost=10.45..22.02 rows=20 width=222) (actual time=0.007..0.007 rows=0 loops=1)

  • Hash Cond: (him.impression_id = thr.impression_id)
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on histo_impression_master him (cost=0.00..11.00 rows=100 width=222) (never executed)

36. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=10.20..10.20 rows=20 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
37. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on test_histopathology_results thr (cost=0.00..10.20 rows=20 width=8) (actual time=0.000..0.000 rows=0 loops=1)

38. 234.326 478.654 ↓ 1.0 519,951 1

Hash (cost=25,932.32..25,932.32 rows=519,932 width=37) (actual time=478.654..478.654 rows=519,951 loops=1)

  • Buckets: 2048 Batches: 64 Memory Usage: 600kB
39. 244.328 244.328 ↓ 1.0 519,951 1

Seq Scan on patient_registration pr (cost=0.00..25,932.32 rows=519,932 width=37) (actual time=0.008..244.328 rows=519,951 loops=1)

40. 0.925 1.930 ↑ 1.0 7,098 1

Hash (cost=136.98..136.98 rows=7,098 width=8) (actual time=1.930..1.930 rows=7,098 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 277kB
41. 1.005 1.005 ↑ 1.0 7,098 1

Seq Scan on outsource_sample_details oh (cost=0.00..136.98 rows=7,098 width=8) (actual time=0.005..1.005 rows=7,098 loops=1)

42. 0.058 0.142 ↑ 1.0 357 1

Hash (cost=10.57..10.57 rows=357 width=12) (actual time=0.142..0.142 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
43. 0.084 0.084 ↑ 1.0 357 1

Seq Scan on tpa_master tpa (cost=0.00..10.57 rows=357 width=12) (actual time=0.003..0.084 rows=357 loops=1)

44. 0.002 0.011 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=1,130) (actual time=0.011..0.011 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.009 0.009 ↑ 1.0 11 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.11 rows=11 width=1,130) (actual time=0.006..0.009 rows=11 loops=1)

46. 0.002 0.011 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=170) (actual time=0.011..0.011 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
47. 0.009 0.009 ↑ 1.0 14 1

Seq Scan on incoming_sample_registration_details isrd (cost=0.00..1.14 rows=14 width=170) (actual time=0.006..0.009 rows=14 loops=1)

48. 0.005 0.029 ↑ 47.0 10 1

Hash (cost=17.93..17.93 rows=470 width=222) (actual time=0.029..0.029 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
49. 0.008 0.024 ↑ 47.0 10 1

Hash Left Join (cost=1.23..17.93 rows=470 width=222) (actual time=0.022..0.024 rows=10 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
50. 0.006 0.006 ↑ 47.0 10 1

Seq Scan on diag_outsource_master dom (cost=0.00..14.70 rows=470 width=122) (actual time=0.005..0.006 rows=10 loops=1)

51. 0.003 0.010 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=336) (actual time=0.010..0.010 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
52. 0.007 0.007 ↑ 1.0 10 1

Seq Scan on outhouse_master om (cost=0.00..1.10 rows=10 width=336) (actual time=0.006..0.007 rows=10 loops=1)

53. 122.752 217.251 ↑ 1.0 517,142 1

Hash (cost=12,650.42..12,650.42 rows=517,142 width=16) (actual time=217.251..217.251 rows=517,142 loops=1)

  • Buckets: 4096 Batches: 32 Memory Usage: 804kB
54. 94.499 94.499 ↑ 1.0 517,142 1

Seq Scan on test_visit_reports tvr (cost=0.00..12,650.42 rows=517,142 width=16) (actual time=0.007..94.499 rows=517,142 loops=1)

55. 0.000 0.000 ↓ 0.0 0 51,080

Index Scan using tests_prescribed_pkey on tests_prescribed itp (cost=0.42..7.09 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=51,080)

  • Index Cond: (prescribed_id = tp.coll_prescribed_id)
56. 0.000 0.000 ↓ 0.0 0 51,080

Index Scan using patient_registration_pkey on patient_registration ipr (cost=0.42..7.07 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=51,080)

  • Index Cond: ((patient_id)::text = (itp.pat_id)::text)
57. 0.055 0.159 ↑ 1.0 357 1

Hash (cost=10.57..10.57 rows=357 width=12) (actual time=0.159..0.159 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
58. 0.104 0.104 ↑ 1.0 357 1

Seq Scan on tpa_master itpa (cost=0.00..10.57 rows=357 width=12) (actual time=0.016..0.104 rows=357 loops=1)

59. 0.002 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=266) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
60. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on incoming_hospitals ih (cost=0.00..1.05 rows=5 width=266) (actual time=0.007..0.007 rows=5 loops=1)