explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8OX6

Settings
# exclusive inclusive rows x rows loops node
1. 4,284.692 150,904.968 ↑ 1,235.8 118 1

GroupAggregate (cost=980,140.53..3,228,474.56 rows=145,830 width=113) (actual time=145,711.437..150,904.968 rows=118 loops=1)

  • Group Key: campaign_contact.campaign_id, campaign.title, campaign.is_archived, campaign.is_autoassign_enabled, campaign.limit_assignment_to_teams, (array_agg(DISTINCT team.title))
2. 18,544.917 146,617.682 ↓ 75.5 11,005,393 1

Sort (cost=980,140.53..980,505.11 rows=145,830 width=93) (actual time=145,655.336..146,617.682 rows=11,005,393 loops=1)

  • Sort Key: campaign_contact.campaign_id, campaign.title, campaign.is_archived, campaign.is_autoassign_enabled, campaign.limit_assignment_to_teams, (array_agg(DISTINCT team.title))
  • Sort Method: external sort Disk: 886120kB
3. 1,783.766 128,072.765 ↓ 75.5 11,005,393 1

Merge Left Join (cost=157.70..960,153.24 rows=145,830 width=93) (actual time=14.134..128,072.765 rows=11,005,393 loops=1)

  • Merge Cond: (campaign.id = campaign_team.campaign_id)
4. 1,140.793 126,068.824 ↓ 75.5 11,005,393 1

Nested Loop (cost=135.28..959,556.48 rows=145,830 width=65) (actual time=13.717..126,068.824 rows=11,005,393 loops=1)

5. 1.675 1.675 ↓ 1.0 148 1

Index Scan using campaign_pkey on campaign (cost=0.28..378.44 rows=143 width=48) (actual time=0.088..1.675 rows=148 loops=1)

  • Filter: (NOT is_archived)
  • Rows Removed by Filter: 2756
6. 123,821.832 124,926.356 ↓ 42.3 74,361 148

Bitmap Heap Scan on campaign_contact (cost=135.00..6,689.96 rows=1,758 width=17) (actual time=9.361..844.097 rows=74,361 loops=148)

  • Recheck Cond: ((campaign_id = campaign.id) AND (campaign_id > 3600) AND (NOT archived))
  • Rows Removed by Index Recheck: 23663
  • Filter: (NOT is_opted_out)
  • Rows Removed by Filter: 45
  • Heap Blocks: exact=1783848 lossy=104849
7. 1,104.524 1,104.524 ↓ 47.7 83,841 148

Bitmap Index Scan on todos_partial_idx (cost=0.00..134.56 rows=1,758 width=0) (actual time=7.463..7.463 rows=83,841 loops=148)

  • Index Cond: ((campaign_id = campaign.id) AND (campaign_id > 3600) AND (is_opted_out = false))
8. 219.056 220.175 ↓ 17,856.0 4,749,683 1

Materialize (cost=22.42..31.52 rows=266 width=36) (actual time=0.161..220.175 rows=4,749,683 loops=1)

9. 0.918 1.119 ↓ 1.0 271 1

GroupAggregate (cost=22.42..28.20 rows=266 width=40) (actual time=0.158..1.119 rows=271 loops=1)

  • Group Key: campaign_team.campaign_id
10. 0.114 0.201 ↓ 1.0 332 1

Sort (cost=22.42..23.24 rows=327 width=20) (actual time=0.147..0.201 rows=332 loops=1)

  • Sort Key: campaign_team.campaign_id
  • Sort Method: quicksort Memory: 50kB
11. 0.053 0.087 ↓ 1.0 332 1

Hash Join (cost=1.18..8.76 rows=327 width=20) (actual time=0.018..0.087 rows=332 loops=1)

  • Hash Cond: (campaign_team.team_id = team.id)
12. 0.026 0.026 ↓ 1.0 332 1

Seq Scan on campaign_team (cost=0.00..6.27 rows=327 width=8) (actual time=0.004..0.026 rows=332 loops=1)

13. 0.004 0.008 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=20) (actual time=0.008..0.008 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.004 0.004 ↑ 1.0 8 1

Seq Scan on team (cost=0.00..1.08 rows=8 width=20) (actual time=0.003..0.004 rows=8 loops=1)

15.          

SubPlan (for GroupAggregate)

16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..15.37 rows=1 width=0) (never executed)

  • Join Filter: (campaign_contact_tag.tag_id = all_tag.id)
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using campaign_contact_tag_pkey on campaign_contact_tag (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (campaign_contact_id = campaign_contact.id)
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on all_tag (cost=0.00..7.05 rows=1 width=4) (never executed)

  • Filter: ((deleted_at IS NULL) AND (NOT is_assignable))
19. 0.389 2.594 ↓ 4.7 4,226 1

Nested Loop (cost=31.12..1,475.96 rows=905 width=4) (actual time=0.034..2.594 rows=4,226 loops=1)

20. 0.040 0.040 ↓ 5.0 5 1

Seq Scan on all_tag all_tag_1 (cost=0.00..7.05 rows=1 width=4) (actual time=0.019..0.040 rows=5 loops=1)

  • Filter: ((deleted_at IS NULL) AND (NOT is_assignable))
  • Rows Removed by Filter: 101
21. 1.840 2.165 ↑ 1.7 845 5

Bitmap Heap Scan on campaign_contact_tag campaign_contact_tag_1 (cost=31.12..1,454.94 rows=1,397 width=8) (actual time=0.094..0.433 rows=845 loops=5)

  • Recheck Cond: (tag_id = all_tag_1.id)
  • Heap Blocks: exact=1288
22. 0.325 0.325 ↑ 1.6 887 5

Bitmap Index Scan on campaign_contact_tag_tag_idx (cost=0.00..30.77 rows=1,397 width=0) (actual time=0.065..0.065 rows=887 loops=5)

  • Index Cond: (tag_id = all_tag_1.id)
Planning time : 2.139 ms
Execution time : 151,014.233 ms