explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KZWh

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 11,650.442 ↓ 3.3 20 1

Limit (cost=1,849,477.03..1,849,483.54 rows=6 width=602) (actual time=11,650.209..11,650.442 rows=20 loops=1)

2. 0.735 11,650.440 ↓ 3.0 21 1

GroupAggregate (cost=1,849,475.95..1,849,483.54 rows=7 width=602) (actual time=11,650.199..11,650.440 rows=21 loops=1)

3. 0.235 11,649.705 ↓ 3.4 24 1

Sort (cost=1,849,475.95..1,849,475.97 rows=7 width=602) (actual time=11,649.703..11,649.705 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.009 11,649.470 ↓ 8.7 61 1

Subquery Scan on foo (cost=1,849,472.18..1,849,475.85 rows=7 width=602) (actual time=11,649.443..11,649.470 rows=61 loops=1)

5. 0.277 11,649.461 ↓ 8.7 61 1

HashAggregate (cost=1,849,472.18..1,849,475.78 rows=7 width=366) (actual time=11,649.442..11,649.461 rows=61 loops=1)

6. 12.499 11,649.184 ↓ 9.6 67 1

Nested Loop (cost=980,791.33..1,849,471.53 rows=7 width=366) (actual time=9,719.671..11,649.184 rows=67 loops=1)

7. 0.093 11,636.417 ↓ 9.6 67 1

Nested Loop (cost=980,791.07..1,849,459.32 rows=7 width=351) (actual time=9,712.563..11,636.417 rows=67 loops=1)

8. 0.082 11,635.654 ↓ 9.6 67 1

Nested Loop (cost=980,790.51..1,849,448.10 rows=7 width=363) (actual time=9,712.537..11,635.654 rows=67 loops=1)

9. 0.115 11,634.902 ↓ 9.6 67 1

Nested Loop (cost=980,789.95..1,849,432.80 rows=7 width=353) (actual time=9,712.505..11,634.902 rows=67 loops=1)

10. 0.087 11,634.184 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,789.50..1,849,415.25 rows=7 width=351) (actual time=9,712.486..11,634.184 rows=67 loops=1)

11. 0.072 11,634.097 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,789.07..1,849,405.88 rows=7 width=346) (actual time=9,712.482..11,634.097 rows=67 loops=1)

12. 0.039 11,634.025 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,788.64..1,849,390.28 rows=7 width=346) (actual time=9,712.479..11,634.025 rows=67 loops=1)

  • Join Filter: (scc.collection_center_id = pr.collection_center_id)
13. 0.069 11,633.919 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,788.64..1,849,389.16 rows=7 width=337) (actual time=9,712.470..11,633.919 rows=67 loops=1)

14. 0.094 11,633.850 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,788.50..1,849,388.01 rows=7 width=334) (actual time=9,712.467..11,633.850 rows=67 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.066 11,633.555 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,788.08..1,849,379.01 rows=7 width=317) (actual time=9,712.458..11,633.555 rows=67 loops=1)

16. 0.077 11,633.288 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,787.94..1,849,377.86 rows=7 width=321) (actual time=9,712.450..11,633.288 rows=67 loops=1)

17. 0.062 11,632.407 ↓ 9.6 67 1

Nested Loop (cost=980,787.51..1,849,368.20 rows=7 width=288) (actual time=9,712.418..11,632.407 rows=67 loops=1)

18. 0.310 11,632.144 ↓ 9.6 67 1

Nested Loop Left Join (cost=980,787.37..1,849,367.00 rows=7 width=275) (actual time=9,712.405..11,632.144 rows=67 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 327
19. 0.494 11,630.652 ↑ 3.4 394 1

Nested Loop Left Join (cost=980,786.95..1,847,551.39 rows=1,324 width=228) (actual time=9,712.391..11,630.652 rows=394 loops=1)

20. 0.129 11,627.006 ↑ 3.4 394 1

Hash Left Join (cost=980,786.52..1,841,333.13 rows=1,324 width=187) (actual time=9,712.345..11,627.006 rows=394 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
21. 0.163 11,626.839 ↑ 3.4 394 1

Hash Left Join (cost=980,782.00..1,841,315.79 rows=1,324 width=184) (actual time=9,712.299..11,626.839 rows=394 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
22. 0.399 11,626.655 ↑ 3.4 394 1

Hash Join (cost=980,779.68..1,841,296.83 rows=1,324 width=171) (actual time=9,712.263..11,626.655 rows=394 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
23. 1,076.046 11,622.907 ↑ 3.4 394 1

Hash Left Join (cost=980,229.22..1,840,726.52 rows=1,324 width=138) (actual time=9,708.900..11,622.907 rows=394 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 ("substring"((sc.patient_id)::text, 5, 2) = "substring"((tpr.pat_id)::text, 5, 2)))
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 72
24. 838.974 6,230.459 ↑ 284.3 466 1

Hash Left Join (cost=538,830.42..1,335,233.05 rows=132,468 width=129) (actual time=5,392.009..6,230.459 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 ("substring"((sc.patient_id)::text, 5, 2) = "substring"((tp.pat_id)::text, 5, 2)))
25. 2.583 514.753 ↑ 515.4 257 1

Hash Right Join (cost=90,056.62..90,784.05 rows=132,468 width=114) (actual time=514.633..514.753 rows=257 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
26. 1.426 1.426 ↑ 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.426 rows=25,665 loops=1)

27. 0.108 510.744 ↑ 515.4 257 1

Hash (cost=88,400.77..88,400.77 rows=132,468 width=110) (actual time=510.744..510.744 rows=257 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 27kB
28. 0.920 510.636 ↑ 515.4 257 1

Hash Left Join (cost=9,192.94..88,400.77 rows=132,468 width=110) (actual time=475.835..510.636 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. 384.320 509.687 ↑ 36.3 4,148 1

Bitmap Heap Scan on sample_collection sc (cost=9,190.43..87,509.29 rows=150,430 width=101) (actual time=387.202..509.687 rows=4,148 loops=1)

  • Recheck Cond: ((sample_status = ANY ('{C,A,R}'::bpchar[])) AND (sample_status = 'C'::bpchar))
  • Filter: ((sample_receive_status = 'R'::bpchar) AND ((sample_date)::date >= '2018-12-29'::date))
  • Rows Removed by Filter: 442422
30. 125.367 125.367 ↑ 1.0 446,570 1

Bitmap Index Scan on sample_collection_sample_status_idx (cost=0.00..9,152.82 rows=451,952 width=0) (actual time=125.367..125.367 rows=446,570 loops=1)

  • Index Cond: ((sample_status = ANY ('{C,A,R}'::bpchar[])) AND (sample_status = 'C'::bpchar))
31. 0.012 0.029 ↑ 1.0 67 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
32. 0.017 0.017 ↑ 1.0 67 1

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

33. 2,459.926 4,876.732 ↑ 1.2 6,466,842 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=34) (actual time=4,876.732..4,876.732 rows=6,466,842 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 26821kB
34. 2,416.806 2,416.806 ↓ 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.022..2,416.806 rows=7,554,345 loops=1)

35. 2,721.803 4,316.402 ↓ 1.0 7,554,345 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=30) (actual time=4,316.402..4,316.402 rows=7,554,345 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 30135kB
36. 1,594.599 1,594.599 ↓ 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.005..1,594.599 rows=7,554,345 loops=1)

37. 0.677 3.349 ↑ 1.0 1,798 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
38. 2.672 2.672 ↑ 1.0 1,798 1

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

39. 0.014 0.021 ↑ 1.0 59 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
40. 0.007 0.007 ↑ 1.0 59 1

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

41. 0.022 0.038 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
42. 0.016 0.016 ↑ 1.0 23 1

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

43. 3.152 3.152 ↑ 1.0 1 394

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

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
44. 1.182 1.182 ↓ 0.0 0 394

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

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
45. 0.201 0.201 ↑ 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.003 rows=1 loops=67)

  • Index Cond: ((ddept_id)::text = (d.ddept_id)::text)
46. 0.804 0.804 ↑ 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.012..0.012 rows=1 loops=67)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
47. 0.201 0.201 ↑ 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.003..0.003 rows=1 loops=67)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
48. 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)
49. 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)
50. 0.063 0.067 ↑ 1.0 1 67

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

51. 0.004 0.004 ↑ 1.0 1 1

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

52. 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)
53. 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)
54. 0.603 0.603 ↑ 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.009..0.009 rows=1 loops=67)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
55. 0.670 0.670 ↑ 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.010..0.010 rows=1 loops=67)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
56. 0.670 0.670 ↑ 1.0 1 67

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

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
57. 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)