explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5AB0

Settings
# exclusive inclusive rows x rows loops node
1. 0.366 18,287.772 ↓ 3.7 177 1

Sort (cost=26,702.81..26,702.93 rows=48 width=251) (actual time=18,287.756..18,287.772 rows=177 loops=1)

  • Sort Key: patient.id, o.park_until_date_time, o.received_date_time
  • Sort Method: quicksort Memory: 68kB
2. 0.440 18,287.406 ↓ 3.7 177 1

Nested Loop Left Join (cost=1.83..26,701.47 rows=48 width=251) (actual time=751.294..18,287.406 rows=177 loops=1)

3. 1,849.502 18,285.937 ↓ 3.7 177 1

Nested Loop (cost=1.54..25,809.57 rows=48 width=154) (actual time=751.265..18,285.937 rows=177 loops=1)

4. 1,356.501 3,469.819 ↓ 91.5 3,241,654 1

Nested Loop (cost=1.12..5,067.52 rows=35,420 width=104) (actual time=0.036..3,469.819 rows=3,241,654 loops=1)

  • Join Filter: (actororg.organization_id = patient.organization_id)
5. 0.369 1.483 ↓ 165.0 165 1

Nested Loop (cost=0.56..30.71 rows=1 width=64) (actual time=0.025..1.483 rows=165 loops=1)

6. 0.454 0.454 ↓ 165.0 165 1

Index Scan using ix_actor_organization_actor_id on actor_organization actororg (cost=0.29..22.38 rows=1 width=8) (actual time=0.021..0.454 rows=165 loops=1)

  • Index Cond: (actor_id = 5563)
  • Filter: (((duty_status)::text <> 'OffDutyHide'::text) AND ((duty_status)::text <> 'Disabled'::text))
7. 0.660 0.660 ↑ 1.0 1 165

Index Scan using pk_organization on organization organization1 (cost=0.27..8.29 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=165)

  • Index Cond: (id = actororg.organization_id)
8. 2,111.835 2,111.835 ↑ 1.9 19,646 165

Index Scan using idx_patient_organization_id_assigned_pharmacist_actor_id on patient (cost=0.56..4,571.23 rows=37,247 width=56) (actual time=0.009..12.799 rows=19,646 loops=165)

  • Index Cond: (organization_id = organization1.id)
9. 12,966.616 12,966.616 ↓ 0.0 0 3,241,654

Index Scan using idx_order_patient_id_status_open_pending on "order" o (cost=0.42..0.58 rows=1 width=58) (actual time=0.004..0.004 rows=0 loops=3,241,654)

  • Index Cond: (patient_id = patient.id)
10. 0.177 0.177 ↓ 0.0 0 177

Index Scan using pk_actor on actor pa (cost=0.28..0.30 rows=1 width=29) (actual time=0.001..0.001 rows=0 loops=177)

  • Index Cond: (patient.assigned_pharmacist_actor_id = id)
11.          

SubPlan (forNested Loop Left Join)

12. 0.708 0.708 ↓ 0.0 0 177

Index Scan using ix_intervention_order_id_status on intervention t1 (cost=0.43..8.45 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=177)

  • Index Cond: ((o.id = order_id) AND ((status)::text = 'OnHold'::text))
  • Filter: ((removed_by_actor_id IS NULL) AND (clinical_program_patient_activity_id IS NULL))
13. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on intervention t1_1 (cost=20.64..31,346.70 rows=46,106 width=8) (never executed)

  • Recheck Cond: (((status)::text = 'OnHold'::text) AND (removed_by_actor_id IS NULL) AND (clinical_program_patient_activity_id IS NULL))
14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_intervention_patient_id_status_onhold (cost=0.00..9.11 rows=46,106 width=0) (never executed)

15. 0.004 0.144 ↓ 0.0 0 24

Nested Loop Anti Join (cost=0.86..377.55 rows=42 width=0) (actual time=0.006..0.006 rows=0 loops=24)

16. 0.120 0.120 ↓ 0.0 0 24

Index Scan using ix_medication_order_order_id on medication_order t2 (cost=0.44..14.63 rows=42 width=8) (actual time=0.005..0.005 rows=0 loops=24)

  • Index Cond: (o.id = order_id)
  • Filter: is_actionable
17. 0.020 0.020 ↑ 1.0 1 1

Index Scan using ix_intervention_removed_by_actor_id_medication_order_id on intervention t3 (cost=0.43..8.45 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: ((removed_by_actor_id IS NULL) AND (t2.id = medication_order_id))
  • Filter: ((clinical_program_patient_activity_id IS NULL) AND ((status)::text = 'OnHold'::text))
18. 0.000 0.000 ↓ 0.0 0

Hash Anti Join (cost=31,923.02..1,332,584.73 rows=8,416,236 width=8) (never executed)

  • Hash Cond: (t2_1.id = t3_1.medication_order_id)
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on medication_order t2_1 (cost=0.00..1,186,191.27 rows=8,431,592 width=16) (never executed)

  • Filter: is_actionable
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=31,346.70..31,346.70 rows=46,106 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on intervention t3_1 (cost=20.64..31,346.70 rows=46,106 width=8) (never executed)

  • Recheck Cond: (((status)::text = 'OnHold'::text) AND (removed_by_actor_id IS NULL) AND (clinical_program_patient_activity_id IS NULL))
22. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_intervention_patient_id_status_onhold (cost=0.00..9.11 rows=46,106 width=0) (never executed)

Planning time : 2.584 ms
Execution time : 18,288.028 ms