explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XPPu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 163.602 ↓ 0.0 0 1

Update on email_campaigns (cost=6,582,741.77..6,582,744.01 rows=1 width=902) (actual time=163.602..163.602 rows=0 loops=1)

2.          

CTE events

3. 46.409 101.106 ↑ 1.2 15,517 1

Hash Join (cost=14,752.97..20,854.59 rows=18,859 width=60) (actual time=86.240..101.106 rows=15,517 loops=1)

  • Hash Cond: (emails_events.email = emails.id)
4. 38.568 38.568 ↑ 1.0 155,174 1

Seq Scan on emails_events (cost=0.00..5,680.65 rows=160,365 width=44) (actual time=0.006..38.568 rows=155,174 loops=1)

5. 4.431 16.129 ↑ 1.1 12,797 1

Hash (cost=14,577.96..14,577.96 rows=14,001 width=32) (actual time=16.129..16.129 rows=12,797 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 928kB
6. 10.691 11.698 ↑ 1.1 12,797 1

Bitmap Heap Scan on emails (cost=1,159.43..14,577.96 rows=14,001 width=32) (actual time=1.310..11.698 rows=12,797 loops=1)

  • Recheck Cond: (campaign = 'b12623d0-9a90-11e9-bc7e-0a95998482ac'::uuid)
  • Heap Blocks: exact=2512
7. 1.007 1.007 ↑ 1.1 12,834 1

Bitmap Index Scan on emails_campaign (cost=0.00..1,155.92 rows=14,001 width=0) (actual time=1.006..1.007 rows=12,834 loops=1)

  • Index Cond: (campaign = 'b12623d0-9a90-11e9-bc7e-0a95998482ac'::uuid)
8.          

CTE recipient_counts

9. 13.046 17.070 ↓ 7.4 13,954 1

HashAggregate (cost=518.62..537.48 rows=1,886 width=44) (actual time=12.729..17.070 rows=13,954 loops=1)

  • Group Key: events.event, events.recipient
10. 4.024 4.024 ↑ 1.2 15,517 1

CTE Scan on events (cost=0.00..377.18 rows=18,859 width=36) (actual time=0.001..4.024 rows=15,517 loops=1)

11.          

CTE update_recipients

12. 210.775 173,390.669 ↓ 0.0 0 1

Update on email_campaign_emails ece (cost=6,455.59..601,307.80 rows=1,401 width=171) (actual time=173,390.669..173,390.669 rows=0 loops=1)

13. 108.717 173,179.894 ↓ 10.0 13,951 1

Hash Join (cost=6,455.59..601,307.80 rows=1,401 width=171) (actual time=46.105..173,179.894 rows=13,951 loops=1)

  • Hash Cond: (rc_9.recipient = ece.email_address)
14. 25.629 25.629 ↓ 7.4 13,954 1

CTE Scan on recipient_counts rc_9 (cost=0.00..37.72 rows=1,886 width=88) (actual time=12.742..25.629 rows=13,954 loops=1)

15. 5.914 11.295 ↓ 1.0 12,797 1

Hash (cost=6,296.32..6,296.32 rows=12,741 width=97) (actual time=11.295..11.295 rows=12,797 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1755kB
16. 5.381 5.381 ↓ 1.0 12,797 1

Index Scan using email_campaign_emails_campaign_idx on email_campaign_emails ece (cost=0.41..6,296.32 rows=12,741 width=97) (actual time=0.019..5.381 rows=12,797 loops=1)

  • Index Cond: (campaign = 'b12623d0-9a90-11e9-bc7e-0a95998482ac'::uuid)
17.          

SubPlan (for Hash Join)

18. 19,196.576 19,196.576 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc (cost=0.00..47.15 rows=1 width=8) (actual time=1.376..1.376 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'accepted'::email_event))
  • Rows Removed by Filter: 13954
19. 19,168.674 19,168.674 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_1 (cost=0.00..47.15 rows=1 width=8) (actual time=1.374..1.374 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'rejected'::email_event))
  • Rows Removed by Filter: 13954
20. 19,210.527 19,210.527 ↑ 1.0 1 13,951

CTE Scan on recipient_counts rc_2 (cost=0.00..47.15 rows=1 width=8) (actual time=0.748..1.377 rows=1 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'delivered'::email_event))
  • Rows Removed by Filter: 13953
21. 19,196.576 19,196.576 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_3 (cost=0.00..47.15 rows=1 width=8) (actual time=1.258..1.376 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'failed'::email_event))
  • Rows Removed by Filter: 13954
22. 19,489.547 19,489.547 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_4 (cost=0.00..47.15 rows=1 width=8) (actual time=1.271..1.397 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'opened'::email_event))
  • Rows Removed by Filter: 13954
23. 19,210.527 19,210.527 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_5 (cost=0.00..47.15 rows=1 width=8) (actual time=1.366..1.377 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'clicked'::email_event))
  • Rows Removed by Filter: 13954
24. 19,182.625 19,182.625 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_6 (cost=0.00..47.15 rows=1 width=8) (actual time=1.358..1.375 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'unsubscribed'::email_event))
  • Rows Removed by Filter: 13954
25. 19,182.625 19,182.625 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_7 (cost=0.00..47.15 rows=1 width=8) (actual time=1.374..1.375 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'complained'::email_event))
  • Rows Removed by Filter: 13954
26. 19,196.576 19,196.576 ↓ 0.0 0 13,951

CTE Scan on recipient_counts rc_8 (cost=0.00..47.15 rows=1 width=8) (actual time=1.376..1.376 rows=0 loops=13,951)

  • Filter: ((ece.email_address = recipient) AND (event = 'stored'::email_event))
  • Rows Removed by Filter: 13954
27.          

CTE email_counts

28. 21.930 41.310 ↓ 7.4 13,953 1

GroupAggregate (cost=1,716.45..1,923.90 rows=1,886 width=28) (actual time=15.651..41.310 rows=13,953 loops=1)

  • Group Key: events_1.event, events_1.email
29. 14.929 19.380 ↑ 1.2 15,517 1

Sort (cost=1,716.45..1,763.60 rows=18,859 width=20) (actual time=15.641..19.380 rows=15,517 loops=1)

  • Sort Key: events_1.event, events_1.email
  • Sort Method: quicksort Memory: 1597kB
30. 4.451 4.451 ↑ 1.2 15,517 1

CTE Scan on events events_1 (cost=0.00..377.18 rows=18,859 width=20) (actual time=0.001..4.451 rows=15,517 loops=1)

31.          

CTE update_emails

32. 418.383 131,206.236 ↓ 0.0 0 1

Update on emails emails_1 (cost=1,159.43..5,956,217.33 rows=14,001 width=1,460) (actual time=131,206.236..131,206.236 rows=0 loops=1)

33. 91.221 130,787.853 ↑ 1.1 12,797 1

Bitmap Heap Scan on emails emails_1 (cost=1,159.43..5,956,217.33 rows=14,001 width=1,460) (actual time=58.050..130,787.853 rows=12,797 loops=1)

  • Recheck Cond: (campaign = 'b12623d0-9a90-11e9-bc7e-0a95998482ac'::uuid)
  • Heap Blocks: exact=2512
34. 0.871 0.871 ↑ 1.1 12,834 1

Bitmap Index Scan on emails_campaign (cost=0.00..1,155.92 rows=14,001 width=0) (actual time=0.871..0.871 rows=12,834 loops=1)

  • Index Cond: (campaign = 'b12623d0-9a90-11e9-bc7e-0a95998482ac'::uuid)
35.          

SubPlan (for Bitmap Heap Scan)

36. 14,562.986 14,562.986 ↓ 0.0 0 12,797

CTE Scan on email_counts (cost=0.00..47.15 rows=1 width=8) (actual time=1.138..1.138 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'accepted'::email_event))
  • Rows Removed by Filter: 13953
37. 14,511.798 14,511.798 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_1 (cost=0.00..47.15 rows=1 width=8) (actual time=1.134..1.134 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'rejected'::email_event))
  • Rows Removed by Filter: 13953
38. 14,486.204 14,486.204 ↑ 1.0 1 12,797

CTE Scan on email_counts email_counts_2 (cost=0.00..47.15 rows=1 width=8) (actual time=0.549..1.132 rows=1 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'delivered'::email_event))
  • Rows Removed by Filter: 13952
39. 14,537.392 14,537.392 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_3 (cost=0.00..47.15 rows=1 width=8) (actual time=1.109..1.136 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'failed'::email_event))
  • Rows Removed by Filter: 13953
40. 14,537.392 14,537.392 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_4 (cost=0.00..47.15 rows=1 width=8) (actual time=1.130..1.136 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'opened'::email_event))
  • Rows Removed by Filter: 13953
41. 14,499.001 14,499.001 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_5 (cost=0.00..47.15 rows=1 width=8) (actual time=1.133..1.133 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'clicked'::email_event))
  • Rows Removed by Filter: 13953
42. 14,524.595 14,524.595 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_6 (cost=0.00..47.15 rows=1 width=8) (actual time=1.135..1.135 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'unsubscribed'::email_event))
  • Rows Removed by Filter: 13953
43. 14,524.595 14,524.595 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_7 (cost=0.00..47.15 rows=1 width=8) (actual time=1.135..1.135 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'complained'::email_event))
  • Rows Removed by Filter: 13953
44. 14,511.798 14,511.798 ↓ 0.0 0 12,797

CTE Scan on email_counts email_counts_8 (cost=0.00..47.15 rows=1 width=8) (actual time=1.134..1.134 rows=0 loops=12,797)

  • Filter: ((email = emails_1.id) AND (event = 'stored'::email_event))
  • Rows Removed by Filter: 13953
45.          

CTE campaign_counts

46. 42.307 163.445 ↑ 33.3 6 1

GroupAggregate (cost=1,716.45..1,859.89 rows=200 width=12) (actual time=152.634..163.445 rows=6 loops=1)

  • Group Key: events_2.event
47. 8.596 121.138 ↑ 1.2 15,517 1

Sort (cost=1,716.45..1,763.60 rows=18,859 width=36) (actual time=117.978..121.138 rows=15,517 loops=1)

  • Sort Key: events_2.event
  • Sort Method: quicksort Memory: 1596kB
48. 112.542 112.542 ↑ 1.2 15,517 1

CTE Scan on events events_2 (cost=0.00..377.18 rows=18,859 width=36) (actual time=86.244..112.542 rows=15,517 loops=1)

49.          

Initplan (for Update)

50. 163.454 163.454 ↓ 0.0 0 1

CTE Scan on campaign_counts (cost=0.00..4.50 rows=1 width=8) (actual time=163.453..163.454 rows=0 loops=1)

  • Filter: (event = 'accepted'::email_event)
  • Rows Removed by Filter: 6
51. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on campaign_counts campaign_counts_1 (cost=0.00..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (event = 'rejected'::email_event)
  • Rows Removed by Filter: 6
52. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on campaign_counts campaign_counts_2 (cost=0.00..4.50 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (event = 'delivered'::email_event)
  • Rows Removed by Filter: 5
53. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on campaign_counts campaign_counts_3 (cost=0.00..4.50 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (event = 'failed'::email_event)
  • Rows Removed by Filter: 5
54. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on campaign_counts campaign_counts_4 (cost=0.00..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Filter: (event = 'opened'::email_event)
  • Rows Removed by Filter: 5
55. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on campaign_counts campaign_counts_5 (cost=0.00..4.50 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (event = 'clicked'::email_event)
  • Rows Removed by Filter: 5
56. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on campaign_counts campaign_counts_6 (cost=0.00..4.50 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (event = 'unsubscribed'::email_event)
  • Rows Removed by Filter: 5
57. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on campaign_counts campaign_counts_7 (cost=0.00..4.50 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (event = 'complained'::email_event)
  • Rows Removed by Filter: 5
58. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on campaign_counts campaign_counts_8 (cost=0.00..4.50 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (event = 'stored'::email_event)
  • Rows Removed by Filter: 6
59. 163.490 163.490 ↑ 1.0 1 1

Index Scan using email_campaigns_pkey on email_campaigns (cost=0.28..2.52 rows=1 width=902) (actual time=163.488..163.490 rows=1 loops=1)

  • Index Cond: (id = 'b12623d0-9a90-11e9-bc7e-0a95998482ac'::uuid)
Planning time : 1.831 ms
Execution time : 304,762.193 ms