explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NhPh

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 34,687.138 ↑ 10.0 2 1

Limit (cost=1,528,540.80..1,528,546.60 rows=20 width=924) (actual time=34,686.989..34,687.138 rows=2 loops=1)

2. 0.190 34,687.136 ↑ 431,443.5 2 1

Group (cost=1,528,540.80..1,778,778.03 rows=862,887 width=924) (actual time=34,686.988..34,687.136 rows=2 loops=1)

  • Group Key: tv.report_id, tp.mr_no, isr.mr_no, pd.salutation, pd.patient_name, pd.middle_name, pd.last_name, tp.pat_id, isr.patient_name, him.impression_id, isr.source_center_id, isr.incoming_source_type, isr.center_id, pr.patient_id, (CASE WHEN (pr.patient_id IS NOT NULL) THEN 'hospital'::text ELSE 'incoming'::text END)
3. 0.024 34,686.946 ↑ 431,443.5 2 1

Sort (cost=1,528,540.80..1,530,698.02 rows=862,887 width=860) (actual time=34,686.945..34,686.946 rows=2 loops=1)

  • Sort Key: tv.report_id DESC, tp.mr_no, isr.mr_no, pd.salutation, pd.patient_name, pd.middle_name, pd.last_name, tp.pat_id, isr.patient_name, him.impression_id, isr.source_center_id, isr.incoming_source_type, isr.center_id, pr.patient_id, (CASE WHEN (pr.patient_id IS NOT NULL) THEN 'hospital'::text ELSE 'incoming'::text END)
  • Sort Method: quicksort Memory: 25kB
4. 0.016 34,686.922 ↑ 431,443.5 2 1

Hash Left Join (cost=15,583.54..1,116,089.27 rows=862,887 width=860) (actual time=30,432.180..34,686.922 rows=2 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
5. 0.007 34,686.879 ↑ 431,443.5 2 1

Nested Loop Left Join (cost=15,580.69..1,104,221.73 rows=862,887 width=832) (actual time=30,432.139..34,686.879 rows=2 loops=1)

6. 0.396 34,686.846 ↑ 112,459.5 2 1

Hash Left Join (cost=15,580.26..963,069.10 rows=224,919 width=828) (actual time=30,432.113..34,686.846 rows=2 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (isr.incoming_visit_id)::text)
7. 0.006 34,513.997 ↑ 112,459.5 2 1

Nested Loop Left Join (cost=24.00..946,922.41 rows=224,919 width=777) (actual time=30,259.265..34,513.997 rows=2 loops=1)

8. 0.007 34,513.979 ↑ 112,459.5 2 1

Nested Loop Left Join (cost=23.57..822,218.41 rows=224,919 width=759) (actual time=30,259.250..34,513.979 rows=2 loops=1)

9. 0.022 34,513.950 ↑ 112,459.5 2 1

Hash Left Join (cost=23.14..692,078.66 rows=224,919 width=726) (actual time=30,259.234..34,513.950 rows=2 loops=1)

  • Hash Cond: (tp.prescribed_id = thr.prescribed_id)
10. 4,600.477 34,513.919 ↑ 112,459.5 2 1

Nested Loop (cost=0.87..691,212.93 rows=224,919 width=508) (actual time=30,259.205..34,513.919 rows=2 loops=1)

11. 1,397.398 9,509.874 ↓ 10.8 10,201,784 1

Nested Loop (cost=0.43..207,396.86 rows=946,909 width=38) (actual time=0.034..9,509.874 rows=10,201,784 loops=1)

12. 1.836 3.566 ↓ 7.6 1,215 1

Nested Loop (cost=0.00..93.10 rows=159 width=8) (actual time=0.014..3.566 rows=1,215 loops=1)

  • Join Filter: ((d.ddept_id)::text = (dd.ddept_id)::text)
  • Rows Removed by Join Filter: 10239
13. 0.014 0.014 ↓ 6.0 6 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.15 rows=1 width=38) (actual time=0.007..0.014 rows=6 loops=1)

  • Filter: ((category)::text = 'DEP_LAB'::text)
14. 1.716 1.716 ↑ 1.0 1,909 6

Seq Scan on diagnostics d (cost=0.00..68.09 rows=1,909 width=18) (actual time=0.003..0.286 rows=1,909 loops=6)

15. 8,108.910 8,108.910 ↑ 1.8 8,397 1,215

Index Scan using tests_prescribed_test_id_idx on tests_prescribed tp (cost=0.43..1,152.90 rows=15,090 width=46) (actual time=0.010..6.674 rows=8,397 loops=1,215)

  • Index Cond: ((test_id)::text = (d.test_id)::text)
  • Filter: ((conducted)::text <> 'RAS'::text)
  • Rows Removed by Filter: 1
16. 20,403.568 20,403.568 ↓ 0.0 0 10,201,784

Index Scan using test_visit_reports_pkey on test_visit_reports tv (cost=0.43..0.51 rows=1 width=474) (actual time=0.002..0.002 rows=0 loops=10,201,784)

  • Index Cond: (report_id = tp.report_id)
  • Filter: ((report_state <> 'D'::bpchar) AND (handed_over = 'N'::bpchar) AND (signed_off = 'Y'::bpchar) AND (date(report_date) >= '2020-01-06'::date))
  • Rows Removed by Filter: 1
17. 0.000 0.009 ↓ 0.0 0 1

Hash (cost=22.02..22.02 rows=20 width=226) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 0.003 0.009 ↓ 0.0 0 1

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

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

20. 0.000 0.006 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 0.006 0.006 ↓ 0.0 0 1

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

22. 0.022 0.022 ↓ 0.0 0 2

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..0.58 rows=1 width=33) (actual time=0.011..0.011 rows=0 loops=2)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
23. 0.012 0.012 ↓ 0.0 0 2

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.55 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=2)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
24. 172.453 172.453 ↓ 1.0 364,472 1

Hash (cost=11,000.56..11,000.56 rows=364,456 width=67) (actual time=172.453..172.453 rows=364,472 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 41041kB
  • -> Seq Scan on incoming_sample_registration isr (cost=0.00..11000.56 rows=364456 width=67) (actual tim:
25. 0.026 0.026 ↓ 0.0 0 2

Index Scan using idx_outsource_sample_details_visit_id on outsource_sample_details oh (cost=0.43..0.56 rows=7 width=20) (actual time=0.013..0.013 rows=0 loops=2)

  • Index Cond: ((visit_id)::text = (tp.pat_id)::text)
26. 0.010 0.027 ↑ 1.0 82 1

Hash (cost=1.82..1.82 rows=82 width=14) (actual time=0.027..0.027 rows=82 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.017 0.017 ↑ 1.0 82 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.82 rows=82 width=14) (actual time=0.008..0.017 rows=82 loops=1)

Planning time : 12.979 ms
Execution time : 34,687.503 ms