explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TwcQt : Optimization for: plan #1FxI

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 17,364.417 ↓ 0.0 0 1

Limit (cost=15,647.28..15,647.31 rows=10 width=12) (actual time=17,364.417..17,364.417 rows=0 loops=1)

2.          

CTE assignments

3. 0.830 1.004 ↑ 1.0 1,435 1

Bitmap Heap Scan on assignment (cost=43.59..862.93 rows=1,441 width=4) (actual time=0.231..1.004 rows=1,435 loops=1)

  • Recheck Cond: (user_id = 4)
  • Heap Blocks: exact=541
4. 0.174 0.174 ↑ 1.0 1,435 1

Bitmap Index Scan on assignment_user_id_index (cost=0.00..43.22 rows=1,441 width=0) (actual time=0.174..0.174 rows=1,435 loops=1)

  • Index Cond: (user_id = 4)
5. 0.006 17,364.416 ↓ 0.0 0 1

Sort (cost=14,784.35..14,784.47 rows=47 width=12) (actual time=17,364.416..17,364.416 rows=0 loops=1)

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

Nested Loop Left Join (cost=83.26..14,783.34 rows=47 width=12) (actual time=17,364.410..17,364.410 rows=0 loops=1)

7. 0.001 17,364.410 ↓ 0.0 0 1

Nested Loop Anti Join (cost=37.90..12,275.45 rows=47 width=12) (actual time=17,364.410..17,364.410 rows=0 loops=1)

8. 2.495 17,364.409 ↓ 0.0 0 1

Hash Join (cost=37.48..12,105.31 rows=62 width=25) (actual time=17,364.409..17,364.409 rows=0 loops=1)

  • Hash Cond: (campaign_contact.assignment_id = assignments.id)
9. 2.122 17,359.938 ↓ 73.8 10,551 1

Nested Loop (cost=0.55..12,067.32 rows=143 width=29) (actual time=0.476..17,359.938 rows=10,551 loops=1)

10. 0.392 0.392 ↑ 1.6 28 1

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

  • Filter: ((NOT is_archived) AND (organization_id = 1))
  • Rows Removed by Filter: 2290
11. 17,357.424 17,357.424 ↓ 62.8 377 28

Index Scan using todos_partial_idx on campaign_contact (cost=0.55..272.08 rows=6 width=33) (actual time=3.446..619.908 rows=377 loops=28)

  • Index Cond: ((campaign_id = campaign.id) AND (message_status = 'needsResponse'::text))
12. 0.185 1.976 ↓ 7.2 1,435 1

Hash (cost=34.42..34.42 rows=200 width=4) (actual time=1.975..1.976 rows=1,435 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 67kB
13. 0.504 1.791 ↓ 7.2 1,435 1

HashAggregate (cost=32.42..34.42 rows=200 width=4) (actual time=1.639..1.791 rows=1,435 loops=1)

  • Group Key: assignments.id
14. 1.287 1.287 ↑ 1.0 1,435 1

CTE Scan on assignments (cost=0.00..28.82 rows=1,441 width=4) (actual time=0.232..1.287 rows=1,435 loops=1)

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
16. 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 2))
17.          

SubPlan (for Index Scan)

18. 0.000 0.000 ↓ 0.0 0

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

19. 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
20. 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)
Planning time : 1.248 ms
Execution time : 17,364.655 ms