explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1FxI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 19,000.090 ↓ 0.0 0 1

Limit (cost=12,335.56..12,335.56 rows=2 width=12) (actual time=19,000.090..19,000.090 rows=0 loops=1)

2. 0.026 19,000.089 ↓ 0.0 0 1

Sort (cost=12,335.56..12,335.56 rows=2 width=12) (actual time=19,000.089..19,000.089 rows=0 loops=1)

  • Sort Key: (COALESCE(message.created_at, campaign_contact.updated_at)) DESC, campaign_contact.id
  • Sort Method: quicksort Memory: 25kB
3. 0.000 19,000.063 ↓ 0.0 0 1

Nested Loop Anti Join (cost=46.76..12,335.55 rows=2 width=12) (actual time=19,000.063..19,000.063 rows=0 loops=1)

4. 0.000 19,000.063 ↓ 0.0 0 1

Nested Loop Left Join (cost=46.33..12,327.77 rows=2 width=33) (actual time=19,000.063..19,000.063 rows=0 loops=1)

5. 8.050 19,000.063 ↓ 0.0 0 1

Nested Loop (cost=0.97..12,221.05 rows=2 width=25) (actual time=19,000.063..19,000.063 rows=0 loops=1)

6. 1.982 18,970.773 ↓ 74.3 10,620 1

Nested Loop (cost=0.55..12,067.32 rows=143 width=29) (actual time=0.723..18,970.773 rows=10,620 loops=1)

7. 0.611 0.611 ↑ 1.6 28 1

Seq Scan on campaign (cost=0.00..92.96 rows=44 width=4) (actual time=0.526..0.611 rows=28 loops=1)

  • Filter: ((NOT is_archived) AND (organization_id = 1))
  • Rows Removed by Filter: 2290
8. 18,968.180 18,968.180 ↓ 63.2 379 28

Index Scan using todos_partial_idx on campaign_contact (cost=0.55..272.08 rows=6 width=33) (actual time=3.787..677.435 rows=379 loops=28)

  • Index Cond: ((campaign_id = campaign.id) AND (message_status = 'needsResponse'::text))
9. 21.240 21.240 ↓ 0.0 0 10,620

Index Scan using assignment_pkey on assignment (cost=0.42..1.08 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=10,620)

  • Index Cond: (id = campaign_contact.assignment_id)
  • Filter: (user_id = 4)
  • Rows Removed by Filter: 1
10. 0.000 0.000 ↓ 0.0 0

Index Scan using message_pkey on message (cost=45.36..53.36 rows=1 width=12) (never executed)

  • Index Cond: (id = (SubPlan 1))
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0 0

Limit (cost=44.79..44.79 rows=1 width=12) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Sort (cost=44.79..44.82 rows=10 width=12) (never executed)

  • Sort Key: message_1.created_at DESC
14. 0.000 0.000 ↓ 0.0 0

Index Scan using message_campaign_contact_id_index on message message_1 (cost=0.57..44.74 rows=10 width=12) (never executed)

  • Index Cond: (campaign_contact_id = campaign_contact.id)
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using opt_out_cell_index on opt_out (cost=0.42..2.76 rows=1 width=13) (never executed)

  • Index Cond: (cell = campaign_contact.cell)
  • Heap Fetches: 0
Planning time : 2.701 ms
Execution time : 19,000.280 ms