explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4iTC

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7,736.379 ↑ 1.0 20 1

Limit (cost=1,703,211.72..1,703,233.42 rows=20 width=602) (actual time=7,736.232..7,736.379 rows=20 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)
  • Buckets: 131072 Batches: 8 Memory Usage: 46133kB
2. 0.185 7,736.377 ↑ 13.2 21 1

GroupAggregate (cost=1,703,210.64..1,703,512.27 rows=278 width=602) (actual time=7,736.221..7,736.377 rows=21 loops=1)

3. 0.401 7,736.192 ↑ 12.6 22 1

Sort (cost=1,703,210.64..1,703,211.33 rows=278 width=602) (actual time=7,736.191..7,736.192 rows=22 loops=1)

  • Sort Key: foo.sample_date_time, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, foo.sample_date, foo.collection_center
  • Sort Method: quicksort Memory: 96kB
4. 0.049 7,735.791 ↑ 1.6 174 1

Subquery Scan on foo (cost=923,028.04..1,703,199.35 rows=278 width=602) (actual time=6,703.551..7,735.791 rows=174 loops=1)

5. 8.939 7,735.742 ↑ 1.6 174 1

Hash Join (cost=923,028.04..1,703,196.57 rows=278 width=366) (actual time=6,703.551..7,735.742 rows=174 loops=1)

  • Hash Cond: (sc.sample_type_id = st.sample_type_id)
6. 1.076 7,726.673 ↑ 1.6 174 1

Nested Loop (cost=922,926.94..1,702,951.26 rows=278 width=351) (actual time=6,703.194..7,726.673 rows=174 loops=1)

  • -> Nested Loop (cost=922926.38..1702505.69 rows=278 width=363) (actual time=6703.175..7724.970 rows=174 loops=1
  • -> Nested Loop Left Join (cost=922925.82..1701898.18 rows=278 width=353) (actual time=6703.157..7723.232
  • -> Nested Loop Left Join (cost=922925.39..1701526.13 rows=278 width=348) (actual time=6703.153..772
  • -> Hash Left Join (cost=922924.96..1700906.59 rows=278 width=348) (actual time=6703.150..7722
  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
  • -> Nested Loop (cost=922923.94..1700901.75 rows=278 width=339) (actual time=6703.138..7
  • -> Hash Left Join (cost=922923.49..1700155.90 rows=298 width=337) (actual time=67
  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
  • -> Nested Loop Left Join (cost=922922.09..1700150.40 rows=298 width=334) (a
  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
7. 0.158 7,720.538 ↑ 1.7 174 1

Hash Left Join (cost=922,921.66..1,699,767.63 rows=298 width=317) (actual time=6,703.092..7,720.538 rows=174 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.011..:
8. 0.026 7,720.380 ↑ 1.7 174 1

Nested Loop Left Join (cost=922,920.19..1,699,762.06 rows=298 width=321) (actual time=6,703.067..7,720.380 rows=174 loops=1)

9. 0.125 7,718.608 ↑ 1.7 174 1

Hash Join (cost=922,919.76..1,699,351.02 rows=298 width=288) (actual time=6,703.044..7,718.608 rows=174 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
10. 1.643 7,718.474 ↑ 1.7 174 1

Hash Left Join (cost=922,918.49..1,699,345.66 rows=298 width=275) (actual time=6,703.025..7,718.474 rows=174 loops=1)

  • Hash Cond: ((sc.patient_id)::text = (isr.incoming_visit_id)::text)
  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 1379
11. 0.671 7,553.159 ↑ 38.4 1,553 1

Nested Loop Left Join (cost=913,532.08..1,688,997.54 rows=59,572 width=228) (actual time=6,539.111..7,553.159 rows=1,553 loops=1)

12. 0.937 7,541.617 ↑ 38.4 1,553 1

Hash Join (cost=913,531.65..1,409,213.16 rows=59,572 width=187) (actual time=6,539.079..7,541.617 rows=1,553 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
13. 464.158 7,539.261 ↑ 38.4 1,553 1

Hash Left Join (cost=912,981.20..1,407,769.13 rows=59,572 width=154) (actual time=6,537.651..7,539.261 rows=1,553 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)
  • Join Filter: (sc.sample_status <> 'R'::bpchar)
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 562
14. 537.396 3,688.304 ↑ 108.8 1,218 1

Hash Left Join (cost=497,835.58..545,249.39 rows=132,468 width=145) (actual time=3,150.698..3,688.304 rows=1,218 loops=1)

  • Join Filter: (sc.sample_status = 'R'::bpchar)
  • Rows Removed by Join Filter: 1044
15. 2.669 295.250 ↑ 108.8 1,218 1

Hash Right Join (cost=90,064.96..90,792.39 rows=132,468 width=130) (actual time=294.850..295.250 rows=1,218 loops=1)

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

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

17. 0.428 291.331 ↑ 108.8 1,218 1

Hash (cost=88,409.11..88,409.11 rows=132,468 width=126) (actual time=291.331..291.331 rows=1,218 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 137kB
18. 5.432 290.903 ↑ 108.8 1,218 1

Hash Left Join (cost=9,201.27..88,409.11 rows=132,468 width=126) (actual time=184.297..290.903 rows=1,218 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: 27806
19. 251.866 285.370 ↑ 5.2 29,024 1

Bitmap Heap Scan on sample_collection sc (cost=9,190.43..87,509.29 rows=150,430 width=101) (actual time=184.175..285.370 rows=29,024 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-11-29'::date))
  • Rows Removed by Filter: 417546
20. 33.504 33.504 ↑ 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=33.504..33.504 rows=446,570 loops=1)

21. 0.007 0.101 ↑ 1.0 67 1

Hash (cost=10.00..10.00 rows=67 width=31) (actual time=0.101..0.101 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
22. 0.014 0.094 ↑ 1.0 67 1

Hash Left Join (cost=6.84..10.00 rows=67 width=31) (actual time=0.060..0.094 rows=67 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
23. 0.025 0.062 ↑ 1.0 67 1

Hash Left Join (cost=2.33..4.84 rows=67 width=28) (actual time=0.037..0.062 rows=67 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
24. 0.013 0.013 ↑ 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.013 rows=67 loops=1)

25. 0.014 0.024 ↑ 1.0 59 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
26. 0.010 0.010 ↑ 1.0 59 1

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

27. 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
28. 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)

29. 1,348.411 2,855.658 ↓ 1.0 7,554,345 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=15) (actual time=2,855.658..2,855.658 rows=7,554,345 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 46133kB
  • -> Seq Scan on t:
30. 1,507.247 1,507.247 ↓ 1.0 7,554,345 1

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

31. 1,253.354 3,386.799 ↑ 1.2 6,466,842 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=19) (actual time=3,386.799..3,386.799 rows=6,466,842 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 20593kB
32. 2,133.445 2,133.445 ↓ 1.0 7,554,345 1

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

33. 0.589 1.419 ↑ 1.0 1,798 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
34. 0.830 0.830 ↑ 1.0 1,798 1

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

35. 10.871 10.871 ↑ 1.0 1 1,553

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=1,553)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
36. 91.917 163.672 ↑ 1.0 225,307 1

Hash (cost=6,570.07..6,570.07 rows=225,307 width=62) (actual time=163.672..163.672 rows=225,307 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 21046kB
37. 71.755 71.755 ↑ 1.0 225,307 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6,570.07 rows=225,307 width=62) (actual time=0.003..71.755 rows=225,307 loops=1)

38. 0.003 0.009 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=23) (actual time=0.009..0.009 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
39. 0.006 0.006 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.12 rows=12 width=23) (actual time=0.002..0.006 rows=12 loops=1)

40. 1.740 1.740 ↑ 1.0 1 174

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

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
41. 0.006 0.006 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.004..0.006 rows=21 loops=1)

42. 0.522 0.522 ↓ 0.0 0 174

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=174)

  • Index Cond: (tp.prescribed_id = prescribed_id)
43. 0.002 0.009 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=17) (actual time=0.009..0.009 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
44. 0.007 0.007 ↑ 1.0 18 1

Seq Scan on incoming_hospitals ih (cost=0.00..1.18 rows=18 width=17) (actual time=0.003..0.007 rows=18 loops=1)

45. 1.392 1.392 ↑ 1.0 1 174

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.44..2.49 rows=1 width=17) (actual time=0.008..0.008 rows=1 loops=174)

  • Index Cond: ((COALESCE(tp.prescribed_id, tpr.prescribed_id))::text = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
46. 0.002 0.004 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
47. 0.002 0.002 ↑ 1.0 1 1

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

48. 0.000 0.000 ↓ 0.0 0 174

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=174)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
49. 0.000 0.000 ↓ 0.0 0 174

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=174)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
50. 1.566 1.566 ↑ 1.0 1 174

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=174)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
51. 1.566 1.566 ↑ 1.0 1 174

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

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
52. 0.018 0.130 ↑ 1.0 49 1

Hash (cost=100.49..100.49 rows=49 width=19) (actual time=0.130..0.130 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
53. 0.112 0.112 ↑ 1.0 49 1

Seq Scan on sample_type st (cost=0.00..100.49 rows=49 width=19) (actual time=0.004..0.112 rows=49 loops=1)