explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QD6N

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 1,800.266 ↑ 4.0 1 1

Nested Loop (cost=74,167.17..82,466.48 rows=4 width=32) (actual time=1,606.290..1,800.266 rows=1 loops=1)

  • Output: (ec.id)::text
  • Join Filter: (ecr.email = ANY (c.email))
  • Rows Removed by Join Filter: 2
  • Buffers: shared hit=1280668 dirtied=179
2. 0.016 0.016 ↑ 1.0 1 1

Index Scan using contacts_pkey on public.contacts c (cost=0.42..2.65 rows=1 width=66) (actual time=0.013..0.016 rows=1 loops=1)

  • Output: c.email, c.brand
  • Index Cond: (c.id = 'adf6fd75-1ead-46bf-acc0-11fa24b165ef'::uuid)
  • Filter: (c.brand = 'fca6efa6-2f91-11ea-b7a0-0a653b6fef3e'::uuid)
  • Buffers: shared hit=4
3. 50.209 1,800.237 ↑ 25.7 3 1

Hash Join (cost=74,166.74..82,462.08 rows=77 width=64) (actual time=1,589.916..1,800.237 rows=3 loops=1)

  • Output: ec.id, ec.brand, ecr.email
  • Inner Unique: true
  • Hash Cond: (ecr.campaign = ec.id)
  • Buffers: shared hit=1280664 dirtied=179
4. 525.002 1,749.976 ↓ 1.9 682,372 1

HashAggregate (cost=74,133.35..77,799.42 rows=366,607 width=84) (actual time=1,576.076..1,749.976 rows=682,372 loops=1)

  • Output: ecr.campaign, ecr.email, (NULL::uuid), (NULL::uuid), ecr.send_type
  • Group Key: ecr.campaign, ecr.email, (NULL::uuid), (NULL::uuid), ecr.send_type
  • Buffers: shared hit=1280639 dirtied=179
5. 59.904 1,224.974 ↓ 1.9 710,899 1

Append (cost=722.80..69,550.76 rows=366,607 width=84) (actual time=2.775..1,224.974 rows=710,899 loops=1)

  • Buffers: shared hit=1280639 dirtied=179
6. 19.029 49.955 ↓ 2.0 90,393 1

Hash Join (cost=722.80..5,565.80 rows=44,934 width=76) (actual time=2.774..49.955 rows=90,393 loops=1)

  • Output: ecr.campaign, ecr.email, NULL::uuid, NULL::uuid, ecr.send_type
  • Inner Unique: true
  • Hash Cond: (ecr.campaign = ec_1.id)
  • Buffers: shared hit=3034
7. 28.172 28.172 ↓ 2.0 90,393 1

Seq Scan on public.email_campaigns_recipients ecr (cost=0.00..4,724.95 rows=44,934 width=44) (actual time=0.008..28.172 rows=90,393 loops=1)

  • Output: ecr.id, ecr.created_at, ecr.updated_at, ecr.deleted_at, ecr.campaign, ecr.tag, ecr.list, ecr.contact, ecr.email, ecr.send_type, ecr.recipient_type, ecr.brand, ecr.agent
  • Filter: ((ecr.email IS NOT NULL) AND (ecr.deleted_at IS NULL) AND (ecr.recipient_type = 'Email'::email_campaign_recipient_type))
  • Rows Removed by Filter: 94284
  • Buffers: shared hit=2419
8. 1.225 2.754 ↓ 1.0 4,792 1

Hash (cost=662.91..662.91 rows=4,791 width=16) (actual time=2.754..2.754 rows=4,792 loops=1)

  • Output: ec_1.id
  • Buckets: 8192 Batches: 1 Memory Usage: 289kB
  • Buffers: shared hit=615
9. 1.529 1.529 ↓ 1.0 4,792 1

Seq Scan on public.email_campaigns ec_1 (cost=0.00..662.91 rows=4,791 width=16) (actual time=0.003..1.529 rows=4,792 loops=1)

  • Output: ec_1.id
  • Buffers: shared hit=615
10. 3.949 142.441 ↓ 26,516.0 26,516 1

Gather (cost=1,001.13..7,334.45 rows=1 width=84) (actual time=0.865..142.441 rows=26,516 loops=1)

  • Output: email_campaigns.id, (contacts.email[1]), contacts.id, NULL::uuid, email_campaigns_recipients.send_type
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=547948
11. 19.303 138.492 ↓ 13,258.0 13,258 2 / 2

Nested Loop (cost=1.13..6,334.35 rows=1 width=84) (actual time=0.274..138.492 rows=13,258 loops=2)

  • Output: email_campaigns.id, contacts.email[1], contacts.id, NULL::uuid, email_campaigns_recipients.send_type
  • Inner Unique: true
  • Join Filter: (email_campaigns_recipients.campaign = email_campaigns.id)
  • Rows Removed by Join Filter: 177290
  • Buffers: shared hit=547948
  • Worker 0: actual time=0.158..141.124 rows=11528 loops=1
  • Buffers: shared hit=273407
12. 0.000 52.899 ↓ 4,419.3 13,258 2 / 2

Nested Loop (cost=0.85..6,332.15 rows=3 width=102) (actual time=0.241..52.899 rows=13,258 loops=2)

  • Output: email_campaigns_recipients.send_type, email_campaigns_recipients.campaign, contacts.email, contacts.id, contacts.brand
  • Inner Unique: true
  • Buffers: shared hit=119120
  • Worker 0: actual time=0.125..54.640 rows=11528 loops=1
  • Buffers: shared hit=53121
13. 1.519 15.172 ↓ 2,651.6 13,258 2 / 2

Nested Loop (cost=0.42..6,327.78 rows=5 width=36) (actual time=0.230..15.172 rows=13,258 loops=2)

  • Output: email_campaigns_recipients.send_type, email_campaigns_recipients.campaign, crm_lists_members.contact
  • Buffers: shared hit=12967
  • Worker 0: actual time=0.112..22.208 rows=11528 loops=1
  • Buffers: shared hit=6959
14. 8.865 8.865 ↑ 1.1 36 2 / 2

Parallel Seq Scan on public.email_campaigns_recipients (cost=0.00..3,775.44 rows=40 width=36) (actual time=0.216..8.865 rows=36 loops=2)

  • Output: email_campaigns_recipients.id, email_campaigns_recipients.created_at, email_campaigns_recipients.updated_at, email_campaigns_recipients.deleted_at, email_campaigns_recipients.campaign, email_campaigns_recipients.tag, email_campaigns_recipients.list, email_campaigns_recipients.contact, email_campaigns_recipients.email, email_campaigns_recipients.send_type, email_campaigns_recipients.recipient_type, email_campaigns_recipients.brand, email_campaigns_recipients.agent
  • Filter: (email_campaigns_recipients.recipient_type = 'List'::email_campaign_recipient_type)
  • Rows Removed by Filter: 92302
  • Buffers: shared hit=2419
  • Worker 0: actual time=0.097..16.799 rows=30 loops=1
  • Buffers: shared hit=2296
15. 4.788 4.788 ↑ 1.0 368 72 / 2

Index Scan using contact_lists_members_pkey on public.crm_lists_members (cost=0.42..60.12 rows=369 width=32) (actual time=0.005..0.133 rows=368 loops=72)

  • Output: crm_lists_members.list, crm_lists_members.contact, crm_lists_members.is_manual, crm_lists_members.created_at, crm_lists_members.deleted_at
  • Index Cond: (crm_lists_members.list = email_campaigns_recipients.list)
  • Filter: (crm_lists_members.deleted_at IS NULL)
  • Rows Removed by Filter: 29
  • Buffers: shared hit=10548
  • Worker 0: actual time=0.005..0.137 rows=384 loops=30
  • Buffers: shared hit=4663
16. 39.774 39.774 ↑ 1.0 1 26,516 / 2

Index Scan using contacts_pkey on public.contacts (cost=0.42..0.87 rows=1 width=82) (actual time=0.003..0.003 rows=1 loops=26,516)

  • Output: contacts.email, contacts.id, contacts.brand
  • Index Cond: (contacts.id = crm_lists_members.contact)
  • Filter: (contacts.deleted_at IS NULL)
  • Buffers: shared hit=106153
  • Worker 0: actual time=0.003..0.003 rows=1 loops=11528
  • Buffers: shared hit=46162
17. 66.290 66.290 ↑ 1.1 14 26,516 / 2

Index Scan using email_campaigns_brand_idx on public.email_campaigns (cost=0.28..0.55 rows=15 width=32) (actual time=0.001..0.005 rows=14 loops=26,516)

  • Output: email_campaigns.id, email_campaigns.created_at, email_campaigns.updated_at, email_campaigns.deleted_at, email_campaigns.created_by, email_campaigns.brand, email_campaigns.subject, email_campaigns.include_signature, email_campaigns.html, email_campaigns.due_at, email_campaigns.executed_at, email_campaigns."from", email_campaigns.individual, email_campaigns.accepted, email_campaigns.rejected, email_campaigns.delivered, email_campaigns.failed, email_campaigns.opened, email_campaigns.clicked, email_campaigns.unsubscribed, email_campaigns.complained, email_campaigns.stored, email_campaigns.template, email_campaigns.deal, email_campaigns.text, email_campaigns.headers, email_campaigns.google_credential, email_campaigns.microsoft_credential, email_campaigns.thread_key
  • Index Cond: (email_campaigns.brand = contacts.brand)
  • Buffers: shared hit=428828
  • Worker 0: actual time=0.001..0.006 rows=18 loops=11528
  • Buffers: shared hit=220286
18. 0.000 561.165 ↓ 24.2 283,877 1

Gather (cost=1,000.71..22,895.53 rows=11,724 width=84) (actual time=0.486..561.165 rows=283,877 loops=1)

  • Output: email_campaigns_1.id, (contacts_1.email[1]), contacts_1.id, NULL::uuid, email_campaigns_recipients_1.send_type
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=605841 dirtied=18
19. 179.277 578.563 ↓ 20.6 141,938 2 / 2

Nested Loop (cost=0.70..20,723.13 rows=6,896 width=84) (actual time=0.157..578.563 rows=141,938 loops=2)

  • Output: email_campaigns_1.id, contacts_1.email[1], contacts_1.id, NULL::uuid, email_campaigns_recipients_1.send_type
  • Join Filter: (ARRAY[email_campaigns_recipients_1.tag] <@ contacts_1.tag)
  • Rows Removed by Join Filter: 557682
  • Buffers: shared hit=605841 dirtied=18
  • Worker 0: actual time=0.273..665.275 rows=146494 loops=1
  • Buffers: shared hit=360215 dirtied=17
20. 0.132 10.203 ↑ 1.4 258 2 / 2

Nested Loop (cost=0.28..4,154.77 rows=351 width=47) (actual time=0.021..10.203 rows=258 loops=2)

  • Output: email_campaigns_1.id, email_campaigns_1.brand, email_campaigns_recipients_1.send_type, email_campaigns_recipients_1.tag
  • Inner Unique: true
  • Buffers: shared hit=3969
  • Worker 0: actual time=0.026..8.081 rows=294 loops=1
  • Buffers: shared hit=1776
21. 9.298 9.298 ↑ 1.4 258 2 / 2

Parallel Seq Scan on public.email_campaigns_recipients email_campaigns_recipients_1 (cost=0.00..3,775.44 rows=351 width=31) (actual time=0.012..9.298 rows=258 loops=2)

  • Output: email_campaigns_recipients_1.id, email_campaigns_recipients_1.created_at, email_campaigns_recipients_1.updated_at, email_campaigns_recipients_1.deleted_at, email_campaigns_recipients_1.campaign, email_campaigns_recipients_1.tag, email_campaigns_recipients_1.list, email_campaigns_recipients_1.contact, email_campaigns_recipients_1.email, email_campaigns_recipients_1.send_type, email_campaigns_recipients_1.recipient_type, email_campaigns_recipients_1.brand, email_campaigns_recipients_1.agent
  • Filter: (email_campaigns_recipients_1.recipient_type = 'Tag'::email_campaign_recipient_type)
  • Rows Removed by Filter: 92081
  • Buffers: shared hit=2419
  • Worker 0: actual time=0.018..7.023 rows=294 loops=1
  • Buffers: shared hit=893
22. 0.772 0.772 ↑ 1.0 1 515 / 2

Index Scan using email_campaigns_pkey on public.email_campaigns email_campaigns_1 (cost=0.28..1.08 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=515)

  • Output: email_campaigns_1.id, email_campaigns_1.created_at, email_campaigns_1.updated_at, email_campaigns_1.deleted_at, email_campaigns_1.created_by, email_campaigns_1.brand, email_campaigns_1.subject, email_campaigns_1.include_signature, email_campaigns_1.html, email_campaigns_1.due_at, email_campaigns_1.executed_at, email_campaigns_1."from", email_campaigns_1.individual, email_campaigns_1.accepted, email_campaigns_1.rejected, email_campaigns_1.delivered, email_campaigns_1.failed, email_campaigns_1.opened, email_campaigns_1.clicked, email_campaigns_1.unsubscribed, email_campaigns_1.complained, email_campaigns_1.stored, email_campaigns_1.template, email_campaigns_1.deal, email_campaigns_1.text, email_campaigns_1.headers, email_campaigns_1.google_credential, email_campaigns_1.microsoft_credential, email_campaigns_1.thread_key
  • Index Cond: (email_campaigns_1.id = email_campaigns_recipients_1.campaign)
  • Buffers: shared hit=1550
  • Worker 0: actual time=0.003..0.003 rows=1 loops=294
  • Buffers: shared hit=883
23. 389.083 389.083 ↓ 2.4 2,717 515 / 2

Index Scan using contacts_brand_idx on public.contacts contacts_1 (cost=0.42..33.33 rows=1,110 width=119) (actual time=0.005..1.511 rows=2,717 loops=515)

  • Output: contacts_1.email, contacts_1.id, contacts_1.tag, contacts_1.brand
  • Index Cond: (contacts_1.brand = email_campaigns_1.brand)
  • Buffers: shared hit=601758 dirtied=18
  • Worker 0: actual time=0.005..1.514 rows=2764 loops=294
  • Buffers: shared hit=358325 dirtied=17
24. 34.121 262.935 ↑ 1.0 216,867 1

Nested Loop (cost=0.70..12,027.55 rows=217,698 width=84) (actual time=0.033..262.935 rows=216,867 loops=1)

  • Output: email_campaigns_2.id, contacts_2.email[1], contacts_2.id, NULL::uuid, email_campaigns_recipients_2.send_type
  • Buffers: shared hit=115423
25. 0.148 18.934 ↑ 1.0 165 1

Nested Loop (cost=0.28..4,959.55 rows=166 width=36) (actual time=0.019..18.934 rows=165 loops=1)

  • Output: email_campaigns_2.id, email_campaigns_2.brand, email_campaigns_recipients_2.send_type
  • Inner Unique: true
  • Buffers: shared hit=2915
26. 18.291 18.291 ↑ 1.0 165 1

Seq Scan on public.email_campaigns_recipients email_campaigns_recipients_2 (cost=0.00..4,724.95 rows=166 width=20) (actual time=0.009..18.291 rows=165 loops=1)

  • Output: email_campaigns_recipients_2.id, email_campaigns_recipients_2.created_at, email_campaigns_recipients_2.updated_at, email_campaigns_recipients_2.deleted_at, email_campaigns_recipients_2.campaign, email_campaigns_recipients_2.tag, email_campaigns_recipients_2.list, email_campaigns_recipients_2.contact, email_campaigns_recipients_2.email, email_campaigns_recipients_2.send_type, email_campaigns_recipients_2.recipient_type, email_campaigns_recipients_2.brand, email_campaigns_recipients_2.agent
  • Filter: (email_campaigns_recipients_2.recipient_type = 'AllContacts'::email_campaign_recipient_type)
  • Rows Removed by Filter: 184512
  • Buffers: shared hit=2419
27. 0.495 0.495 ↑ 1.0 1 165

Index Scan using email_campaigns_pkey on public.email_campaigns email_campaigns_2 (cost=0.28..1.41 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=165)

  • Output: email_campaigns_2.id, email_campaigns_2.created_at, email_campaigns_2.updated_at, email_campaigns_2.deleted_at, email_campaigns_2.created_by, email_campaigns_2.brand, email_campaigns_2.subject, email_campaigns_2.include_signature, email_campaigns_2.html, email_campaigns_2.due_at, email_campaigns_2.executed_at, email_campaigns_2."from", email_campaigns_2.individual, email_campaigns_2.accepted, email_campaigns_2.rejected, email_campaigns_2.delivered, email_campaigns_2.failed, email_campaigns_2.opened, email_campaigns_2.clicked, email_campaigns_2.unsubscribed, email_campaigns_2.complained, email_campaigns_2.stored, email_campaigns_2.template, email_campaigns_2.deal, email_campaigns_2.text, email_campaigns_2.headers, email_campaigns_2.google_credential, email_campaigns_2.microsoft_credential, email_campaigns_2.thread_key
  • Index Cond: (email_campaigns_2.id = email_campaigns_recipients_2.campaign)
  • Buffers: shared hit=496
28. 209.880 209.880 ↓ 3.6 1,314 165

Index Scan using contacts_brand_idx on public.contacts contacts_2 (cost=0.42..38.88 rows=370 width=82) (actual time=0.015..1.272 rows=1,314 loops=165)

  • Output: contacts_2.email, contacts_2.id, contacts_2.brand
  • Index Cond: (contacts_2.brand = email_campaigns_2.brand)
  • Filter: (length(contacts_2.email[1]) > 0)
  • Rows Removed by Filter: 385
  • Buffers: shared hit=112508
29. 30.014 148.574 ↓ 1.0 93,246 1

Hash Join (cost=11,018.86..16,228.33 rows=92,250 width=75) (actual time=83.233..148.574 rows=93,246 loops=1)

  • Output: email_campaigns_3.id, agents.email, NULL::uuid, email_campaigns_recipients_3.agent, email_campaigns_recipients_3.send_type
  • Inner Unique: true
  • Hash Cond: (email_campaigns_recipients_3.agent = agents.id)
  • Buffers: shared hit=8393 dirtied=161
30. 17.176 43.144 ↓ 1.0 93,246 1

Hash Join (cost=722.80..5,690.12 rows=92,250 width=36) (actual time=7.611..43.144 rows=93,246 loops=1)

  • Output: email_campaigns_3.id, email_campaigns_recipients_3.agent, email_campaigns_recipients_3.send_type
  • Inner Unique: true
  • Hash Cond: (email_campaigns_recipients_3.campaign = email_campaigns_3.id)
  • Buffers: shared hit=3034
31. 24.264 24.264 ↓ 1.0 93,246 1

Seq Scan on public.email_campaigns_recipients email_campaigns_recipients_3 (cost=0.00..4,724.95 rows=92,250 width=36) (actual time=5.890..24.264 rows=93,246 loops=1)

  • Output: email_campaigns_recipients_3.id, email_campaigns_recipients_3.created_at, email_campaigns_recipients_3.updated_at, email_campaigns_recipients_3.deleted_at, email_campaigns_recipients_3.campaign, email_campaigns_recipients_3.tag, email_campaigns_recipients_3.list, email_campaigns_recipients_3.contact, email_campaigns_recipients_3.email, email_campaigns_recipients_3.send_type, email_campaigns_recipients_3.recipient_type, email_campaigns_recipients_3.brand, email_campaigns_recipients_3.agent
  • Filter: (email_campaigns_recipients_3.recipient_type = 'Agent'::email_campaign_recipient_type)
  • Rows Removed by Filter: 91431
  • Buffers: shared hit=2419
32. 0.730 1.704 ↓ 1.0 4,792 1

Hash (cost=662.91..662.91 rows=4,791 width=16) (actual time=1.704..1.704 rows=4,792 loops=1)

  • Output: email_campaigns_3.id
  • Buckets: 8192 Batches: 1 Memory Usage: 289kB
  • Buffers: shared hit=615
33. 0.974 0.974 ↓ 1.0 4,792 1

Seq Scan on public.email_campaigns email_campaigns_3 (cost=0.00..662.91 rows=4,791 width=16) (actual time=0.005..0.974 rows=4,792 loops=1)

  • Output: email_campaigns_3.id
  • Buffers: shared hit=615
34. 41.597 75.416 ↑ 1.0 215,209 1

Hash (cost=7,553.25..7,553.25 rows=219,425 width=39) (actual time=75.416..75.416 rows=215,209 loops=1)

  • Output: agents.email, agents.id
  • Buckets: 262144 Batches: 1 Memory Usage: 14400kB
  • Buffers: shared hit=5359 dirtied=161
35. 33.819 33.819 ↑ 1.0 215,209 1

Seq Scan on public.agents (cost=0.00..7,553.25 rows=219,425 width=39) (actual time=0.007..33.819 rows=215,209 loops=1)

  • Output: agents.email, agents.id
  • Buffers: shared hit=5359 dirtied=161
36. 0.003 0.052 ↓ 3.0 3 1

Hash (cost=33.38..33.38 rows=1 width=32) (actual time=0.052..0.052 rows=3 loops=1)

  • Output: ec.id, ec.brand
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=25
37. 0.042 0.049 ↓ 3.0 3 1

Bitmap Heap Scan on public.email_campaigns ec (cost=1.60..33.38 rows=1 width=32) (actual time=0.040..0.049 rows=3 loops=1)

  • Output: ec.id, ec.brand
  • Recheck Cond: (ec.brand = 'fca6efa6-2f91-11ea-b7a0-0a653b6fef3e'::uuid)
  • Filter: ((ec.deleted_at IS NULL) AND (ec.executed_at IS NULL) AND (ec.due_at IS NOT NULL) AND (ec.due_at >= '2020-01-07 20:29:59.79+00'::timestamp with time zone) AND (ec.due_at <= '2020-03-31 23:59:59.999+00'::timestamp with time zone))
  • Rows Removed by Filter: 26
  • Heap Blocks: exact=23
  • Buffers: shared hit=25
38. 0.007 0.007 ↑ 1.0 29 1

Bitmap Index Scan on email_campaigns_brand_idx (cost=0.00..1.60 rows=29 width=0) (actual time=0.007..0.007 rows=29 loops=1)

  • Index Cond: (ec.brand = 'fca6efa6-2f91-11ea-b7a0-0a653b6fef3e'::uuid)
  • Buffers: shared hit=2
Planning time : 2.776 ms
Execution time : 1,801.876 ms