explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Raja

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 87.787 ↓ 0.0 0 1

GroupAggregate (cost=216,704.31..216,707.56 rows=3 width=1,132) (actual time=87.787..87.787 rows=0 loops=1)

  • Group 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_
2. 0.018 87.786 ↓ 0.0 0 1

Sort (cost=216,704.31..216,704.32 rows=3 width=1,365) (actual time=87.786..87.786 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.as
  • Sort Method: quicksort Memory: 25kB
3. 0.001 87.768 ↓ 0.0 0 1

Subquery Scan on foo (cost=216,703.98..216,704.28 rows=3 width=1,365) (actual time=87.768..87.768 rows=0 loops=1)

4. 0.000 87.767 ↓ 0.0 0 1

Unique (cost=216,703.98..216,704.25 rows=3 width=1,425) (actual time=87.767..87.767 rows=0 loops=1)

5. 0.014 87.767 ↓ 0.0 0 1

Sort (cost=216,703.98..216,703.99 rows=3 width=1,425) (actual time=87.767..87.767 rows=0 loops=1)

  • Sort Key: st.sample_type, sc.sample_sno, sc.sample_assertion_batch, ((sc.sample_date)::date), scc.collection_center, sc.sample_collection_id, sc1.transfer_time, sc1.transfer_oth
  • Sort Method: quicksort Memory: 25kB
6. 0.000 87.753 ↓ 0.0 0 1

Nested Loop (cost=179,772.59..216,703.96 rows=3 width=1,425) (actual time=87.753..87.753 rows=0 loops=1)

7. 0.000 87.753 ↓ 0.0 0 1

Nested Loop (cost=179,772.45..216,701.94 rows=3 width=1,289) (actual time=87.753..87.753 rows=0 loops=1)

8. 0.000 87.753 ↓ 0.0 0 1

Nested Loop (cost=179,772.02..216,700.05 rows=3 width=1,301) (actual time=87.753..87.753 rows=0 loops=1)

9. 0.002 87.753 ↓ 0.0 0 1

Nested Loop (cost=179,771.59..216,695.74 rows=3 width=1,290) (actual time=87.753..87.753 rows=0 loops=1)

10. 0.000 87.751 ↓ 0.0 0 1

Nested Loop Left Join (cost=179,771.15..216,692.30 rows=3 width=1,289) (actual time=87.751..87.751 rows=0 loops=1)

11. 0.000 87.751 ↓ 0.0 0 1

Nested Loop Left Join (cost=179,770.72..216,690.55 rows=3 width=1,284) (actual time=87.751..87.751 rows=0 loops=1)

12. 0.000 87.751 ↓ 0.0 0 1

Nested Loop Left Join (cost=179,770.30..216,681.75 rows=3 width=1,284) (actual time=87.751..87.751 rows=0 loops=1)

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

Nested Loop Left Join (cost=179,770.30..216,680.69 rows=3 width=1,166) (actual time=87.751..87.751 rows=0 loops=1)

14. 0.001 87.751 ↓ 0.0 0 1

Nested Loop Left Join (cost=179,770.16..216,680.21 rows=3 width=955) (actual time=87.751..87.751 rows=0 loops=1)

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

Nested Loop Left Join (cost=179,769.87..216,679.08 rows=3 width=938) (actual time=87.750..87.750 rows=0 loops=1)

16. 0.000 87.750 ↓ 0.0 0 1

Nested Loop Left Join (cost=179,769.73..216,678.59 rows=3 width=829) (actual time=87.750..87.750 rows=0 loops=1)

17. 0.008 87.750 ↓ 0.0 0 1

Nested Loop (cost=179,769.31..216,676.71 rows=3 width=797) (actual time=87.750..87.750 rows=0 loops=1)

  • -> Nested Loop Left Join (cost=179769.17..216676.19 rows=3 width=479) (actual time=87.750..87.750 rows=
  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • -> Nested Loop Left Join (cost=179768.89..216382.79 rows=693 width=433) (actual time=87.749..87.7
  • -> Hash Join (cost=179768.46..213170.16 rows=693 width=392) (actual time=87.749..87.749 row
  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
  • -> Hash Left Join (cost=179696.31..213088.48 rows=693 width=361) (actual time=87.114.
  • Hash Cond: (sc.outsource_dest_id = dom.outsource_dest_id)
  • Filter: ((dom.outsource_dest_type IS NULL) OR (dom.outsource_dest_type <> 'C'::bp
  • -> Hash Left Join (cost=179689.29..213077.53 rows=710 width=129) (actual time=8
  • Hash Cond: (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END = tp.sample_collection_id)
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
18. 0.026 87.112 ↓ 0.0 0 1

Hash Left Join (cost=102,570.15..110,500.37 rows=17,717 width=120) (actual time=87.112..87.112 rows=0 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)
  • -> Hash Right Join (cost=26843.03..26954.49 rows=17717 width=105) (
  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on sample_rejections sr (cost=0.00..88.29 rows=5,729 width=8) (never executed)

20. 0.000 87.086 ↓ 0.0 0 1

Hash (cost=26,621.56..26,621.56 rows=17,717 width=101) (actual time=87.086..87.086 rows=0 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 256kB
21. 87.086 87.086 ↓ 0.0 0 1

Seq Scan on sample_collection sc (cost=0.00..26,621.56 rows=17,717 width=101) (actual time=87.086..87.086 rows=0 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: 599355
22. 0.000 0.000 ↓ 0.0 0

Hash (cost=50,941.28..50,941.28 rows=1,425,828 width=15) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tpr (cost=0.00..50,941.28 rows=1,425,828 width=15) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash (cost=50,941.28..50,941.28 rows=1,425,828 width=19) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tp (cost=0.00..50,941.28 rows=1,425,828 width=19) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.50..6.50 rows=42 width=244) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=4.15..6.50 rows=42 width=244) (never executed)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
28. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=1.85..3.81 rows=42 width=348) (never executed)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
29. 0.000 0.000 ↓ 0.0 0

Seq Scan on diag_outsource_master dom (cost=0.00..1.42 rows=42 width=130) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=38 width=336) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on outhouse_master om (cost=0.00..1.38 rows=38 width=336) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.13..2.13 rows=13 width=18) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Seq Scan on hospital_center_master hcm (cost=0.00..2.13 rows=13 width=18) (never executed)

34. 0.356 0.630 ↑ 1.0 1,518 1

Hash (cost=53.18..53.18 rows=1,518 width=47) (actual time=0.630..0.630 rows=1,518 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 135kB
35. 0.274 0.274 ↑ 1.0 1,518 1

Seq Scan on diagnostics d (cost=0.00..53.18 rows=1,518 width=47) (actual time=0.016..0.274 rows=1,518 loops=1)

36. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.29..0.41 rows=1 width=61) (never executed)

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

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

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

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..0.62 rows=1 width=47) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
40. 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=156) (never executed)

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

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

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

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.13..0.15 rows=1 width=266) (never executed)

  • Index Cond: ((hospital_id)::text = (isr.orig_lab_name)::text)
  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on sample_rejections sr (cost=0.00..88.29 rows=5,729 width=8) (never executed)

44. 0.000 87.086 ↓ 0.0 0 1

Hash (cost=26,621.56..26,621.56 rows=17,717 width=101) (actual time=87.086..87.086 rows=0 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 256kB
45. 87.086 87.086 ↓ 0.0 0 1

Seq Scan on sample_collection sc (cost=0.00..26,621.56 rows=17,717 width=101) (actual time=87.086..87.086 rows=0 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: 599355
46. 0.000 0.000 ↓ 0.0 0

Hash (cost=50,941.28..50,941.28 rows=1,425,828 width=15) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tpr (cost=0.00..50,941.28 rows=1,425,828 width=15) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Hash (cost=50,941.28..50,941.28 rows=1,425,828 width=19) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Seq Scan on tests_prescribed tp (cost=0.00..50,941.28 rows=1,425,828 width=19) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.50..6.50 rows=42 width=244) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=4.15..6.50 rows=42 width=244) (never executed)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
52. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=1.85..3.81 rows=42 width=348) (never executed)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
53. 0.000 0.000 ↓ 0.0 0

Seq Scan on diag_outsource_master dom (cost=0.00..1.42 rows=42 width=130) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=38 width=336) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on outhouse_master om (cost=0.00..1.38 rows=38 width=336) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.13..2.13 rows=13 width=18) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Seq Scan on hospital_center_master hcm (cost=0.00..2.13 rows=13 width=18) (never executed)

58. 0.356 0.630 ↑ 1.0 1,518 1

Hash (cost=53.18..53.18 rows=1,518 width=47) (actual time=0.630..0.630 rows=1,518 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 135kB
59. 0.274 0.274 ↑ 1.0 1,518 1

Seq Scan on diagnostics d (cost=0.00..53.18 rows=1,518 width=47) (actual time=0.016..0.274 rows=1,518 loops=1)

60. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.29..0.41 rows=1 width=61) (never executed)

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

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

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

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..0.62 rows=1 width=47) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
64. 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=156) (never executed)

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

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

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

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.13..0.15 rows=1 width=266) (never executed)

  • Index Cond: ((hospital_id)::text = (isr.orig_lab_name)::text)