explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XbjO : Optimization for: Optimization for: plan #1FxI; plan #TwcQt

Settings

Optimization path:

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

Limit (cost=17,529.06..17,529.08 rows=10 width=12) (actual time=19,852.664..19,852.664 rows=0 loops=1)

2.          

CTE assignments

3. 0.772 0.896 ↑ 1.0 1,435 1

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

  • Recheck Cond: (user_id = 4)
  • Heap Blocks: exact=541
4. 0.124 0.124 ↑ 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.124..0.124 rows=1,435 loops=1)

  • Index Cond: (user_id = 4)
5.          

CTE campaigns

6. 0.314 0.314 ↑ 1.6 28 1

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

  • Filter: ((NOT is_archived) AND (organization_id = 1))
  • Rows Removed by Filter: 2290
7. 0.003 19,852.663 ↓ 0.0 0 1

Sort (cost=16,573.16..16,573.36 rows=80 width=12) (actual time=19,852.663..19,852.663 rows=0 loops=1)

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

Nested Loop Left Join (cost=84.25..16,571.43 rows=80 width=12) (actual time=19,852.660..19,852.660 rows=0 loops=1)

9. 0.000 19,852.659 ↓ 0.0 0 1

Nested Loop Anti Join (cost=38.89..12,302.68 rows=80 width=12) (actual time=19,852.659..19,852.659 rows=0 loops=1)

10. 2.381 19,852.659 ↓ 0.0 0 1

Hash Join (cost=38.46..12,014.52 rows=105 width=25) (actual time=19,852.659..19,852.659 rows=0 loops=1)

  • Hash Cond: (campaign_contact.assignment_id = assignments.id)
11. 2.011 19,848.511 ↓ 42.7 10,387 1

Nested Loop (cost=1.54..11,975.79 rows=243 width=29) (actual time=3.620..19,848.511 rows=10,387 loops=1)

12. 0.060 0.380 ↑ 1.6 28 1

HashAggregate (cost=0.99..1.43 rows=44 width=4) (actual time=0.327..0.380 rows=28 loops=1)

  • Group Key: campaigns.id
13. 0.320 0.320 ↑ 1.6 28 1

CTE Scan on campaigns (cost=0.00..0.88 rows=44 width=4) (actual time=0.284..0.320 rows=28 loops=1)

14. 19,846.120 19,846.120 ↓ 61.8 371 28

Index Scan using todos_partial_idx on campaign_contact (cost=0.55..272.08 rows=6 width=33) (actual time=4.430..708.790 rows=371 loops=28)

  • Index Cond: ((campaign_id = campaigns.id) AND (message_status = 'needsResponse'::text))
15. 0.175 1.767 ↓ 7.2 1,435 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 67kB
16. 0.444 1.592 ↓ 7.2 1,435 1

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

  • Group Key: assignments.id
17. 1.148 1.148 ↑ 1.0 1,435 1

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

18. 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
19. 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 3))
20.          

SubPlan (for Index Scan)

21. 0.000 0.000 ↓ 0.0 0

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

22. 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
23. 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.197 ms
Execution time : 19,852.867 ms