explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LJr

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,038.094 ↓ 0.0 0 1

Limit (cost=1,167.84..38,435.12 rows=10 width=621) (actual time=3,038.094..3,038.094 rows=0 loops=1)

2. 0.002 3,038.092 ↓ 0.0 0 1

Group (cost=1,167.84..38,435.12 rows=10 width=621) (actual time=3,038.092..3,038.092 rows=0 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, 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)
3. 2.061 3,038.090 ↓ 0.0 0 1

Sort (cost=1,167.84..1,167.87 rows=10 width=621) (actual time=3,038.090..3,038.090 rows=0 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, 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.002 3,036.029 ↓ 0.0 0 1

Nested Loop Left Join (cost=543.37..1,167.68 rows=10 width=621) (actual time=3,036.029..3,036.029 rows=0 loops=1)

  • Join Filter: (dom.outsource_dest_id = oh.outsource_dest_id)
5. 0.025 3,036.027 ↓ 0.0 0 1

Hash Right Join (cost=543.37..1,154.30 rows=10 width=625) (actual time=3,036.027..3,036.027 rows=0 loops=1)

  • Hash Cond: ((oh.visit_id)::text = (tp.pat_id)::text)
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on outsource_sample_details oh (cost=0.00..511.33 rows=26,533 width=13) (never executed)

7. 0.000 3,036.002 ↓ 0.0 0 1

Hash (cost=543.35..543.35 rows=2 width=621) (actual time=3,036.002..3,036.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
8. 0.001 3,036.002 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.11..543.35 rows=2 width=621) (actual time=3,036.002..3,036.002 rows=0 loops=1)

9. 0.001 3,036.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.97..543.01 rows=2 width=624) (actual time=3,036.001..3,036.001 rows=0 loops=1)

10. 0.001 3,036.000 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.55..541.24 rows=2 width=624) (actual time=3,036.000..3,036.000 rows=0 loops=1)

11. 0.001 3,035.999 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.26..540.42 rows=2 width=619) (actual time=3,035.999..3,035.999 rows=0 loops=1)

12. 0.000 3,035.998 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.11..540.08 rows=2 width=590) (actual time=3,035.998..3,035.998 rows=0 loops=1)

13. 0.002 3,035.998 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.97..539.74 rows=2 width=593) (actual time=3,035.998..3,035.998 rows=0 loops=1)

14. 0.000 3,035.996 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.69..538.58 rows=2 width=578) (actual time=3,035.996..3,035.996 rows=0 loops=1)

15. 0.000 3,035.996 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.27..536.80 rows=2 width=549) (actual time=3,035.996..3,035.996 rows=0 loops=1)

16. 0.001 3,035.996 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.12..532.06 rows=2 width=331) (actual time=3,035.996..3,035.996 rows=0 loops=1)

  • Join Filter: (tp.prescribed_id = thr.prescribed_id)
17. 6.658 3,035.995 ↓ 0.0 0 1

Nested Loop (cost=2.12..521.21 rows=2 width=331) (actual time=3,035.995..3,035.995 rows=0 loops=1)

18. 18.071 2,696.273 ↓ 42.0 41,633 1

Nested Loop (cost=1.84..178.97 rows=992 width=30) (actual time=8.903..2,696.273 rows=41,633 loops=1)

19. 1.898 107.550 ↓ 37.9 909 1

Hash Join (cost=1.55..38.19 rows=24 width=8) (actual time=4.870..107.550 rows=909 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
20. 102.606 102.606 ↑ 1.0 1,047 1

Seq Scan on diagnostics d (cost=0.00..32.47 rows=1,047 width=18) (actual time=1.085..102.606 rows=1,047 loops=1)

21. 1.166 3.046 ↓ 37.0 37 1

Hash (cost=1.54..1.54 rows=1 width=38) (actual time=3.046..3.046 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 1.880 1.880 ↓ 37.0 37 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.54 rows=1 width=38) (actual time=1.858..1.880 rows=37 loops=1)

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 6
23. 2,570.652 2,570.652 ↑ 1.4 46 909

Index Scan using tests_prescribed_testid_idx on tests_prescribed tp (cost=0.29..5.23 rows=64 width=38) (actual time=0.252..2.828 rows=46 loops=909)

  • Index Cond: ((test_id)::text = (d.test_id)::text)
  • Filter: ((conducted)::text <> 'RAS'::text)
  • Rows Removed by Filter: 0
24. 333.064 333.064 ↓ 0.0 0 41,633

Index Scan using test_visit_reports_pkey on test_visit_reports tv (cost=0.29..0.33 rows=1 width=305) (actual time=0.008..0.008 rows=0 loops=41,633)

  • 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) >= '2019-06-01'::date) AND (date(report_date) <= '2019-07-01'::date))
  • Rows Removed by Filter: 1
25. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..10.30 rows=20 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Seq Scan on test_histopathology_results thr (cost=0.00..10.20 rows=20 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using impression_id_pkey on histo_impression_master him (cost=0.14..2.36 rows=1 width=222) (never executed)

  • Index Cond: (impression_id = thr.impression_id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..0.88 rows=1 width=29) (never executed)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.29..0.57 rows=1 width=33) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
  • Filter: patient_confidentiality_check(COALESCE(patient_group, 0), mr_no)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.14..0.16 rows=1 width=12) (never executed)

  • Index Cond: ((pr.primary_sponsor_id)::text = (tpa_id)::text)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.14..0.16 rows=1 width=45) (never executed)

  • Index Cond: ((incoming_visit_id)::text = (tp.pat_id)::text)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using tests_prescribed_pkey on tests_prescribed itp (cost=0.29..0.40 rows=1 width=13) (never executed)

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

Index Scan using patient_registration_pkey on patient_registration ipr (cost=0.42..0.88 rows=1 width=18) (never executed)

  • Index Cond: ((patient_id)::text = (itp.pat_id)::text)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using tpa_master_pkey on tpa_master itpa (cost=0.14..0.16 rows=1 width=12) (never executed)

  • Index Cond: ((tpa_id)::text = (ipr.primary_sponsor_id)::text)
35. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..2.14 rows=76 width=13) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on diag_outsource_master dom (cost=0.00..1.76 rows=76 width=13) (never executed)

37.          

SubPlan (forGroup)

38. 0.000 0.000 ↓ 0.0 0

Limit (cost=3,726.32..3,726.40 rows=1 width=32) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Subquery Scan on foo (cost=3,726.32..3,726.40 rows=1 width=32) (never executed)

40. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,726.32..3,726.39 rows=1 width=159) (never executed)

  • Group Key: b.bill_no, tvr.report_id, b.visit_id, b.bill_type, b.visit_type, (SubPlan 1), (SubPlan 2)
41. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=3,652.99..3,726.30 rows=1 width=159) (never executed)

  • Join Filter: ((bcn.bill_no)::text = (b.bill_no)::text)
  • Filter: ((SubPlan 4) > '0'::numeric)
42. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.84..58.59 rows=1 width=47) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.42..58.01 rows=1 width=14) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.00..56.47 rows=1 width=13) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..48.16 rows=1 width=13) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using tests_prescribed_report_idx on tests_prescribed tp_1 (cost=0.29..11.03 rows=3 width=8) (never executed)

  • Index Cond: (report_id = tv.report_id)
  • Filter: ((coll_prescribed_id IS NULL) AND (mr_no IS NOT NULL))
47. 0.000 0.000 ↓ 0.0 0

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.42..12.37 rows=1 width=15) (never executed)

  • Index Cond: ((activity_id)::text = ((tp_1.prescribed_id)::character varying)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
48. 0.000 0.000 ↓ 0.0 0

Index Only Scan using test_visit_reports_pkey on test_visit_reports tvr (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: (report_id = tv.report_id)
  • Heap Fetches: 0
49. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_charge_id_idx on bill_charge bc (cost=0.42..1.53 rows=1 width=19) (never executed)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_pkey on bill b (cost=0.42..0.57 rows=1 width=43) (never executed)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: ((visit_type = 'o'::bpchar) AND ((SubPlan 3) > '0'::numeric))
51.          

SubPlan (forIndex Scan)

52. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.02 rows=1 width=0) (never executed)

53. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,651.15..3,654.15 rows=200 width=55) (never executed)

  • Group Key: bcn.bill_no
54. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.42..3,647.32 rows=510 width=55) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on bill_credit_notes bcn (cost=0.00..15.10 rows=510 width=96) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_pkey on bill cn (cost=0.42..7.11 rows=1 width=17) (never executed)

  • Index Cond: ((bcn.credit_note_bill_no)::text = (bill_no)::text)
57.          

SubPlan (forNested Loop Left Join)

58. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.02 rows=1 width=0) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.04 rows=1 width=0) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.04 rows=1 width=0) (never executed)

Planning time : 885.361 ms
Execution time : 3,091.559 ms