explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o9gm

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 94,248.602 ↓ 20.0 20 1

Limit (cost=539.34..778,696.06 rows=1 width=4) (actual time=55,195.086..94,248.602 rows=20 loops=1)

  • -> Subquery Scan on diag_radschedules_summary_view (cost=539.34..778696.06 rows=1 width=4) (actual time=55195.084..94248.562 rows=20 loop
2. 8.415 94,248.537 ↓ 20.0 20 1

Nested Loop (cost=539.34..778,696.05 rows=1 width=2,967) (actual time=55,195.082..94,248.537 rows=20 loops=1)

  • Join Filter: ((condoc.doctor_id)::text = (COALESCE(bc.payee_doctor_id, bac.doctor_id))::text)
3. 0.235 0.235 ↑ 1.0 1 1

Seq Scan on doctors condoc (cost=0.00..159.53 rows=1 width=8) (actual time=0.235..0.235 rows=1 loops=1)

  • Filter: ((doctor_name)::text = 'Dr. Sudhi Shalwar'::text)
  • Rows Removed by Filter: 747
4. 0.071 94,239.887 ↑ 1.5 20 1

Nested Loop Left Join (cost=539.34..778,535.90 rows=30 width=28) (actual time=55,190.254..94,239.887 rows=20 loops=1)

5. 17.565 89,981.376 ↑ 1.2 20 1

Nested Loop Left Join (cost=538.20..778,179.56 rows=25 width=20) (actual time=54,961.061..89,981.376 rows=20 loops=1)

  • Join Filter: (dom.outsource_dest_id = oh.outsource_dest_id)
  • Rows Removed by Join Filter: 1660
  • -> Nested Loop Left Join (cost=538.20..778146.40 rows=25 width=24) (actual time=54960.523..89980.293 rows=20 loop
  • -> Nested Loop Left Join (cost=537.78..778054.61 rows=25 width=20) (actual time=54889.702..89631.545 rows=2
  • Filter: (COALESCE(pr.center_id, isr.center_id) = 22)
  • Rows Removed by Filter: 1423
  • -> Nested Loop Left Join (cost=537.35..759620.71 rows=5074 width=31) (actual time=2.790..89610.344 ro
  • -> Nested Loop Left Join (cost=536.92..736349.52 rows=5074 width=27) (actual time=2.257..88375.
  • Join Filter: (tcr.prescribed_id = tp.prescribed_id)
  • -> Nested Loop Left Join (cost=536.92..734055.85 rows=5074 width=27) (actual time=2.255..
  • Join Filter: (thr.prescribed_id = tp.prescribed_id)
  • -> Nested Loop (cost=536.92..732523.40 rows=5074 width=27) (actual time=2.251..8837
  • -> Seq Scan on diagnostics_departments dd (cost=0.00..1.18 rows=1 width=10) (
  • Filter: (((ddept_id)::text = 'DDept0010'::text) AND ((category)::text = '
  • Rows Removed by Filter: 9
6. 6.395 88,370.545 ↑ 3.5 1,443 1

Nested Loop Left Join (cost=536.92..732,471.48 rows=5,074 width=37) (actual time=2.245..88,370.545 rows=1,443 loops=1)

  • Filter: ((tp.report_id = 0) OR (tp.report_id IS NULL) OR (tvr.signed_off = 'N'::bpchar))
7. 10.484 53,772.554 ↑ 12.0 1,443 1

Hash Join (cost=536.49..660,954.58 rows=17,286 width=41) (actual time=2.238..53,772.554 rows=1,443 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
8. 53,761.315 53,761.315 ↑ 8.1 19,714 1

Seq Scan on tests_prescribed tp (cost=0.00..660,000.28 rows=158,815 width=44) (actual time=0.137..53,761.315 rows=19,714 loops=1)

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

Hash (cost=533.89..533.89 rows=208 width=22) (actual time=0.754..0.755 rows=208 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
10. 0.718 0.718 ↑ 1.0 208 1

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

  • Filter: ((ddept_id)::text = 'DDept0010'::text)
  • Rows Removed by Filter: 1703
11. 34,591.596 34,591.596 ↓ 0.0 0 1,443

Index Scan using test_visit_reports_pkey on test_visit_reports tvr (cost=0.43..4.12 rows=1 width=6) (actual time=23.972..23.972 rows=0 loops=1,443)

  • Index Cond: (tp.report_id = report_id)
12. 1.441 1.443 ↓ 0.0 0 1,443

Materialize (cost=0.00..10.30 rows=20 width=8) (actual time=0.001..0.001 rows=0 loops=1,443)

13. 0.002 0.002 ↓ 0.0 0 1

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

14. 0.000 0.000 ↓ 0.0 0 1,443

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

15. 0.001 0.001 ↓ 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.001 rows=0 loops=1)

16. 1,226.550 1,226.550 ↑ 1.0 1 1,443

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..4.59 rows=1 width=20) (actual time=0.850..0.850 rows=1 loops=1,443)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
17. 15.873 15.873 ↓ 0.0 0 1,443

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..3.62 rows=1 width=20) (actual time=0.011..0.011 rows=0 loops=1,443)

  • Index Cond: ((tp.pat_id)::text = (incoming_visit_id)::text)
18. 348.680 348.680 ↓ 0.0 0 20

Index Scan using idx_outsource_sample_details_prescribed_id on outsource_sample_details oh (cost=0.43..3.66 rows=1 width=8) (actual time=17.434..17.434 rows=0 loops=20)

  • Index Cond: (prescribed_id = tp.prescribed_id)
19. 0.720 0.720 ↑ 1.0 83 20

Materialize (cost=0.00..2.25 rows=83 width=14) (actual time=0.024..0.036 rows=83 loops=20)

  • -> Seq Scan on diag_outsource_master dom (cost=0.00..1.83 rows=83 width=14) (actual time=0.459..0.478 rows=rows=83 loops=1)
20. 0.300 4,258.440 ↑ 1.0 1 20

Nested Loop Left Join (cost=1.14..14.24 rows=1 width=16) (actual time=212.919..212.922 rows=1 loops=20)

21. 4,048.860 4,048.860 ↑ 1.0 1 20

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.57..10.15 rows=1 width=22) (actual time=202.441..202.443 rows=1 loops=20)

  • Index Cond: ((activity_id)::text = ((tp.prescribed_id)::character varying)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
22. 209.280 209.280 ↑ 1.0 1 20

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..4.09 rows=1 width=14) (actual time=10.464..10.464 rows=1 loops=20)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
Planning time : 4.399 ms
Execution time : 94,248.792 ms