explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d2RG

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 951.936 ↓ 25.0 25 1

Limit (cost=66,529.23..66,529.30 rows=1 width=203) (actual time=951.875..951.936 rows=25 loops=1)

2. 0.047 951.928 ↓ 25.0 25 1

Unique (cost=66,529.23..66,529.30 rows=1 width=203) (actual time=951.874..951.928 rows=25 loops=1)

3. 0.822 951.881 ↓ 96.0 96 1

Sort (cost=66,529.23..66,529.24 rows=1 width=203) (actual time=951.873..951.881 rows=96 loops=1)

  • Sort Key: treatment_plans.start_date, treatment_plans.id, treatment_plans.sid, treatment_plans.patient_sid, treatment_plans.end_date, treatment_plans.created_at, treatment_plans.updated_at, treatment_plans.completed_at, treatment_plans.completed, treatment_plans.patient_exemption_sid, treatment_plans.practitioner_sid, treatment_plans.regulation11_value, treatment_plans.treatment_on_referral, treatment_plans.continuing_treatment, treatment_plans.import_id, treatment_plans.site_id, treatment_plans.nhs_uda_value, treatment_plans.private_treatment_value, treatment_plans.nhs_completed_uda_value, treatment_plans.last_completed_at, treatment_plans.nickname, treatment_plans.treatment_plan_template_id, treatment_plans.patient_id, treatment_plans.patient_exemption_id, treatment_plans.practitioner_id
  • Sort Method: quicksort Memory: 89kB
4. 0.036 951.059 ↓ 263.0 263 1

Nested Loop (cost=29,351.81..66,529.22 rows=1 width=203) (actual time=119.822..951.059 rows=263 loops=1)

5. 0.432 949.623 ↓ 280.0 280 1

Nested Loop (cost=29,351.25..66,228.09 rows=1 width=219) (actual time=119.810..949.623 rows=280 loops=1)

  • Join Filter: (treatment_plans.id = services_appointments.treatment_plan_id)
6. 0.287 948.087 ↓ 552.0 552 1

Nested Loop (cost=29,350.68..66,218.37 rows=1 width=235) (actual time=98.301..948.087 rows=552 loops=1)

7. 0.187 946.445 ↓ 271.0 271 1

Nested Loop (cost=29,350.12..66,189.74 rows=1 width=203) (actual time=98.291..946.445 rows=271 loops=1)

  • Join Filter: (patients.id = patient_stats.patient_id)
8. 518.808 944.273 ↓ 397.0 397 1

Nested Loop (cost=29,349.69..66,181.30 rows=1 width=219) (actual time=98.278..944.273 rows=397 loops=1)

  • Join Filter: (treatment_plans.patient_id = patients.id)
  • Rows Removed by Join Filter: 7300353
9. 0.714 84.947 ↓ 1.6 551 1

Bitmap Heap Scan on treatment_plans (cost=28,339.63..64,049.89 rows=352 width=203) (actual time=84.255..84.947 rows=551 loops=1)

  • Recheck Cond: ((practice_id = 259) AND (start_date >= '2019-05-01'::date) AND (start_date <= '2019-08-13'::date))
  • Filter: (NOT completed)
  • Rows Removed by Filter: 1394
  • Heap Blocks: exact=144
10. 0.537 84.233 ↓ 0.0 0 1

BitmapAnd (cost=28,339.63..28,339.63 rows=10,332 width=0) (actual time=84.233..84.233 rows=0 loops=1)

11. 9.347 9.347 ↓ 1.1 155,573 1

Bitmap Index Scan on index_treatment_plans_on_practice_id_and_seq_id (cost=0.00..2,742.73 rows=146,973 width=0) (actual time=9.347..9.347 rows=155,573 loops=1)

  • Index Cond: (practice_id = 259)
12. 74.349 74.349 ↓ 1.1 1,297,812 1

Bitmap Index Scan on index_treatment_plans_on_start_date (cost=0.00..25,596.47 rows=1,210,803 width=0) (actual time=74.349..74.349 rows=1,297,812 loops=1)

  • Index Cond: ((start_date >= '2019-05-01'::date) AND (start_date <= '2019-08-13'::date))
13. 329.856 340.518 ↓ 133.8 13,250 551

Materialize (cost=1,010.06..1,608.94 rows=99 width=16) (actual time=0.004..0.618 rows=13,250 loops=551)

14. 8.378 10.662 ↓ 133.8 13,250 1

Bitmap Heap Scan on patients (cost=1,010.06..1,608.44 rows=99 width=16) (actual time=2.413..10.662 rows=13,250 loops=1)

  • Recheck Cond: ((payment_plan_id = '927c6958-4607-4f0f-a885-cd22db8ca2ac'::uuid) AND (practice_id = 259))
  • Filter: active
  • Rows Removed by Filter: 4032
  • Heap Blocks: exact=1264
15. 0.061 2.284 ↓ 0.0 0 1

BitmapAnd (cost=1,010.06..1,010.06 rows=153 width=0) (actual time=2.284..2.284 rows=0 loops=1)

16. 0.992 0.992 ↑ 1.0 17,375 1

Bitmap Index Scan on index_patients_on_payment_plan_id (cost=0.00..484.14 rows=17,561 width=0) (actual time=0.992..0.992 rows=17,375 loops=1)

  • Index Cond: (payment_plan_id = '927c6958-4607-4f0f-a885-cd22db8ca2ac'::uuid)
17. 1.231 1.231 ↓ 1.0 23,923 1

Bitmap Index Scan on index_patients_on_practice_id (cost=0.00..525.62 rows=23,626 width=0) (actual time=1.231..1.231 rows=23,923 loops=1)

  • Index Cond: (practice_id = 259)
18. 1.985 1.985 ↑ 1.0 1 397

Index Scan using index_patient_stats_on_patient_id_and_practice_id on patient_stats (cost=0.43..8.43 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=397)

  • Index Cond: ((practice_id = 259) AND (patient_id = treatment_plans.patient_id))
  • Filter: ((next_appointment_date IS NULL) OR (next_appointment_date < '2019-08-13 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 0
19. 1.355 1.355 ↓ 2.0 2 271

Index Scan using index_services_appointments_on_treatment_plan_id on services_appointments services_appointments_treatment_plans_join (cost=0.56..28.63 rows=1 width=32) (actual time=0.005..0.005 rows=2 loops=271)

  • Index Cond: (treatment_plan_id = treatment_plans.id)
  • Filter: (practice_id = 259)
20. 1.104 1.104 ↑ 1.0 1 552

Index Scan using index_services_appointments_on_treatment_plan_id on services_appointments (cost=0.56..9.70 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=552)

  • Index Cond: (treatment_plan_id = services_appointments_treatment_plans_join.treatment_plan_id)
  • Filter: ((appointment_id IS NULL) AND bookable AND (practice_id = 259))
  • Rows Removed by Filter: 3
21. 1.400 1.400 ↑ 1.0 1 280

Index Scan using index_treatment_plan_items_on_services_appointment_id on treatment_plan_items (cost=0.57..301.13 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=280)

  • Index Cond: (services_appointment_id = services_appointments_treatment_plans_join.id)
  • Filter: ((NOT completed) AND (practice_id = 259))
  • Rows Removed by Filter: 1