explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J5Mv

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 8,721.084 ↓ 3.3 20 1

Limit (cost=1,608,716.95..1,608,723.46 rows=6 width=602) (actual time=8,720.908..8,721.084 rows=20 loops=1)

2. 0.208 8,721.079 ↓ 3.0 21 1

GroupAggregate (cost=1,608,715.87..1,608,723.46 rows=7 width=602) (actual time=8,720.897..8,721.079 rows=21 loops=1)

3. 0.134 8,720.871 ↓ 3.4 24 1

Sort (cost=1,608,715.87..1,608,715.88 rows=7 width=602) (actual time=8,720.869..8,720.871 rows=24 loops=1)

  • Sort Key: foo.sample_date_time, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, foo.sample_date, foo.collection_center, foo.mr_no, foo.patient_id, foo.sample_qty, foo.ih_name, foo.sample_status, foo.assertion_time, foo.rejected_time, foo.conducted, foo.sample_type_id, foo.collection_center_id, foo.visit_type, foo.center_id, foo.sample_collection_id, foo.patient_name, foo.outsource_dest_id, foo.outsource_name, foo.orig_sample_no, foo.transfer_time, foo.transfer_other_details, foo.receipt_time, foo.receipt_other_details, foo.bill_status, foo.charge_head
  • Sort Method: quicksort Memory: 45kB
4. 0.008 8,720.737 ↓ 8.7 61 1

Subquery Scan on foo (cost=1,608,712.09..1,608,715.77 rows=7 width=602) (actual time=8,720.713..8,720.737 rows=61 loops=1)

5. 0.228 8,720.729 ↓ 8.7 61 1

HashAggregate (cost=1,608,712.09..1,608,715.70 rows=7 width=366) (actual time=8,720.712..8,720.729 rows=61 loops=1)

6. 4.777 8,720.501 ↓ 9.6 67 1

Nested Loop (cost=1,034,508.20..1,608,711.45 rows=7 width=366) (actual time=7,405.114..8,720.501 rows=67 loops=1)

7. 0.049 8,715.456 ↓ 9.6 67 1

Nested Loop (cost=1,034,507.93..1,608,699.24 rows=7 width=351) (actual time=7,404.975..8,715.456 rows=67 loops=1)

8. 0.052 8,714.804 ↓ 9.6 67 1

Nested Loop (cost=1,034,507.37..1,608,688.02 rows=7 width=363) (actual time=7,404.959..8,714.804 rows=67 loops=1)

9. 0.073 8,714.149 ↓ 9.6 67 1

Nested Loop (cost=1,034,506.81..1,608,672.72 rows=7 width=353) (actual time=7,404.945..8,714.149 rows=67 loops=1)

10. 0.072 8,713.540 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,506.36..1,608,655.16 rows=7 width=351) (actual time=7,404.930..8,713.540 rows=67 loops=1)

11. 0.078 8,713.468 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,505.93..1,608,645.80 rows=7 width=346) (actual time=7,404.927..8,713.468 rows=67 loops=1)

12. 0.091 8,713.390 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,505.50..1,608,630.20 rows=7 width=346) (actual time=7,404.925..8,713.390 rows=67 loops=1)

  • Join Filter: (scc.collection_center_id = pr.collection_center_id)
13. 0.068 8,713.299 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,505.50..1,608,629.08 rows=7 width=337) (actual time=7,404.919..8,713.299 rows=67 loops=1)

14. 0.054 8,713.231 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,505.36..1,608,627.92 rows=7 width=334) (actual time=7,404.917..8,713.231 rows=67 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.086 8,712.976 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,504.94..1,608,618.93 rows=7 width=317) (actual time=7,404.909..8,712.976 rows=67 loops=1)

16. 0.041 8,712.756 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,504.80..1,608,617.78 rows=7 width=321) (actual time=7,404.902..8,712.756 rows=67 loops=1)

17. 0.116 8,711.978 ↓ 9.6 67 1

Nested Loop (cost=1,034,504.37..1,608,608.12 rows=7 width=288) (actual time=7,404.885..8,711.978 rows=67 loops=1)

18. 0.216 8,711.728 ↓ 9.6 67 1

Nested Loop Left Join (cost=1,034,504.24..1,608,606.92 rows=7 width=275) (actual time=7,404.877..8,711.728 rows=67 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 324
19. 0.521 8,710.730 ↑ 3.4 391 1

Nested Loop Left Join (cost=1,034,503.82..1,606,789.96 rows=1,325 width=228) (actual time=7,404.497..8,710.730 rows=391 loops=1)

20. 0.103 8,707.472 ↑ 3.4 391 1

Hash Left Join (cost=1,034,503.38..1,600,567.54 rows=1,325 width=187) (actual time=7,404.471..8,707.472 rows=391 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
21. 0.156 8,707.351 ↑ 3.4 391 1

Hash Left Join (cost=1,034,498.87..1,600,550.19 rows=1,325 width=184) (actual time=7,404.449..8,707.351 rows=391 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
22. 0.387 8,707.175 ↑ 3.4 391 1

Hash Join (cost=1,034,496.54..1,600,531.22 rows=1,325 width=171) (actual time=7,404.417..8,707.175 rows=391 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
23. 720.865 8,705.697 ↑ 3.4 391 1

Hash Left Join (cost=1,033,946.08..1,599,960.89 rows=1,325 width=138) (actual time=7,403.319..8,705.697 rows=391 loops=1)

  • Hash Cond: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END = tpr.prescribed_id) AND ((sc.mr_no)::text = (tpr.mr_no)::text))
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 75
24. 581.719 4,820.471 ↑ 284.3 466 1

Hash Left Join (cost=592,547.28..1,096,865.44 rows=132,504 width=144) (actual time=4,238.793..4,820.471 rows=466 loops=1)

  • Hash Cond: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END = tp.sample_collection_id) AND ((sc.mr_no)::text = (tp.mr_no)::text))
25. 2.279 633.685 ↑ 515.6 257 1

Hash Right Join (cost=143,773.48..144,500.92 rows=132,504 width=129) (actual time=633.574..633.685 rows=257 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
26. 1.311 1.311 ↑ 1.0 25,665 1

Seq Scan on sample_rejections sr (cost=0.00..395.65 rows=25,665 width=8) (actual time=0.004..1.311 rows=25,665 loops=1)

27. 0.135 630.095 ↑ 515.6 257 1

Hash (cost=142,117.18..142,117.18 rows=132,504 width=125) (actual time=630.095..630.095 rows=257 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 31kB
28. 0.925 629.960 ↑ 515.6 257 1

Hash Left Join (cost=2.51..142,117.18 rows=132,504 width=125) (actual time=557.741..629.960 rows=257 loops=1)

  • Hash Cond: (sc.outsource_dest_id = dom.outsource_dest_id)
  • Filter: ((dom.outsource_dest_type IS NULL) OR (dom.outsource_dest_type <> 'C'::bpchar))
  • Rows Removed by Filter: 3891
29. 629.013 629.013 ↑ 36.3 4,148 1

Seq Scan on sample_collection sc (cost=0.00..141,225.46 rows=150,471 width=116) (actual time=387.063..629.013 rows=4,148 loops=1)

  • Filter: ((sample_status = 'C'::bpchar) AND (sample_receive_status = 'R'::bpchar) AND (sample_status = ANY ('{C,A,R}'::bpchar[])) AND ((sample_date)::date >= '2018-12-29'::date))
  • Rows Removed by Filter: 3095660
30. 0.012 0.022 ↑ 1.0 67 1

Hash (cost=1.67..1.67 rows=67 width=15) (actual time=0.022..0.022 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
31. 0.010 0.010 ↑ 1.0 67 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.67 rows=67 width=15) (actual time=0.004..0.010 rows=67 loops=1)

32. 1,542.886 3,605.067 ↑ 1.2 6,466,203 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=34) (actual time=3,605.067..3,605.067 rows=6,466,203 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 26988kB
33. 2,062.181 2,062.181 ↓ 1.0 7,554,345 1

Seq Scan on tests_prescribed tp (cost=0.00..276,500.72 rows=7,551,672 width=34) (actual time=0.004..2,062.181 rows=7,554,345 loops=1)

34. 1,713.868 3,164.361 ↓ 1.0 7,553,706 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=30) (actual time=3,164.361..3,164.361 rows=7,553,706 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 30511kB
35. 1,450.493 1,450.493 ↓ 1.0 7,554,345 1

Seq Scan on tests_prescribed tpr (cost=0.00..276,500.72 rows=7,551,672 width=30) (actual time=0.003..1,450.493 rows=7,554,345 loops=1)

36. 0.310 1.091 ↑ 1.0 1,798 1

Hash (cost=527.98..527.98 rows=1,798 width=49) (actual time=1.091..1.091 rows=1,798 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
37. 0.781 0.781 ↑ 1.0 1,798 1

Seq Scan on diagnostics d (cost=0.00..527.98 rows=1,798 width=49) (actual time=0.004..0.781 rows=1,798 loops=1)

38. 0.009 0.020 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=24) (actual time=0.020..0.020 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
39. 0.011 0.011 ↑ 1.0 59 1

Seq Scan on outhouse_master om (cost=0.00..1.59 rows=59 width=24) (actual time=0.003..0.011 rows=59 loops=1)

40. 0.008 0.018 ↑ 1.0 23 1

Hash (cost=4.23..4.23 rows=23 width=16) (actual time=0.018..0.018 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
41. 0.010 0.010 ↑ 1.0 23 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.23 rows=23 width=16) (actual time=0.002..0.010 rows=23 loops=1)

42. 2.737 2.737 ↑ 1.0 1 391

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..4.69 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=391)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
43. 0.782 0.782 ↓ 0.0 0 391

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..1.36 rows=1 width=62) (actual time=0.002..0.002 rows=0 loops=391)

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
44. 0.134 0.134 ↑ 1.0 1 67

Index Scan using diagnostics_departments_pkey on diagnostics_departments (cost=0.14..0.16 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=67)

  • Index Cond: ((ddept_id)::text = (d.ddept_id)::text)
45. 0.737 0.737 ↑ 1.0 1 67

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.37 rows=1 width=48) (actual time=0.010..0.011 rows=1 loops=67)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
46. 0.134 0.134 ↑ 1.0 1 67

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=67)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
47. 0.201 0.201 ↓ 0.0 0 67

Index Scan using incoming_sample_registration_details_pkey on incoming_sample_registration_details isrd (cost=0.42..1.27 rows=1 width=30) (actual time=0.003..0.003 rows=0 loops=67)

  • Index Cond: (tp.prescribed_id = prescribed_id)
48. 0.000 0.000 ↓ 0.0 0 67

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.14..0.16 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=67)

  • Index Cond: ((hospital_id)::text = (isr.orig_lab_name)::text)
49. 0.000 0.000 ↑ 1.0 1 67

Materialize (cost=0.00..1.01 rows=1 width=13) (actual time=0.000..0.000 rows=1 loops=67)

50. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on sample_collection_centers scc (cost=0.00..1.01 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=1)

51. 0.000 0.000 ↓ 0.0 0 67

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..2.22 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=67)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
52. 0.000 0.000 ↓ 0.0 0 67

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.43..1.33 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=67)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
53. 0.536 0.536 ↑ 1.0 1 67

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.45..2.50 rows=1 width=17) (actual time=0.008..0.008 rows=1 loops=67)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
54. 0.603 0.603 ↑ 1.0 1 67

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..2.18 rows=1 width=30) (actual time=0.009..0.009 rows=1 loops=67)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
55. 0.603 0.603 ↑ 1.0 1 67

Index Scan using bill_pkey on bill b (cost=0.56..1.59 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=67)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
56. 0.268 0.268 ↑ 1.0 1 67

Index Scan using sample_type_pkey on sample_type st (cost=0.27..1.23 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=67)

  • Index Cond: (sample_type_id = sc.sample_type_id)