explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9fte

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 667.993 ↓ 0.0 0 1

GroupAggregate (cost=280,046.20..280,047.29 rows=1 width=602) (actual time=667.993..667.993 rows=0 loops=1)

2. 0.026 667.993 ↓ 0.0 0 1

Sort (cost=280,046.20..280,046.20 rows=1 width=602) (actual time=667.993..667.993 rows=0 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.sa
  • Sort Method: quicksort Memory: 25kB
3. 0.001 667.967 ↓ 0.0 0 1

Subquery Scan on foo (cost=280,045.66..280,046.19 rows=1 width=602) (actual time=667.967..667.967 rows=0 loops=1)

4. 0.002 667.966 ↓ 0.0 0 1

HashAggregate (cost=280,045.66..280,046.18 rows=1 width=366) (actual time=667.966..667.966 rows=0 loops=1)

5. 0.000 667.964 ↓ 0.0 0 1

Nested Loop (cost=116,792.97..280,045.57 rows=1 width=366) (actual time=667.964..667.964 rows=0 loops=1)

6. 0.001 667.964 ↓ 0.0 0 1

Nested Loop (cost=116,792.70..280,042.30 rows=1 width=351) (actual time=667.964..667.964 rows=0 loops=1)

7. 0.000 667.963 ↓ 0.0 0 1

Nested Loop (cost=116,792.27..280,039.63 rows=1 width=363) (actual time=667.963..667.963 rows=0 loops=1)

8. 0.001 667.963 ↓ 0.0 0 1

Nested Loop (cost=116,791.71..280,036.62 rows=1 width=353) (actual time=667.963..667.963 rows=0 loops=1)

9. 0.001 667.962 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,791.26..280,031.72 rows=1 width=351) (actual time=667.962..667.962 rows=0 loops=1)

10. 0.000 667.961 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,790.83..280,029.10 rows=1 width=346) (actual time=667.961..667.961 rows=0 loops=1)

11. 0.000 667.961 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,790.40..280,026.19 rows=1 width=346) (actual time=667.961..667.961 rows=0 loops=1)

  • Join Filter: (scc.collection_center_id = pr.collection_center_id)
12. 0.000 667.961 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,790.40..280,025.16 rows=1 width=337) (actual time=667.961..667.961 rows=0 loops=1)

13. 0.001 667.961 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,790.26..280,025.00 rows=1 width=334) (actual time=667.961..667.961 rows=0 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
14. 0.000 667.960 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,789.84..280,022.40 rows=1 width=317) (actual time=667.960..667.960 rows=0 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
15. 0.000 667.960 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,789.42..280,019.78 rows=1 width=270) (actual time=667.960..667.960 rows=0 loops=1)

16. 0.002 667.960 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,789.28..280,019.62 rows=1 width=274) (actual time=667.960..667.960 rows=0 loops=1)

  • -> Nested Loop Left Join (cost=116788.85..280016.95 rows=1 width=241) (actual time=667.960..667.960 rows=0 lo
17. 0.000 667.958 ↓ 0.0 0 1

Nested Loop (cost=116,788.42..280,012.86 rows=1 width=200) (actual time=667.958..667.958 rows=0 loops=1)

18. 0.000 667.958 ↓ 0.0 0 1

Hash Left Join (cost=116,788.28..280,012.69 rows=1 width=187) (actual time=667.958..667.958 rows=0 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
19. 0.001 667.958 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,783.77..280,008.15 rows=1 width=184) (actual time=667.958..667.958 rows=0 loops=1)

20. 0.000 667.957 ↓ 0.0 0 1

Nested Loop (cost=116,783.63..280,007.85 rows=1 width=171) (actual time=667.957..667.957 rows=0 loops=1)

21. 0.001 667.957 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,783.35..280,005.43 rows=1 width=138) (actual time=667.957..667.957 rows=0 loops=1)

  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
22. 0.001 667.956 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,782.91..279,997.46 rows=1 width=129) (actual time=667.956..667.956 rows=0 loops=1)

23. 0.000 667.955 ↓ 0.0 0 1

Nested Loop Left Join (cost=116,782.48..279,991.97 rows=1 width=114) (actual time=667.955..667.955 rows=0 loops=1)

24. 0.061 667.955 ↓ 0.0 0 1

Hash Join (cost=116,782.19..279,989.53 rows=1 width=110) (actual time=667.955..667.955 rows=0 loops=1)

  • Hash Cond: (((tp1_1.sample_no)::text = (sc.sample_sno)::text) AND ((tp1_1.mr_no)::text = (sc.mr_no)::text))
25. 17.080 54.508 ↑ 315,111.0 1 1

Bitmap Heap Scan on tests_prescribed tp1_1 (cost=6,759.98..164,452.87 rows=315,111 width=28) (actual time=54.508..54.508 rows=1 loops=1)

  • Recheck Cond: ((conducted)::text = ANY ('{N,NRN}'::text[]))
26. 37.428 37.428 ↑ 1.0 301,551 1

Bitmap Index Scan on tests_prescribed_conducted_idx (cost=0.00..6,681.20 rows=315,111 width=0) (actual time=37.428..37.428 rows=301,551 loops=1)

  • Index Cond: ((conducted)::text = ANY ('{N,NRN}'::text[]))
27. 0.001 613.386 ↓ 0.0 0 1

Hash (cost=108,730.41..108,730.41 rows=86,120 width=125) (actual time=613.386..613.386 rows=0 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 0kB
  • Hash Cond: (sc.outsource_dest_id = dom.outsource_dest_id)
28. 0.037 613.385 ↓ 0.0 0 1

Hash Left Join (cost=2.46..108,730.41 rows=86,120 width=125) (actual time=613.385..613.385 rows=0 loops=1)

  • Filter: ((dom.outsource_dest_type IS NULL) OR (dom.outsource_dest_type <> 'C'::bpchar))
  • Rows Removed by Filter: 3
29. 613.326 613.326 ↑ 32,735.7 3 1

Seq Scan on sample_collection sc (cost=0.00..108,149.82 rows=98,207 width=116) (actual time=496.658..613.326 rows=3 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: 2368600
30. 0.012 0.022 ↑ 1.0 65 1

Hash (cost=1.65..1.65 rows=65 width=15) (actual time=0.022..0.022 rows=65 loops=1)

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

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

32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_sample_rejections_sample_collection_id on sample_rejections sr (cost=0.29..2.42 rows=2 width=8) (never executed)

  • Index Cond: (sc.sample_collection_id = sample_collection_id)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using tests_prescribed_pkey on tests_prescribed tpr (cost=0.43..5.48 rows=1 width=15) (never executed)

  • Index Cond: (prescribed_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_tests_prescribed_sample_collection_id on tests_prescribed tp (cost=0.43..7.54 rows=29 width=19) (never executed)

  • Index Cond: (sample_collection_id = CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_pkey on diagnostics d (cost=0.28..2.40 rows=1 width=49) (never executed)

  • Index Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (test_id)::text)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using outhouse_master_pkey on outhouse_master om (cost=0.14..0.30 rows=1 width=24) (never executed)

  • Index Cond: ((oh_id)::text = (dom.outsource_dest)::text)
37. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.23..4.23 rows=23 width=16) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on hospital_center_master hcm (cost=0.00..4.23 rows=23 width=16) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_departments_pkey on diagnostics_departments (cost=0.14..0.16 rows=1 width=23) (never executed)

  • Index Cond: ((ddept_id)::text = (d.ddept_id)::text)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..4.09 rows=1 width=41) (never executed)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..2.65 rows=1 width=48) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=14) (never executed)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..2.61 rows=1 width=62) (never executed)

  • Index Cond: ((sc.patient_id)::text = (incoming_visit_id)::text)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using incoming_sample_registration_details_pkey on incoming_sample_registration_details isrd (cost=0.42..2.58 rows=1 width=30) (never executed)

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

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.14..0.16 rows=1 width=17) (never executed)

  • Index Cond: ((hospital_id)::text = (isr.orig_lab_name)::text)
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on sample_collection_centers scc (cost=0.00..1.01 rows=1 width=13) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..2.90 rows=1 width=8) (never executed)

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

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.43..2.61 rows=1 width=13) (never executed)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.45..4.89 rows=1 width=17) (never executed)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..3.01 rows=1 width=30) (never executed)

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

Index Scan using bill_pkey on bill b (cost=0.43..2.66 rows=1 width=16) (never executed)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
52. 0.000 0.000 ↓ 0.0 0

Index Scan using sample_type_pkey on sample_type st (cost=0.27..2.76 rows=1 width=19) (never executed)

  • Index Cond: (sample_type_id = sc.sample_type_id)