explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4P7e

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 2,191.856 ↓ 0.0 0 1

Sort (cost=663,064.37..663,064.37 rows=1 width=12) (actual time=2,191.856..2,191.856 rows=0 loops=1)

  • Sort Key: diag_radschedules_summary_view.pres_date
  • Sort Method: quicksort Memory: 25kB
  • Filter: ((ddept_id)::text = 'DDept0010'::text)
2. 0.001 2,191.848 ↓ 0.0 0 1

Subquery Scan on diag_radschedules_summary_view (cost=544.89..663,064.36 rows=1 width=12) (actual time=2,191.848..2,191.848 rows=0 loops=1)

3. 0.000 2,191.847 ↓ 0.0 0 1

Nested Loop (cost=544.89..663,064.35 rows=1 width=2,967) (actual time=2,191.847..2,191.847 rows=0 loops=1)

4. 0.000 2,191.847 ↓ 0.0 0 1

Nested Loop Left Join (cost=544.61..663,057.90 rows=1 width=36) (actual time=2,191.847..2,191.847 rows=0 loops=1)

5. 0.000 2,191.847 ↓ 0.0 0 1

Nested Loop Left Join (cost=544.05..663,053.81 rows=1 width=42) (actual time=2,191.847..2,191.847 rows=0 loops=1)

6. 0.001 2,191.847 ↓ 0.0 0 1

Nested Loop Left Join (cost=543.48..663,041.31 rows=1 width=28) (actual time=2,191.846..2,191.847 rows=0 loops=1)

7. 0.024 2,191.846 ↓ 0.0 0 1

Nested Loop Left Join (cost=537.78..663,033.44 rows=1 width=28) (actual time=2,191.846..2,191.846 rows=0 loops=1)

  • Filter: (COALESCE(pr.center_id, isr.center_id) = 22)
  • Rows Removed by Filter: 26
8. 0.047 2,191.744 ↑ 2.9 26 1

Nested Loop Left Join (cost=537.35..662,664.53 rows=75 width=39) (actual time=1,520.106..2,191.744 rows=26 loops=1)

9. 0.014 2,191.437 ↑ 2.9 26 1

Nested Loop Left Join (cost=536.92..662,188.97 rows=75 width=35) (actual time=1,520.083..2,191.437 rows=26 loops=1)

  • Join Filter: (tcr.prescribed_id = tp.prescribed_id)
10. 0.018 2,191.423 ↑ 2.9 26 1

Nested Loop Left Join (cost=536.92..662,144.84 rows=75 width=35) (actual time=1,520.081..2,191.423 rows=26 loops=1)

  • Join Filter: (thr.prescribed_id = tp.prescribed_id)
11. 0.009 2,191.405 ↑ 2.9 26 1

Nested Loop (cost=536.92..662,112.09 rows=75 width=35) (actual time=1,520.076..2,191.405 rows=26 loops=1)

12. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.18 rows=1 width=10) (actual time=0.010..0.013 rows=1 loops=1)

  • Filter: (((ddept_id)::text = 'DDept0010'::text) AND ((category)::text = 'DEP_RAD'::text))
  • Rows Removed by Filter: 11
13. 0.026 2,191.383 ↑ 2.9 26 1

Nested Loop Left Join (cost=536.92..662,110.16 rows=75 width=45) (actual time=1,520.063..2,191.383 rows=26 loops=1)

  • Filter: ((tp.report_id = 0) OR (tp.report_id IS NULL) OR (tvr.signed_off = 'N'::bpchar))
14. 0.170 2,191.331 ↑ 9.9 26 1

Hash Join (cost=536.49..660,473.13 rows=257 width=49) (actual time=1,520.056..2,191.331 rows=26 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
15. 2,190.443 2,190.443 ↑ 4.8 495 1

Seq Scan on tests_prescribed tp (cost=0.00..659,930.43 rows=2,363 width=52) (actual time=340.323..2,190.443 rows=495 loops=1)

  • Filter: (((pres_date)::date >= '2019-09-20'::date) AND ((pres_date)::date <= '2019-09-21'::date) AND ((conducted)::text <> ALL ('{X,U,RBS,RAS,S}'::text[])))
  • Rows Removed by Filter: 11769099
16. 0.036 0.718 ↑ 1.0 208 1

Hash (cost=533.89..533.89 rows=208 width=22) (actual time=0.717..0.718 rows=208 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
17. 0.682 0.682 ↑ 1.0 208 1

Seq Scan on diagnostics d (cost=0.00..533.89 rows=208 width=22) (actual time=0.010..0.682 rows=208 loops=1)

  • Rows Removed by Filter: 1703
18. 0.026 0.026 ↓ 0.0 0 26

Index Scan using test_visit_reports_pkey on test_visit_reports tvr (cost=0.43..6.35 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=26)

  • Index Cond: (tp.report_id = report_id)
19. 0.000 0.000 ↓ 0.0 0 26

Materialize (cost=0.00..10.30 rows=20 width=8) (actual time=0.000..0.000 rows=0 loops=26)

20. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on test_histopathology_results thr (cost=0.00..10.20 rows=20 width=8) (actual time=0.003..0.003 rows=0 loops=1)

21. 0.000 0.000 ↓ 0.0 0 26

Materialize (cost=0.00..10.45 rows=30 width=8) (actual time=0.000..0.000 rows=0 loops=26)

22. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on test_cytology_results tcr (cost=0.00..10.30 rows=30 width=8) (actual time=0.001..0.002 rows=0 loops=1)

23. 0.260 0.260 ↑ 1.0 1 26

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..6.34 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=26)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
24. 0.078 0.078 ↓ 0.0 0 26

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..4.91 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=26)

  • Index Cond: ((tp.pat_id)::text = (incoming_visit_id)::text)
25. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=5.70..7.85 rows=1 width=4) (never executed)

  • Hash Cond: (dom.outsource_dest_id = oh.outsource_dest_id)
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on diag_outsource_master dom (cost=0.00..1.83 rows=83 width=14) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=5.69..5.69 rows=1 width=8) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_outsource_sample_details_prescribed_id on outsource_sample_details oh (cost=0.43..5.69 rows=1 width=8) (never executed)

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

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.57..12.50 rows=1 width=22) (never executed)

  • Index Cond: ((activity_id)::text = ((tp.prescribed_id)::character varying)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
30. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using doctors_pkey on doctors condoc (cost=0.28..3.24 rows=1 width=8) (never executed)

  • Index Cond: ((doctor_id)::text = (COALESCE(bc.payee_doctor_id, bac.doctor_id))::text)
  • Filter: ((doctor_name)::text = 'Dr. Sudhi Shalwar'::text)
Planning time : 6.010 ms
Execution time : 2,192.122 ms