explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vx9 : Optimization for: plan #kc7D

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=22,240.05..22,240.06 rows=1 width=0) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8,733.30..22,240.04 rows=1 width=0) (actual rows= loops=)

  • Join Filter: ((activities.patient_id = activities_1.patient_id) AND (((model_updates.new_fields)::jsonb ->> 'assigned_pharmacy_id'::text) = ((model_updates_1.old_fields)::jsonb ->> 'assigned_pharmacy_id'::text)))
  • Filter: ((claims.dispense_date)::date <= CASE WHEN ((model_updates_1.created_at)::date IS NULL) THEN '2099-12-31'::date ELSE (model_updates_1.created_at)::date END)
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,910.65..11,890.78 rows=1 width=51) (actual rows= loops=)

  • Join Filter: (((claims.dispense_date)::date >= (model_updates.created_at)::date) AND ((((model_updates.new_fields)::jsonb ->> 'assigned_pharmacy_id'::text))::integer = claims.pharmacy_id))
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,910.22..11,432.47 rows=108 width=51) (actual rows= loops=)

  • Hash Cond: (activities.model_update_id = model_updates.id)
5. 0.000 0.000 ↓ 0.0

Seq Scan on activities (cost=0.00..5,445.76 rows=286,776 width=8) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash (cost=4,908.87..4,908.87 rows=108 width=51) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on model_updates (cost=0.00..4,908.87 rows=108 width=51) (actual rows= loops=)

  • Filter: (((new_fields)::jsonb ? 'assigned_pharmacy_id'::text) AND (((new_fields)::jsonb -> 'assigned_pharmacy_id'::text) <> 'null'::jsonb))
8. 0.000 0.000 ↓ 0.0

Index Scan using claims_patient_id on claims (cost=0.43..2.39 rows=57 width=16) (actual rows= loops=)

  • Index Cond: (patient_id = activities.patient_id)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,822.65..10,344.91 rows=109 width=42) (actual rows= loops=)

  • Hash Cond: (activities_1.model_update_id = model_updates_1.id)
10. 0.000 0.000 ↓ 0.0

Seq Scan on activities activities_1 (cost=0.00..5,445.76 rows=286,776 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash (cost=3,821.28..3,821.28 rows=109 width=42) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on model_updates model_updates_1 (cost=0.00..3,821.28 rows=109 width=42) (actual rows= loops=)

  • Filter: ((old_fields)::jsonb ? 'assigned_pharmacy_id'::text)