explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KplO

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 132,912.793 ↑ 1.0 20 1

Limit (cost=43,617.71..43,623.51 rows=20 width=952) (actual time=132,912.499..132,912.793 rows=20 loops=1)

  • Rows Removed by Filter: 0
2. 1.180 132,912.782 ↑ 45.0 20 1

Group (cost=43,617.71..43,879.00 rows=901 width=952) (actual time=132,912.497..132,912.782 rows=20 loops=1)

  • Group Key: tv.report_id, tp.mr_no, isr.mr_no, pd.salutation, pd.patient_name, pd.middle_name, pd.last_name, tp.pat_id, isr.patient_name
3. 48.234 132,911.602 ↑ 39.2 23 1

Sort (cost=43,617.71..43,619.97 rows=901 width=888) (actual time=132,911.598..132,911.602 rows=23 loops=1)

  • Sort Key: tv.report_id DESC, tp.mr_no, isr.mr_no, pd.salutation, pd.patient_name, pd.middle_name, pd.last_name, tp.pat_id, isr.pa
  • Sort Method: quicksort Memory: 3356kB
4. 19.287 132,863.368 ↓ 12.3 11,089 1

Hash Left Join (cost=349.22..43,573.50 rows=901 width=888) (actual time=2,897.011..132,863.368 rows=11,089 loops=1)

  • Hash Cond: (oh.outsource_dest_id = dom.outsource_dest_id)
5. 587.579 132,843.567 ↓ 12.3 11,089 1

Nested Loop Left Join (cost=346.35..43,558.24 rows=901 width=860) (actual time=2,896.460..132,843.567 rows=11,089 loops=1)

  • -> Nested Loop Left Join (cost=345.93..43390.33 rows=268 width=856) (actual time=2873.979..125458.603 rows=3729 loo
  • Filter: ((pr.center_id = 1) OR (isr.center_id = 1))
  • Rows Removed by Filter: 30197
  • -> Nested Loop Left Join (cost=345.50..42393.23 rows=2180 width=809) (actual time=2465.116..125051.174 rows=3
  • -> Nested Loop Left Join (cost=345.07..41260.24 rows=2180 width=791) (actual time=2464.014..115158.817
  • -> Hash Left Join (cost=344.64..40062.65 rows=2180 width=756) (actual time=2463.718..111318.656 r
  • Hash Cond: (thr.impression_id = him.impression_id)
  • -> Nested Loop Left Join (cost=332.39..40044.43 rows=2180 width=538) (actual time=2463.698.
  • -> Nested Loop (cost=331.82..38137.80 rows=3078 width=38) (actual time=2448.681
6. 0.005 6.295 ↑ 1.0 1 1

Nested Loop (cost=0.00..535.11 rows=1 width=8) (actual time=2.139..6.295 rows=1 loops=1)

  • Join Filter: ((d.ddept_id)::text = (dd.ddept_id)::text)
7. 6.278 6.278 ↑ 1.0 1 1

Seq Scan on diagnostics d (cost=0.00..533.89 rows=1 width=18) (actual time=2.123..6.278 rows=1 loops=1)

  • Filter: ((test_name)::text ~~* '%Procalcitonin, Serum%'::text)
  • Rows Removed by Filter: 1910
8. 0.012 0.012 ↑ 6.0 1 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.15 rows=6 width=10) (actual time=0.012..0.012 rows=1 loops=1)

  • Filter: ((category)::text = 'DEP_LAB'::text)
9. 591.682 3,031.168 ↓ 2.4 34,945 1

Bitmap Heap Scan on tests_prescribed tp (cost=331.82..37,457.81 rows=14,488 width=46) (actual time=2,446.535..3,031.168 rows=34,945 loops=1)

  • Recheck Cond: ((test_id)::text = (d.test_id)::text)
  • Filter: ((conducted)::text <> 'RAS'::text)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=31832
10. 2,439.486 2,439.486 ↓ 2.4 34,947 1

Bitmap Index Scan on tests_prescribed_test_id_idx (cost=0.00..328.20 rows=14,502 width=0) (actual time=2,439.486..2,439.486 rows=34,947 loops=1)

  • Index Cond: ((test_id)::text = (d.test_id)::text)
11. 107,910.160 107,910.160 ↑ 1.0 1 34,945

Index Scan using test_visit_reports_pkey on test_visit_reports tv (cost=0.43..0.50 rows=1 width=504) (actual time=3.088..3.088 rows=1 loops=34,945)

  • Index Cond: (report_id = tp.report_id)
  • Filter: ((report_state <> 'D'::bpchar) AND (handed_over = 'N'::bpchar) AND (signed_off = 'Y'::bpchar))
12. 101.778 101.778 ↓ 0.0 0 33,926

Index Scan using idx_test_histopathology_results_prescribed_id on test_histopathology_results thr (cost=0.14..0.16 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=33,926)

  • Index Cond: (tp.prescribed_id = prescribed_id)
13. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=11.00..11.00 rows=100 width=222) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on histo_impression_master him (cost=0.00..11.00 rows=100 width=222) (actual time=0.003..0.004 rows=0 loops=1)

15. 3,697.934 3,697.934 ↑ 1.0 1 33,926

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..0.55 rows=1 width=35) (actual time=0.109..0.109 rows=1 loops=33,926)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
16. 9,804.614 9,804.614 ↑ 1.0 1 33,926

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.52 rows=1 width=48) (actual time=0.289..0.289 rows=1 loops=33,926)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
17. 339.260 339.260 ↓ 0.0 0 33,926

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..0.44 rows=1 width=67) (actual time=0.010..0.010 rows=0 loops=33,926)

  • Index Cond: ((incoming_visit_id)::text = (tp.pat_id)::text)
18. 7,364.775 7,364.775 ↑ 3.5 2 3,729

Index Scan using idx_outsource_sample_details_visit_id on outsource_sample_details oh (cost=0.43..0.56 rows=7 width=20) (actual time=1.189..1.975 rows=2 loops=3,729)

  • Index Cond: ((visit_id)::text = (tp.pat_id)::text)
19. 0.041 0.514 ↑ 1.0 83 1

Hash (cost=1.83..1.83 rows=83 width=14) (actual time=0.514..0.514 rows=83 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
20. 0.473 0.473 ↑ 1.0 83 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.83 rows=83 width=14) (actual time=0.450..0.473 rows=83 loops=1)

Planning time : 1,042.217 ms
Execution time : 132,915.387 ms