explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hnpn

Settings
# exclusive inclusive rows x rows loops node
1. 582.641 1,254.181 ↓ 2.3 13,100 1

Hash Left Join (cost=10,667.62..23,590.42 rows=5,584 width=904) (actual time=453.014..1,254.181 rows=13,100 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
  • Total runtime: 1257.501 ms
2. 8.130 671.478 ↓ 2.3 13,100 1

Hash Left Join (cost=10,665.90..22,060.08 rows=5,584 width=693) (actual time=452.308..671.478 rows=13,100 loops=1)

  • Hash Cond: ((ipr.primary_sponsor_id)::text = (itpa.tpa_id)::text)
3. 6.337 663.192 ↓ 2.3 13,100 1

Nested Loop Left Join (cost=10,660.58..22,005.97 rows=5,584 width=696) (actual time=452.114..663.192 rows=13,100 loops=1)

4. 9.390 630.655 ↓ 2.3 13,100 1

Nested Loop Left Join (cost=10,660.16..17,048.33 rows=5,584 width=700) (actual time=452.111..630.655 rows=13,100 loops=1)

5. 10.907 608.165 ↓ 2.3 13,100 1

Hash Left Join (cost=10,659.87..13,599.22 rows=5,584 width=688) (actual time=452.104..608.165 rows=13,100 loops=1)

  • Hash Cond: (((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text) AND (tp.prescribed_id = isrd.prescribed_id))
6. 10.144 596.068 ↓ 2.3 13,100 1

Hash Left Join (cost=10,623.82..13,521.28 rows=5,584 width=683) (actual time=450.887..596.068 rows=13,100 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (isr.incoming_visit_id)::text)
7. 8.513 585.579 ↓ 2.3 13,100 1

Hash Left Join (cost=10,614.42..13,489.62 rows=5,584 width=641) (actual time=450.510..585.579 rows=13,100 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tpa.tpa_id)::text)
8. 6.883 576.891 ↓ 2.3 13,100 1

Hash Left Join (cost=10,609.10..13,435.51 rows=5,584 width=644) (actual time=450.305..576.891 rows=13,100 loops=1)

  • Hash Cond: (tcr.impression_id = cim.impression_id)
9. 8.102 570.007 ↓ 2.3 13,100 1

Hash Left Join (cost=10,596.85..13,346.48 rows=5,584 width=430) (actual time=450.272..570.007 rows=13,100 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
10. 70.255 561.693 ↓ 2.3 13,100 1

Hash Left Join (cost=10,590.78..13,263.63 rows=5,584 width=422) (actual time=450.000..561.693 rows=13,100 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (pr.patient_id)::text)
11. 37.589 157.990 ↓ 2.3 13,100 1

Hash Right Join (cost=2,756.56..3,893.75 rows=5,584 width=394) (actual time=105.999..157.990 rows=13,100 loops=1)

  • Hash Cond: (oh.prescribed_id = tp.prescribed_id)
12. 14.637 14.637 ↑ 1.0 26,507 1

Seq Scan on outsource_sample_details oh (cost=0.00..511.07 rows=26,507 width=8) (actual time=0.020..14.637 rows=26,507 loops=1)

13. 17.242 105.764 ↓ 2.3 13,095 1

Hash (cost=2,402.76..2,402.76 rows=5,584 width=390) (actual time=105.764..105.764 rows=13,095 loops=1)

  • Buckets: 1024 Batches: 4 Memory Usage: 494kB
14. 6.034 88.522 ↓ 2.3 13,095 1

Hash Left Join (cost=440.85..2,402.76 rows=5,584 width=390) (actual time=14.177..88.522 rows=13,095 loops=1)

  • Hash Cond: (tp.prescribed_id = tcr.prescribed_id)
15. 5.512 82.487 ↓ 2.3 13,095 1

Hash Left Join (cost=430.17..2,371.10 rows=5,584 width=386) (actual time=14.155..82.487 rows=13,095 loops=1)

  • Hash Cond: (tp.prescribed_id = thr.prescribed_id)
16. 11.382 76.949 ↓ 2.3 13,095 1

Hash Join (cost=407.90..2,327.86 rows=5,584 width=168) (actual time=14.093..76.949 rows=13,095 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
17. 7.811 62.403 ↓ 2.3 13,095 1

Hash Left Join (cost=331.11..2,174.29 rows=5,584 width=92) (actual time=10.863..62.403 rows=13,095 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: 1
18. 43.783 43.783 ↓ 1.0 13,096 1

Seq Scan on tests_prescribed tp (cost=0.00..1,547.67 rows=12,979 width=86) (actual time=0.020..43.783 rows=13,096 loops=1)

  • Filter: ((conducted)::text <> ALL ('{X,U,RBS,RAS,S}'::text[]))
  • Rows Removed by Filter: 29530
19. 4.130 10.809 ↑ 1.0 8,805 1

Hash (cost=221.05..221.05 rows=8,805 width=16) (actual time=10.809..10.809 rows=8,805 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 430kB
20. 6.679 6.679 ↑ 1.0 8,805 1

Seq Scan on test_visit_reports tvr (cost=0.00..221.05 rows=8,805 width=16) (actual time=0.024..6.679 rows=8,805 loops=1)

21. 0.828 3.164 ↑ 1.0 1,044 1

Hash (cost=63.73..63.73 rows=1,044 width=84) (actual time=3.164..3.164 rows=1,044 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 88kB
22. 0.513 2.336 ↑ 1.0 1,044 1

Hash Left Join (cost=6.83..63.73 rows=1,044 width=84) (actual time=0.290..2.336 rows=1,044 loops=1)

  • Hash Cond: (d.sample_type_id = st.sample_type_id)
23. 0.994 1.641 ↑ 1.0 1,044 1

Hash Join (cost=1.97..46.76 rows=1,044 width=87) (actual time=0.083..1.641 rows=1,044 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
24. 0.606 0.606 ↑ 1.0 1,044 1

Seq Scan on diagnostics d (cost=0.00..30.44 rows=1,044 width=49) (actual time=0.018..0.606 rows=1,044 loops=1)

25. 0.014 0.041 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=76) (actual time=0.041..0.041 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
26. 0.027 0.027 ↑ 1.0 43 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.43 rows=43 width=76) (actual time=0.015..0.027 rows=43 loops=1)

27. 0.073 0.182 ↑ 1.0 127 1

Hash (cost=3.27..3.27 rows=127 width=5) (actual time=0.182..0.182 rows=127 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
28. 0.109 0.109 ↑ 1.0 127 1

Seq Scan on sample_type st (cost=0.00..3.27 rows=127 width=5) (actual time=0.005..0.109 rows=127 loops=1)

29. 0.000 0.026 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
30. 0.025 0.026 ↓ 0.0 0 1

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

  • Hash Cond: (him.impression_id = thr.impression_id)
31. 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)

32. 0.000 0.001 ↓ 0.0 0 1

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

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

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

34. 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
35. 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)

36. 89.433 333.448 ↑ 1.0 122,321 1

Hash (cost=5,468.21..5,468.21 rows=122,321 width=28) (actual time=333.448..333.448 rows=122,321 loops=1)

  • Buckets: 2048 Batches: 8 Memory Usage: 905kB
37. 244.015 244.015 ↑ 1.0 122,321 1

Seq Scan on patient_registration pr (cost=0.00..5,468.21 rows=122,321 width=28) (actual time=0.034..244.015 rows=122,321 loops=1)

38. 0.041 0.212 ↑ 1.0 76 1

Hash (cost=5.12..5.12 rows=76 width=16) (actual time=0.212..0.212 rows=76 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
39. 0.079 0.171 ↑ 1.0 76 1

Hash Left Join (cost=2.44..5.12 rows=76 width=16) (actual time=0.110..0.171 rows=76 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
40. 0.030 0.030 ↑ 1.0 76 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.76 rows=76 width=13) (actual time=0.016..0.030 rows=76 loops=1)

41. 0.040 0.062 ↑ 1.0 64 1

Hash (cost=1.64..1.64 rows=64 width=23) (actual time=0.062..0.062 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
42. 0.022 0.022 ↑ 1.0 64 1

Seq Scan on outhouse_master om (cost=0.00..1.64 rows=64 width=23) (actual time=0.005..0.022 rows=64 loops=1)

43. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=11.00..11.00 rows=100 width=222) (actual time=0.001..0.001 rows=0 loops=1)

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

Seq Scan on histo_impression_master cim (cost=0.00..11.00 rows=100 width=222) (actual time=0.001..0.001 rows=0 loops=1)

45. 0.064 0.175 ↑ 1.0 103 1

Hash (cost=4.03..4.03 rows=103 width=12) (actual time=0.175..0.175 rows=103 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
46. 0.111 0.111 ↑ 1.0 103 1

Seq Scan on tpa_master tpa (cost=0.00..4.03 rows=103 width=12) (actual time=0.006..0.111 rows=103 loops=1)

47. 0.183 0.345 ↑ 1.0 240 1

Hash (cost=6.40..6.40 rows=240 width=42) (actual time=0.345..0.345 rows=240 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
48. 0.162 0.162 ↑ 1.0 240 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6.40 rows=240 width=42) (actual time=0.016..0.162 rows=240 loops=1)

49. 0.705 1.190 ↑ 1.0 1,082 1

Hash (cost=19.82..19.82 rows=1,082 width=18) (actual time=1.190..1.190 rows=1,082 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
50. 0.485 0.485 ↑ 1.0 1,082 1

Seq Scan on incoming_sample_registration_details isrd (cost=0.00..19.82 rows=1,082 width=18) (actual time=0.028..0.485 rows=1,082 loops=1)

51. 13.100 13.100 ↓ 0.0 0 13,100

Index Scan using tests_prescribed_pkey on tests_prescribed itp (cost=0.29..0.61 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=13,100)

  • Index Cond: (prescribed_id = tp.coll_prescribed_id)
52. 26.200 26.200 ↓ 0.0 0 13,100

Index Scan using patient_registration_pkey on patient_registration ipr (cost=0.42..0.88 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=13,100)

  • Index Cond: ((patient_id)::text = (itp.pat_id)::text)
53. 0.075 0.156 ↑ 1.0 103 1

Hash (cost=4.03..4.03 rows=103 width=12) (actual time=0.156..0.156 rows=103 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
54. 0.081 0.081 ↑ 1.0 103 1

Seq Scan on tpa_master itpa (cost=0.00..4.03 rows=103 width=12) (actual time=0.006..0.081 rows=103 loops=1)

55. 0.017 0.062 ↑ 1.0 32 1

Hash (cost=1.32..1.32 rows=32 width=266) (actual time=0.062..0.062 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
56. 0.045 0.045 ↑ 1.0 32 1

Seq Scan on incoming_hospitals ih (cost=0.00..1.32 rows=32 width=266) (actual time=0.037..0.045 rows=32 loops=1)