explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dI38

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 4,288.800 ↑ 7.0 1 1

Nested Loop (cost=111,321.51..127,557.78 rows=7 width=32) (actual time=4,031.072..4,288.800 rows=1 loops=1)

  • Output: (ec.id)::text
  • Join Filter: (ecr.email = ANY (c.email))
  • Rows Removed by Join Filter: 2
  • Buffers: shared hit=2,807,350 dirtied=2
2. 0.020 0.020 ↑ 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.015..0.020 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. 60.389 4,288.767 ↑ 49.0 3 1

Hash Join (cost=111,321.09..127,551.80 rows=147 width=64) (actual time=4,031.050..4,288.767 rows=3 loops=1)

  • Output: ec.id, ec.brand, ecr.email
  • Inner Unique: true
  • Hash Cond: (ecr.campaign = ec.id)
  • Buffers: shared hit=2,807,346 dirtied=2
4. 665.035 4,228.336 ↓ 1.2 826,816 1

HashAggregate (cost=111,286.61..118,459.68 rows=717,307 width=84) (actual time=4,012.138..4,228.336 rows=826,816 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=2,807,321 dirtied=2
5. 75.282 3,563.301 ↓ 1.3 920,913 1

Append (cost=722.44..102,320.27 rows=717,307 width=84) (actual time=1.761..3,563.301 rows=920,913 loops=1)

  • Buffers: shared hit=2,807,321 dirtied=2
6. 15.848 40.731 ↓ 2.0 90,390 1

Hash Join (cost=722.44..5,565.44 rows=44,934 width=76) (actual time=1.760..40.731 rows=90,390 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=3,032
7. 23.142 23.142 ↓ 2.0 90,390 1

Seq Scan on public.email_campaigns_recipients ecr (cost=0.00..4,724.95 rows=44,934 width=44) (actual time=0.008..23.142 rows=90,390 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: 94,284
  • Buffers: shared hit=2,419
8. 0.737 1.741 ↑ 1.0 4,789 1

Hash (cost=661.64..661.64 rows=4,864 width=16) (actual time=1.740..1.741 rows=4,789 loops=1)

  • Output: ec_1.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 289kB
  • Buffers: shared hit=613
9. 1.004 1.004 ↑ 1.0 4,789 1

Seq Scan on public.email_campaigns ec_1 (cost=0.00..661.64 rows=4,864 width=16) (actual time=0.002..1.004 rows=4,789 loops=1)

  • Output: ec_1.id
  • Buffers: shared hit=613
10. 27.730 272.096 ↓ 26,516.0 26,516 1

Nested Loop (cost=1.13..9,075.46 rows=1 width=84) (actual time=0.440..272.096 rows=26,516 loops=1)

  • 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: 354,070
  • Buffers: shared hit=541,813
11. 1.071 111.786 ↓ 5,303.2 26,516 1

Nested Loop (cost=0.85..9,071.79 rows=5 width=102) (actual time=0.408..111.786 rows=26,516 loops=1)

  • Output: email_campaigns_recipients.send_type, email_campaigns_recipients.campaign, contacts.email, contacts.id, contacts.brand
  • Inner Unique: true
  • Buffers: shared hit=119,118
12. 3.151 31.167 ↓ 2,946.2 26,516 1

Nested Loop (cost=0.42..9,063.92 rows=9 width=36) (actual time=0.397..31.167 rows=26,516 loops=1)

  • Output: email_campaigns_recipients.send_type, email_campaigns_recipients.campaign, crm_lists_members.contact
  • Buffers: shared hit=12,966
13. 17.936 17.936 ↓ 1.1 72 1

Seq Scan on public.email_campaigns_recipients (cost=0.00..4,724.95 rows=68 width=36) (actual time=0.376..17.936 rows=72 loops=1)

  • 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: 184,602
  • Buffers: shared hit=2,419
14. 10.080 10.080 ↑ 1.0 368 72

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.140 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=10,547
15. 79.548 79.548 ↑ 1.0 1 26,516

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=106,152
16. 132.580 132.580 ↑ 1.1 14 26,516

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=422,695
17. 347.036 1,187.893 ↓ 24.2 283,877 1

Nested Loop (cost=0.70..33,360.65 rows=11,709 width=84) (actual time=0.045..1,187.893 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
  • Join Filter: (ARRAY[email_campaigns_recipients_1.tag] <@ contacts_1.tag)
  • Rows Removed by Join Filter: 1,115,355
  • Buffers: shared hit=605,680 dirtied=1
18. 0.693 19.947 ↑ 1.2 515 1

Nested Loop (cost=0.28..5,369.05 rows=596 width=47) (actual time=0.021..19.947 rows=515 loops=1)

  • 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=3,968
19. 18.224 18.224 ↑ 1.2 515 1

Seq Scan on public.email_campaigns_recipients email_campaigns_recipients_1 (cost=0.00..4,724.95 rows=596 width=31) (actual time=0.010..18.224 rows=515 loops=1)

  • 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: 184,159
  • Buffers: shared hit=2,419
20. 1.030 1.030 ↑ 1.0 1 515

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.002..0.002 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=1,549
21. 820.910 820.910 ↓ 2.4 2,717 515

Index Scan using contacts_brand_idx on public.contacts contacts_1 (cost=0.42..33.10 rows=1,109 width=119) (actual time=0.005..1.594 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=601,712 dirtied=1
22. 35.551 255.503 ↑ 1.0 216,930 1

Nested Loop (cost=0.70..11,989.36 rows=217,413 width=84) (actual time=0.033..255.503 rows=216,930 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=115,479
23. 0.139 18.817 ↑ 1.0 165 1

Nested Loop (cost=0.28..4,959.55 rows=166 width=36) (actual time=0.018..18.817 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=2,915
24. 18.183 18.183 ↑ 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.183 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: 184,509
  • Buffers: shared hit=2,419
25. 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
26. 201.135 201.135 ↓ 3.6 1,315 165

Index Scan using contacts_brand_idx on public.contacts contacts_2 (cost=0.42..38.65 rows=370 width=82) (actual time=0.026..1.219 rows=1,315 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=112,564
27. 45.059 1,582.483 ↑ 1.7 209,954 1

Hash Join (cost=2,246.49..15,341.77 rows=351,000 width=81) (actual time=10.699..1,582.483 rows=209,954 loops=1)

  • Output: ec_2.id, u.email, NULL::uuid, NULL::uuid, ecr_1.send_type
  • Inner Unique: true
  • Hash Cond: (ba."user" = u.id)
  • Buffers: shared hit=1,532,926 dirtied=1
28. 24.413 1,534.115 ↑ 1.7 209,954 1

Nested Loop (cost=0.53..12,173.90 rows=351,000 width=36) (actual time=7.377..1,534.115 rows=209,954 loops=1)

  • Output: ec_2.id, ecr_1.send_type, ba."user
  • Buffers: shared hit=1,530,840 dirtied=1
29. 0.424 21.358 ↑ 1.2 286 1

Nested Loop (cost=0.28..5,153.65 rows=351 width=36) (actual time=0.019..21.358 rows=286 loops=1)

  • Output: ec_2.id, ecr_1.send_type, ecr_1.brand
  • Inner Unique: true
  • Buffers: shared hit=3,278
30. 19.790 19.790 ↑ 1.2 286 1

Seq Scan on public.email_campaigns_recipients ecr_1 (cost=0.00..4,724.95 rows=351 width=36) (actual time=0.009..19.790 rows=286 loops=1)

  • Output: ecr_1.id, ecr_1.created_at, ecr_1.updated_at, ecr_1.deleted_at, ecr_1.campaign, ecr_1.tag, ecr_1.list, ecr_1.contact, ecr_1.email, ecr_1.send_type, ecr_1.recipient_type, ecr_1.brand, ecr_1.agent
  • Filter: ((ecr_1.deleted_at IS NULL) AND (ecr_1.recipient_type = 'Brand'::email_campaign_recipient_type))
  • Rows Removed by Filter: 184,388
  • Buffers: shared hit=2,419
31. 1.144 1.144 ↑ 1.0 1 286

Index Only Scan using email_campaigns_pkey on public.email_campaigns ec_2 (cost=0.28..1.22 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=286)

  • Output: ec_2.id
  • Index Cond: (ec_2.id = ecr_1.campaign)
  • Heap Fetches: 286
  • Buffers: shared hit=859
32. 1,488.344 1,488.344 ↑ 1.4 734 286

Function Scan on public.get_brand_agents ba (cost=0.25..10.25 rows=1,000 width=16) (actual time=5.158..5.204 rows=734 loops=286)

  • Output: ba."user", ba.agent, ba.mui, ba.mls, ba.brand_user, ba.brand_role, ba.brand, ba.enabled
  • Function Call: get_brand_agents(ecr_1.brand)
  • Buffers: shared hit=1,527,562 dirtied=1
33. 1.047 3.309 ↓ 1.0 7,111 1

Hash (cost=2,157.09..2,157.09 rows=7,109 width=45) (actual time=3.309..3.309 rows=7,111 loops=1)

  • Output: u.email, u.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 602kB
  • Buffers: shared hit=2,086
34. 2.262 2.262 ↓ 1.0 7,111 1

Seq Scan on public.users u (cost=0.00..2,157.09 rows=7,109 width=45) (actual time=0.004..2.262 rows=7,111 loops=1)

  • Output: u.email, u.id
  • Buffers: shared hit=2,086
35. 29.653 149.313 ↓ 1.0 93,246 1

Hash Join (cost=11,018.50..16,227.98 rows=92,250 width=75) (actual time=81.739..149.313 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=8,391
36. 18.776 45.667 ↓ 1.0 93,246 1

Hash Join (cost=722.44..5,689.76 rows=92,250 width=36) (actual time=7.565..45.667 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=3,032
37. 25.271 25.271 ↓ 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.933..25.271 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: 91,428
  • Buffers: shared hit=2,419
38. 0.702 1.620 ↑ 1.0 4,789 1

Hash (cost=661.64..661.64 rows=4,864 width=16) (actual time=1.620..1.620 rows=4,789 loops=1)

  • Output: email_campaigns_3.id
  • Buckets: 8,192 Batches: 1 Memory Usage: 289kB
  • Buffers: shared hit=613
39. 0.918 0.918 ↑ 1.0 4,789 1

Seq Scan on public.email_campaigns email_campaigns_3 (cost=0.00..661.64 rows=4,864 width=16) (actual time=0.005..0.918 rows=4,789 loops=1)

  • Output: email_campaigns_3.id
  • Buffers: shared hit=613
40. 42.696 73.993 ↑ 1.0 215,204 1

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

  • Output: agents.email, agents.id
  • Buckets: 262,144 Batches: 1 Memory Usage: 14,400kB
  • Buffers: shared hit=5,359
41. 31.297 31.297 ↑ 1.0 215,204 1

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

  • Output: agents.email, agents.id
  • Buffers: shared hit=5,359
42. 0.002 0.042 ↓ 3.0 3 1

Hash (cost=34.47..34.47 rows=1 width=32) (actual time=0.042..0.042 rows=3 loops=1)

  • Output: ec.id, ec.brand
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=25
43. 0.031 0.040 ↓ 3.0 3 1

Bitmap Heap Scan on public.email_campaigns ec (cost=1.61..34.47 rows=1 width=32) (actual time=0.033..0.040 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
44. 0.009 0.009 ↑ 1.0 29 1

Bitmap Index Scan on email_campaigns_brand_idx (cost=0.00..1.61 rows=30 width=0) (actual time=0.009..0.009 rows=29 loops=1)

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