explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oPg

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 97,093.579 ↓ 0.0 0 1

Limit (cost=10,986.93..10,986.96 rows=1 width=36) (actual time=97,093.579..97,093.579 rows=0 loops=1)

2. 0.092 97,093.571 ↓ 51.0 51 1

GroupAggregate (cost=10,986.91..10,986.93 rows=1 width=36) (actual time=97,093.488..97,093.571 rows=51 loops=1)

  • Group Key: diag_schedules_lab_summary_view.patient_id, diag_schedules_lab_summary_view.mr_no, diag_schedules_lab_summary_view.verify_finger_print
3. 0.547 97,093.479 ↓ 59.0 59 1

Sort (cost=10,986.91..10,986.91 rows=1 width=36) (actual time=97,093.470..97,093.479 rows=59 loops=1)

  • Sort Key: diag_schedules_lab_summary_view.patient_id, diag_schedules_lab_summary_view.mr_no, diag_schedules_lab_summary_view.verify_finger_print
  • Sort Method: quicksort Memory: 29kB
4. 0.103 97,092.932 ↓ 59.0 59 1

Subquery Scan on diag_schedules_lab_summary_view (cost=557.38..10,986.90 rows=1 width=36) (actual time=371.189..97,092.932 rows=59 loops=1)

5. 865.415 97,092.829 ↓ 59.0 59 1

Nested Loop Left Join (cost=557.38..10,986.89 rows=1 width=2,603) (actual time=371.186..97,092.829 rows=59 loops=1)

  • Filter: ((sc.sample_receive_status IS NULL) OR (sc.sample_receive_status = 'R'::bpchar))
6. 0.567 95,781.610 ↓ 59.0 59 1

Nested Loop Left Join (cost=556.95..10,980.32 rows=1 width=56) (actual time=90.684..95,781.610 rows=59 loops=1)

7. 61.407 95,588.821 ↓ 59.0 59 1

Nested Loop Left Join (cost=551.07..10,972.28 rows=1 width=56) (actual time=81.355..95,588.821 rows=59 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 1)
  • Rows Removed by Filter: 44448
8. 184.007 90,453.616 ↓ 178.0 44,507 1

Nested Loop Left Join (cost=550.65..9,709.79 rows=250 width=52) (actual time=81.313..90,453.616 rows=44,507 loops=1)

  • -> Nested Loop Left Join (cost=550.21..8117.56 rows=250 width=46) (actual time=80.000..70958.55rows=44507 loops=1)
9. 71.806 70,864.557 ↓ 178.0 44,507 1

Nested Loop Left Join (cost=550.08..8,071.57 rows=250 width=46) (actual time=79.973..70,864.557 rows=44,507 loops=1)

10. 130.180 70,703.737 ↓ 178.0 44,507 1

Nested Loop Left Join (cost=549.94..8,025.57 rows=250 width=46) (actual time=79.926..70,703.737 rows=44,507 loops=1)

  • Filter: ((tp.report_id = 0) OR (tp.report_id IS NULL) OR (tvr.signed_off = 'N'::bpchar))
  • Rows Removed by Filter: 569
11. 125.429 6,205.029 ↓ 51.8 45,076 1

Hash Join (cost=549.51..2,461.06 rows=870 width=50) (actual time=12.590..6,205.029 rows=45,076 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
12. 6,074.714 6,074.714 ↓ 28.3 49,271 1

Index Scan using tests_prescribed_conducted_idx on tests_prescribed tp (cost=0.43..1,896.76 rows=1,739 width=50) (actual time=0.993..6,074.714 rows=49,271 loops=1)

  • Index Cond: (((conducted)::text = ANY ('{N,P,C,V,RC,RV,NRN,CRN,RP,MA,TS,CC,CR}'::text[])) AND ((conducted)::text = ANY ('{C,V,RC,RV}'::text[])))
13. 0.493 4.886 ↓ 1.3 1,217 1

Hash (cost=537.12..537.12 rows=956 width=8) (actual time=4.886..4.886 rows=1,217 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 64kB
14. 0.698 4.393 ↓ 1.3 1,217 1

Hash Join (cost=1.22..537.12 rows=956 width=8) (actual time=0.067..4.393 rows=1,217 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
15. 3.675 3.675 ↑ 1.0 1,911 1

Seq Scan on diagnostics d (cost=0.00..529.11 rows=1,911 width=22) (actual time=0.024..3.675 rows=1,911 loops=1)

16. 0.009 0.020 ↑ 1.0 6 1

Hash (cost=1.15..1.15 rows=6 width=10) (actual time=0.020..0.020 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.011 0.011 ↑ 1.0 6 1

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

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 6
18. 64,368.528 64,368.528 ↑ 1.0 1 45,076

Index Scan using test_visit_reports_pkey on test_visit_reports tvr (cost=0.43..6.38 rows=1 width=6) (actual time=1.428..1.428 rows=1 loops=45,076)

  • Index Cond: (tp.report_id = report_id)
19. 89.014 89.014 ↓ 0.0 0 44,507

Index Scan using idx_test_histopathology_results_prescribed_id on test_histopathology_results thr (cost=0.14..0.17 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=44,507)

  • Index Cond: (prescribed_id = tp.prescribed_id)
20. 44.507 44.507 ↓ 0.0 0 44,507

Index Scan using idx_test_cytology_results_prescribed_id on test_cytology_results tcr (cost=0.14..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=44,507)

  • Index Cond: (prescribed_id = tp.prescribed_id)
21. 19,360.545 19,360.545 ↑ 1.0 1 44,507

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..6.37 rows=1 width=22) (actual time=0.435..0.435 rows=1 loops=44,507)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
22. 5,073.798 5,073.798 ↓ 0.0 0 44,507

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..5.04 rows=1 width=20) (actual time=0.114..0.114 rows=0 loops=44,507)

  • Index Cond: ((tp.pat_id)::text = (incoming_visit_id)::text)
23. 1.035 192.222 ↓ 0.0 0 59

Hash Right Join (cost=5.88..8.03 rows=1 width=4) (actual time=3.257..3.258 rows=0 loops=59)

  • Hash Cond: (dom.outsource_dest_id = oh.outsource_dest_id)
24. 0.322 0.322 ↑ 1.0 83 1

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

25. 0.118 190.865 ↓ 0.0 0 59

Hash (cost=5.87..5.87 rows=1 width=8) (actual time=3.235..3.235 rows=0 loops=59)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
26. 190.747 190.747 ↓ 0.0 0 59

Index Scan using idx_outsource_sample_details_prescribed_id on outsource_sample_details oh (cost=0.43..5.87 rows=1 width=8) (actual time=3.233..3.233 rows=0 loops=59)

  • Index Cond: (prescribed_id = tp.prescribed_id)
27. 445.804 445.804 ↑ 1.0 1 59

Index Scan using sample_collection_id_pkey on sample_collection sc (cost=0.43..6.30 rows=1 width=6) (actual time=7.556..7.556 rows=1 loops=59)

  • Index Cond: (sample_collection_id = tp.sample_collection_id)
Planning time : 185.679 ms
Execution time : 97,097.323 ms