explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9APR

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 157.005 ↑ 10.0 2 1

Limit (cost=54,964.78..54,971.33 rows=20 width=1,610) (actual time=156.987..157.005 rows=2 loops=1)

  • Total runtime: 157.259 ms
2. 0.060 157.004 ↑ 151.5 2 1

Group (cost=54,964.78..55,064.01 rows=303 width=1,610) (actual time=156.986..157.004 rows=2 loops=1)

3. 0.016 156.944 ↑ 151.5 2 1

Sort (cost=54,964.78..54,965.54 rows=303 width=1,610) (actual time=156.943..156.944 rows=2 loops=1)

  • Sort Key: tv.report_id, (COALESCE(((((((b.total_amount - b.primary_total_claim) - b.secondary_total_claim) - b.total_receipts) - b.deposit_set_off) - b.points_redeemed_amt)), 0::numeric)), tp.mr_no, isr.mr_no, pd.salutation, pd.patient_name, pd.middle_name, pd.last_name, tp.pat_id, pr.reference_docto_id, isr.referring_doctor, tv.report_date, tv.report_name, isr.patient_name, tv.addendum_signed_off, tv.handed_over, tv.handed_over_to, tv.hand_over_time, tv.num_prints, tv.report_addendum, him.impression_id, him.short_impression, ipr.center_id, pr.center_id, isr.incoming_source_type, tv.signoff_center, isr.center_id, (CASE WHEN (COALESCE(tpa.sponsor_type, itpa.sponsor_type, 'R'::bpchar) = 'R'::bpchar) THEN 'R'::text ELSE 'S'::text END), (CASE WHEN (pr.patient_id IS NOT NULL) THEN 'hospital'::text ELSE 'incoming'::text END)
  • Sort Method: quicksort Memory: 25kB
4. 0.008 156.928 ↑ 151.5 2 1

Hash Left Join (cost=53,926.95..54,952.29 rows=303 width=1,610) (actual time=156.865..156.928 rows=2 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
5. 0.003 156.916 ↑ 67.5 2 1

Hash Left Join (cost=53,906.37..54,919.66 rows=135 width=1,614) (actual time=156.855..156.916 rows=2 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (oh.visit_id)::text)
6. 0.004 155.323 ↑ 67.5 2 1

Nested Loop Left Join (cost=53,680.37..54,673.84 rows=135 width=1,610) (actual time=155.262..155.323 rows=2 loops=1)

7. 0.007 155.167 ↑ 67.5 2 1

Merge Left Join (cost=53,679.95..53,680.74 rows=135 width=1,590) (actual time=155.165..155.167 rows=2 loops=1)

  • Merge Cond: ((tp.pat_id)::text = (isr.incoming_visit_id)::text)
8. 0.005 155.141 ↑ 67.5 2 1

Sort (cost=53,678.32..53,678.66 rows=135 width=892) (actual time=155.141..155.141 rows=2 loops=1)

  • Sort Key: tp.pat_id
  • Sort Method: quicksort Memory: 25kB
9. 0.004 155.136 ↑ 67.5 2 1

Hash Left Join (cost=107.49..53,673.54 rows=135 width=892) (actual time=155.072..155.136 rows=2 loops=1)

  • Hash Cond: ((ipr.primary_sponsor_id)::text = (itpa.tpa_id)::text)
10. 0.001 155.013 ↑ 67.5 2 1

Nested Loop Left Join (cost=91.25..53,655.48 rows=135 width=899) (actual time=154.951..155.013 rows=2 loops=1)

11. 0.001 155.012 ↑ 67.5 2 1

Nested Loop Left Join (cost=90.83..52,694.19 rows=135 width=895) (actual time=154.950..155.012 rows=2 loops=1)

12. 0.004 155.009 ↑ 67.5 2 1

Hash Left Join (cost=90.41..51,731.61 rows=135 width=890) (actual time=154.948..155.009 rows=2 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tpa.tpa_id)::text)
13. 0.005 154.882 ↑ 67.5 2 1

Nested Loop Left Join (cost=74.17..51,713.55 rows=135 width=897) (actual time=154.821..154.882 rows=2 loops=1)

14. 0.000 154.859 ↑ 67.5 2 1

Nested Loop Left Join (cost=73.75..50,752.26 rows=135 width=860) (actual time=154.808..154.859 rows=2 loops=1)

  • Join Filter: (him.impression_id = thr.impression_id)
15. 0.000 154.858 ↑ 67.5 2 1

Nested Loop Left Join (cost=73.75..50,538.51 rows=135 width=642) (actual time=154.807..154.858 rows=2 loops=1)

  • Join Filter: (tp.prescribed_id = thr.prescribed_id)
16. 0.005 154.856 ↑ 67.5 2 1

Nested Loop Left Join (cost=73.75..50,487.76 rows=135 width=642) (actual time=154.805..154.856 rows=2 loops=1)

17. 0.010 154.773 ↑ 67.5 2 1

Hash Join (cost=72.45..46,115.19 rows=135 width=610) (actual time=154.756..154.773 rows=2 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
18. 0.010 154.142 ↑ 1,125.3 3 1

Nested Loop (cost=0.42..46,029.16 rows=3,376 width=618) (actual time=154.126..154.142 rows=3 loops=1)

19. 154.114 154.114 ↑ 734.3 3 1

Seq Scan on test_visit_reports tv (cost=0.00..21,700.90 rows=2,203 width=583) (actual time=154.110..154.114 rows=3 loops=1)

  • Filter: ((report_state <> 'D'::bpchar) AND (handed_over = 'N'::bpchar) AND (signed_off = 'Y'::bpchar) AND (date(report_date) >= '2019-06-01'::date) AND (date(report_date) <= '2019-07-01'::date))
  • Rows Removed by Filter: 517157
20. 0.018 0.018 ↑ 6.0 1 3

Index Scan using tests_prescribed_report_idx on tests_prescribed tp (cost=0.42..10.98 rows=6 width=39) (actual time=0.005..0.006 rows=1 loops=3)

  • Index Cond: (report_id = tv.report_id)
  • Filter: ((conducted)::text <> 'RAS'::text)
21. 0.137 0.621 ↓ 15.6 1,218 1

Hash (cost=71.06..71.06 rows=78 width=8) (actual time=0.621..0.621 rows=1,218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
22. 0.338 0.484 ↓ 15.6 1,218 1

Hash Join (cost=1.32..71.06 rows=78 width=8) (actual time=0.018..0.484 rows=1,218 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
23. 0.136 0.136 ↑ 1.0 1,960 1

Seq Scan on diagnostics d (cost=0.00..61.60 rows=1,960 width=18) (actual time=0.002..0.136 rows=1,960 loops=1)

24. 0.003 0.010 ↓ 15.0 15 1

Hash (cost=1.31..1.31 rows=1 width=38) (actual time=0.010..0.010 rows=15 loops=1)

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

Seq Scan on diagnostics_departments dd (cost=0.00..1.31 rows=1 width=38) (actual time=0.003..0.007 rows=15 loops=1)

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 10
26. 0.002 0.078 ↑ 1.0 1 2

Limit (cost=1.29..32.37 rows=1 width=23) (actual time=0.039..0.039 rows=1 loops=2)

27. 0.006 0.076 ↑ 1.0 1 2

Nested Loop (cost=1.29..32.37 rows=1 width=23) (actual time=0.038..0.038 rows=1 loops=2)

28. 0.010 0.048 ↑ 1.0 1 2

Nested Loop (cost=0.86..24.67 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2)

29. 0.020 0.020 ↑ 1.0 1 2

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.43..16.21 rows=1 width=9) (actual time=0.010..0.010 rows=1 loops=2)

  • Index Cond: ((activity_id)::text = ((tp.prescribed_id)::character varying)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
30. 0.018 0.018 ↑ 1.0 1 2

Index Scan using bill_charge_charge_id_idx on bill_charge bc (cost=0.43..8.45 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=2)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
31. 0.022 0.022 ↑ 1.0 1 2

Index Scan using bill_pkey on bill b (cost=0.43..7.68 rows=1 width=31) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
32. 0.002 0.002 ↓ 0.0 0 2

Materialize (cost=0.00..10.30 rows=20 width=8) (actual time=0.001..0.001 rows=0 loops=2)

33. 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)

34. 0.001 0.002 ↓ 0.0 0 2

Materialize (cost=0.00..11.50 rows=100 width=222) (actual time=0.001..0.001 rows=0 loops=2)

35. 0.001 0.001 ↓ 0.0 0 1

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

36. 0.018 0.018 ↑ 1.0 1 2

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..7.11 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=2)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
37. 0.051 0.123 ↑ 1.0 366 1

Hash (cost=11.66..11.66 rows=366 width=12) (actual time=0.123..0.123 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
38. 0.072 0.072 ↑ 1.0 366 1

Seq Scan on tpa_master tpa (cost=0.00..11.66 rows=366 width=12) (actual time=0.002..0.072 rows=366 loops=1)

39. 0.002 0.002 ↓ 0.0 0 2

Index Scan using tests_prescribed_pkey on tests_prescribed itp (cost=0.42..7.12 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: (prescribed_id = tp.coll_prescribed_id)
40. 0.000 0.000 ↓ 0.0 0 2

Index Scan using patient_registration_pkey on patient_registration ipr (cost=0.42..7.11 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=2)

  • Index Cond: ((patient_id)::text = (itp.pat_id)::text)
41. 0.047 0.119 ↑ 1.0 366 1

Hash (cost=11.66..11.66 rows=366 width=12) (actual time=0.119..0.119 rows=366 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
42. 0.072 0.072 ↑ 1.0 366 1

Seq Scan on tpa_master itpa (cost=0.00..11.66 rows=366 width=12) (actual time=0.001..0.072 rows=366 loops=1)

43. 0.013 0.019 ↑ 1.0 20 1

Sort (cost=1.63..1.68 rows=20 width=964) (actual time=0.017..0.019 rows=20 loops=1)

  • Sort Key: isr.incoming_visit_id
  • Sort Method: quicksort Memory: 26kB
44. 0.006 0.006 ↑ 1.0 20 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.20 rows=20 width=964) (actual time=0.002..0.006 rows=20 loops=1)

45. 0.152 0.152 ↑ 1.0 1 2

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..7.35 rows=1 width=40) (actual time=0.076..0.076 rows=1 loops=2)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
  • Filter: patient_confidentiality_check(COALESCE(patient_group, 0), mr_no)
46. 0.793 1.590 ↑ 1.0 7,111 1

Hash (cost=137.11..137.11 rows=7,111 width=13) (actual time=1.590..1.590 rows=7,111 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 332kB
47. 0.797 0.797 ↑ 1.0 7,111 1

Seq Scan on outsource_sample_details oh (cost=0.00..137.11 rows=7,111 width=13) (actual time=0.002..0.797 rows=7,111 loops=1)

48. 0.000 0.004 ↑ 47.0 10 1

Hash (cost=14.70..14.70 rows=470 width=122) (actual time=0.004..0.004 rows=10 loops=1)

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

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