explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tT7Z

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,700.483 ↓ 0.0 0 1

Nested Loop (cost=572,260.04..610,952.76 rows=17 width=32) (actual time=2,700.483..2,700.483 rows=0 loops=1)

  • Output: (ec.id)::text
  • Join Filter: (ecr.email = ANY (c.email))
  • Buffers: shared hit=1,562,859
2. 0.031 0.031 ↑ 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.027..0.031 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. 2.540 2,700.449 ↓ 0.0 0 1

Hash Join (cost=572,259.61..610,942.09 rows=353 width=64) (actual time=2,700.449..2,700.449 rows=0 loops=1)

  • Output: ec.id, ec.brand, ecr.email
  • Inner Unique: true
  • Hash Cond: (ecr.campaign = ec.id)
  • Buffers: shared hit=1,562,855
4. 33.049 2,697.851 ↑ 50.8 33,639 1

HashAggregate (cost=572,226.23..589,321.72 rows=1,709,549 width=84) (actual time=2,684.359..2,697.851 rows=33,639 loops=1)

  • Output: ecr.campaign, ecr.email, c_1.id, (NULL::uuid), ecr.send_type
  • Group Key: ecr.campaign, ecr.email, c_1.id, (NULL::uuid), ecr.send_type
  • Buffers: shared hit=1,562,830
5. 0.000 2,664.802 ↑ 50.6 33,811 1

Gather (cost=1,001.13..550,856.86 rows=1,709,549 width=84) (actual time=1.238..2,664.802 rows=33,811 loops=1)

  • Output: ecr.campaign, ecr.email, c_1.id, (NULL::uuid), ecr.send_type
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1,562,830
6. 1.228 2,669.680 ↑ 107.4 11,270 3 / 3

Parallel Append (cost=1.13..378,901.96 rows=1,210,931 width=84) (actual time=1.118..2,669.680 rows=11,270 loops=3)

  • Buffers: shared hit=1,562,830
  • Worker 0: actual time=0.159..2674.689 rows=14,097 loops=1
  • Buffers: shared hit=638,910
  • Worker 1: actual time=2.741..2678.123 rows=2,563 loops=1
  • Buffers: shared hit=335,917
7. 35.637 2,570.412 ↑ 413.5 2,432 3 / 3

Nested Loop (cost=721.06..349,417.46 rows=1,005,616 width=76) (actual time=2.714..2,570.412 rows=2,432 loops=3)

  • Output: ecr.campaign, ecr.email, c_1.id, NULL::uuid, ecr.send_type
  • Buffers: shared hit=1,013,860
  • Worker 0: actual time=2.854..2531.346 rows=2,569 loops=1
  • Buffers: shared hit=364,481
  • Worker 1: actual time=2.740..2677.836 rows=2,563 loops=1
  • Buffers: shared hit=335,917
8. 7.936 21.089 ↓ 1.1 28,244 3 / 3

Hash Join (cost=720.08..4,562.78 rows=25,601 width=60) (actual time=2.328..21.089 rows=28,244 loops=3)

  • Output: ec_1.brand, ecr.campaign, ecr.email, ecr.send_type
  • Inner Unique: true
  • Hash Cond: (ecr.campaign = ec_1.id)
  • Buffers: shared hit=4,308
  • Worker 0: actual time=2.224..21.564 rows=30,580 loops=1
  • Buffers: shared hit=1,598
  • Worker 1: actual time=2.431..21.201 rows=27,886 loops=1
  • Buffers: shared hit=1,375
9. 10.921 10.921 ↓ 1.1 28,244 3 / 3

Parallel Seq Scan on public.email_campaigns_recipients ecr (cost=0.00..3,775.44 rows=25,601 width=44) (actual time=0.007..10.921 rows=28,244 loops=3)

  • 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.contact IS NULL) AND (ecr.deleted_at IS NULL) AND (ecr.recipient_type = 'Email'::email_campaign_recipient_type))
  • Rows Removed by Filter: 33,314
  • Buffers: shared hit=2,419
  • Worker 0: actual time=0.004..11.508 rows=30,580 loops=1
  • Buffers: shared hit=959
  • Worker 1: actual time=0.011..10.674 rows=27,886 loops=1
  • Buffers: shared hit=736
10. 1.019 2.232 ↑ 1.0 4,789 3 / 3

Hash (cost=659.48..659.48 rows=4,848 width=32) (actual time=2.231..2.232 rows=4,789 loops=3)

  • Output: ec_1.id, ec_1.brand
  • Buckets: 8,192 Batches: 1 Memory Usage: 364kB
  • Buffers: shared hit=1,833
  • Worker 0: actual time=2.092..2.092 rows=4,789 loops=1
  • Buffers: shared hit=611
  • Worker 1: actual time=2.330..2.330 rows=4,789 loops=1
  • Buffers: shared hit=611
11. 1.213 1.213 ↑ 1.0 4,789 3 / 3

Seq Scan on public.email_campaigns ec_1 (cost=0.00..659.48 rows=4,848 width=32) (actual time=0.010..1.213 rows=4,789 loops=3)

  • Output: ec_1.id, ec_1.brand
  • Buffers: shared hit=1,833
  • Worker 0: actual time=0.018..1.161 rows=4,789 loops=1
  • Buffers: shared hit=611
  • Worker 1: actual time=0.007..1.289 rows=4,789 loops=1
  • Buffers: shared hit=611
12. 0.000 2,513.686 ↓ 0.0 0 84,731 / 3

Bitmap Heap Scan on public.contacts c_1 (cost=0.98..13.36 rows=11 width=82) (actual time=0.089..0.089 rows=0 loops=84,731)

  • Output: c_1.id, c_1.email, c_1.brand
  • Recheck Cond: ((c_1.brand = ec_1.brand) AND (c_1.email && ARRAY[ecr.email]))
  • Filter: ((c_1.deleted_at IS NULL) AND (c_1.id IS NOT NULL))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2,371
  • Buffers: shared hit=1,009,552
  • Worker 0: actual time=0.081..0.081 rows=0 loops=30,580
  • Buffers: shared hit=362,883
  • Worker 1: actual time=0.094..0.094 rows=0 loops=27,886
  • Buffers: shared hit=334,542
13. 2,513.686 2,513.686 ↓ 0.0 0 84,731 / 3

Bitmap Index Scan on contacts_brand_email_idx (cost=0.00..0.97 rows=20 width=0) (actual time=0.089..0.089 rows=0 loops=84,731)

  • Index Cond: ((c_1.brand = ec_1.brand) AND (c_1.email && ARRAY[ecr.email]))
  • Buffers: shared hit=1,001,579
  • Worker 0: actual time=0.081..0.081 rows=0 loops=30,580
  • Buffers: shared hit=360,091
  • Worker 1: actual time=0.094..0.094 rows=0 loops=27,886
  • Buffers: shared hit=331,732
14. 16.919 98.040 ↓ 13,258.0 13,258 2 / 3

Nested Loop (cost=1.13..6,334.36 rows=1 width=84) (actual time=0.306..147.060 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: 177,802
  • Buffers: shared hit=548,970
  • Worker 0: actual time=0.159..142.009 rows=11,528 loops=1
  • Buffers: shared hit=274,429
15. 0.000 36.928 ↓ 4,419.3 13,258 2 / 3

Nested Loop (cost=0.85..6,332.16 rows=3 width=102) (actual time=0.274..55.392 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=119,120
  • Worker 0: actual time=0.131..54.849 rows=11,528 loops=1
  • Buffers: shared hit=53,121
16. 1.223 10.483 ↓ 2,651.6 13,258 2 / 3

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

  • Output: email_campaigns_recipients.send_type, email_campaigns_recipients.campaign, crm_lists_members.contact
  • Buffers: shared hit=12,967
  • Worker 0: actual time=0.118..22.005 rows=11,528 loops=1
  • Buffers: shared hit=6,959
17. 5.901 5.901 ↑ 1.1 36 2 / 3

Parallel Seq Scan on public.email_campaigns_recipients (cost=0.00..3,775.44 rows=40 width=36) (actual time=0.239..8.851 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: 92,301
  • Buffers: shared hit=2,419
  • Worker 0: actual time=0.100..16.666 rows=30 loops=1
  • Buffers: shared hit=2,296
18. 3.360 3.360 ↑ 1.0 368 72 / 3

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,548
  • Worker 0: actual time=0.005..0.135 rows=384 loops=30
  • Buffers: shared hit=4,663
19. 26.516 26.516 ↑ 1.0 1 26,516 / 3

Index Scan using contacts_pkey on public.contacts (cost=0.42..0.88 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) AND (contacts.id IS NOT NULL))
  • Filter: (contacts.deleted_at IS NULL)
  • Buffers: shared hit=106,153
  • Worker 0: actual time=0.003..0.003 rows=1 loops=11,528
  • Buffers: shared hit=46,162
20. 44.193 44.193 ↑ 1.1 14 26,516 / 3

Index Scan using email_campaigns_brand_idx on public.email_campaigns (cost=0.28..0.55 rows=15 width=32) (actual time=0.002..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=429,850
  • Worker 0: actual time=0.001..0.006 rows=18 loops=11,528
  • Buffers: shared hit=221,308
21. 0.010 0.058 ↓ 3.0 3 1

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

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

Bitmap Heap Scan on public.email_campaigns ec (cost=1.60..33.38 rows=1 width=32) (actual time=0.041..0.048 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
23. 0.013 0.013 ↑ 1.0 29 1

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

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