explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rsIU

Settings
# exclusive inclusive rows x rows loops node
1. 438.382 2,866.716 ↓ 98,687.8 394,751 1

Nested Loop (cost=418.99..6,919.86 rows=4 width=242) (actual time=72.585..2,866.716 rows=394,751 loops=1)

  • Buffers: shared hit=2337283
2.          

CTE blasts

3. 0.002 0.061 ↑ 2.0 1 1

Append (cost=0.42..4.08 rows=2 width=32) (actual time=0.030..0.061 rows=1 loops=1)

  • Buffers: shared hit=6
4. 0.030 0.030 ↑ 1.0 1 1

Index Only Scan using "t_email_blast_dim$idx3" on t_email_blast_dim t_email_blast_dim_1 (cost=0.42..2.64 rows=1 width=32) (actual time=0.029..0.030 rows=1 loops=1)

  • Index Cond: (b_identity_uuid = 'a34c84db-53ba-49a5-8e0c-0e330e7c52a9'::uuid)
  • Filter: (bc_identity_uuid = COALESCE(bc_identity_uuid))
  • Heap Fetches: 0
  • Buffers: shared hit=4
5. 0.029 0.029 ↓ 0.0 0 1

Index Only Scan using "t_communication_series_dim$idx4" on t_communication_series_dim t_communication_series_dim_1 (cost=0.28..1.44 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)

  • Index Cond: (cs_identity_uuid = 'a34c84db-53ba-49a5-8e0c-0e330e7c52a9'::uuid)
  • Filter: (csc_identity_uuid = COALESCE(csc_identity_uuid))
  • Heap Fetches: 0
  • Buffers: shared hit=2
6.          

CTE orgid

7. 0.002 0.003 ↑ 1.0 1 1

Limit (cost=0.00..0.02 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=1)

8. 0.001 0.001 ↑ 2.0 1 1

CTE Scan on blasts blasts_3 (cost=0.00..0.04 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=1)

9.          

Initplan (forNested Loop)

10. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on orgid (cost=0.00..0.02 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1)

11. 76.104 1,244.074 ↓ 113.2 394,752 1

Hash Left Join (cost=414.31..607.45 rows=3,487 width=161) (actual time=72.544..1,244.074 rows=394,752 loops=1)

  • Hash Cond: (t_email_f_current.bounce_code_id = tebd.bounce_id)
  • Buffers: shared hit=361104
12. 116.910 1,167.937 ↓ 113.2 394,752 1

Hash Left Join (cost=412.81..589.04 rows=3,487 width=151) (actual time=72.504..1,167.937 rows=394,752 loops=1)

  • Hash Cond: (t_email_f_current.bounce_category_id = tebcd.bounce_cat_id)
  • Buffers: shared hit=361103
13. 97.563 1,051.005 ↓ 113.2 394,752 1

Hash Left Join (cost=411.61..573.19 rows=3,487 width=147) (actual time=72.460..1,051.005 rows=394,752 loops=1)

  • Hash Cond: ((t_email_f_current.organization_uuid = ta.organization_uuid) AND (t_email_f_current.blast_content_id = ta.referring_entity_id) AND (t_email_f_current.person_uuid = ta.person_uuid))
  • Buffers: shared hit=361102
14. 108.000 885.028 ↓ 113.2 394,752 1

Hash Left Join (cost=177.86..277.11 rows=3,487 width=155) (actual time=4.011..885.028 rows=394,752 loops=1)

  • Hash Cond: ((t_email_f_current.organization_uuid = ti.organization_uuid) AND (t_email_f_current.blast_content_id = ti.entity_id) AND (t_email_f_current.person_uuid = ti.targeted_person_uuid))
  • Buffers: shared hit=356876
15. 63.849 773.179 ↓ 113.2 394,752 1

Nested Loop (cost=1.16..38.08 rows=3,487 width=147) (actual time=0.147..773.179 rows=394,752 loops=1)

  • Buffers: shared hit=355276
16. 0.005 0.171 ↑ 1.0 1 1

Nested Loop (cost=0.47..5.22 rows=1 width=99) (actual time=0.114..0.171 rows=1 loops=1)

  • Buffers: shared hit=12
17. 0.014 0.086 ↑ 1.0 1 1

HashAggregate (cost=0.05..0.06 rows=1 width=32) (actual time=0.085..0.086 rows=1 loops=1)

  • Group Key: blasts.organization_uuid, blasts.blast_content_id
  • Buffers: shared hit=6
18. 0.072 0.072 ↑ 1.0 1 1

CTE Scan on blasts (cost=0.00..0.04 rows=1 width=32) (actual time=0.040..0.072 rows=1 loops=1)

  • Filter: (organization_uuid = $2)
  • Buffers: shared hit=6
19. 0.007 0.080 ↑ 2.0 1 1

Append (cost=0.42..5.14 rows=2 width=68) (actual time=0.025..0.080 rows=1 loops=1)

  • Buffers: shared hit=6
20. 0.052 0.052 ↑ 1.0 1 1

Index Scan using tebd_bc_uuid__name on t_email_blast_dim (cost=0.42..2.64 rows=1 width=67) (actual time=0.024..0.052 rows=1 loops=1)

  • Index Cond: (bc_identity_uuid = blasts.blast_content_id)
  • Filter: (bc_organization_uuid = $2)
  • Buffers: shared hit=4
21. 0.021 0.021 ↓ 0.0 0 1

Index Scan using "t_communication_series_dim$idx2" on t_communication_series_dim (cost=0.28..2.50 rows=1 width=73) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (csc_identity_uuid = blasts.blast_content_id)
  • Filter: (csc_organization_uuid = $2)
  • Buffers: shared hit=2
22. 29.304 709.159 ↓ 15,182.8 394,752 1

Append (cost=0.70..32.60 rows=26 width=112) (actual time=0.031..709.159 rows=394,752 loops=1)

  • Buffers: shared hit=355264
23. 679.809 679.809 ↓ 32,896.0 394,752 1

Index Scan using "t_email_f_current$idx2" on t_email_f_current (cost=0.70..15.22 rows=12 width=112) (actual time=0.031..679.809 rows=394,752 loops=1)

  • Index Cond: ((organization_uuid = $2) AND (blast_content_id = t_email_blast_dim.bc_identity_uuid))
  • Buffers: shared hit=355259
24. 0.046 0.046 ↓ 0.0 0 1

Index Scan using "t_email_f_archive$idx2" on t_email_f_archive (cost=0.70..17.38 rows=14 width=112) (actual time=0.046..0.046 rows=0 loops=1)

  • Index Cond: ((organization_uuid = $2) AND (blast_content_id = t_email_blast_dim.bc_identity_uuid))
  • Buffers: shared hit=5
25. 0.151 3.849 ↓ 291.0 291 1

Hash (cost=176.68..176.68 rows=1 width=56) (actual time=3.849..3.849 rows=291 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
  • Buffers: shared hit=1600
26. 0.091 3.698 ↓ 291.0 291 1

Subquery Scan on ti (cost=176.66..176.68 rows=1 width=56) (actual time=3.506..3.698 rows=291 loops=1)

  • Buffers: shared hit=1600
27. 0.569 3.607 ↓ 291.0 291 1

HashAggregate (cost=176.66..176.67 rows=1 width=56) (actual time=3.505..3.607 rows=291 loops=1)

  • Group Key: i.targeted_person_uuid, tl.entity_id, i.organization_uuid
  • Buffers: shared hit=1600
28. 0.308 3.038 ↓ 321.0 321 1

Nested Loop (cost=1.04..176.65 rows=1 width=48) (actual time=0.082..3.038 rows=321 loops=1)

  • Buffers: shared hit=1600
29. 0.108 1.446 ↓ 321.0 321 1

Nested Loop (cost=0.61..174.02 rows=1 width=48) (actual time=0.062..1.446 rows=321 loops=1)

  • Buffers: shared hit=316
30. 0.005 0.006 ↑ 2.0 1 1

HashAggregate (cost=0.04..0.07 rows=2 width=16) (actual time=0.005..0.006 rows=1 loops=1)

  • Group Key: blasts_1.blast_content_id
31. 0.001 0.001 ↑ 2.0 1 1

CTE Scan on blasts blasts_1 (cost=0.00..0.04 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=1)

32. 1.332 1.332 ↓ 321.0 321 1

Index Scan using "t_interaction$idx3" on t_interaction i (cost=0.56..86.97 rows=1 width=64) (actual time=0.055..1.332 rows=321 loops=1)

  • Index Cond: (referring_entity_id = blasts_1.blast_content_id)
  • Filter: ((entity_type = 'TrackingLink'::text) AND (organization_uuid = $2))
  • Buffers: shared hit=316
33. 1.284 1.284 ↑ 1.0 1 321

Index Scan using "t_tracking_link$idx1" on t_tracking_link tl (cost=0.43..2.64 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=321)

  • Index Cond: (uuid = i.entity_id)
  • Buffers: shared hit=1284
34. 0.030 68.414 ↓ 59.0 59 1

Hash (cost=233.73..233.73 rows=1 width=56) (actual time=68.414..68.414 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=4226
35. 0.014 68.384 ↓ 59.0 59 1

Subquery Scan on ta (cost=233.71..233.73 rows=1 width=56) (actual time=68.356..68.384 rows=59 loops=1)

  • Buffers: shared hit=4226
36. 0.078 68.370 ↓ 59.0 59 1

HashAggregate (cost=233.71..233.72 rows=1 width=56) (actual time=68.355..68.370 rows=59 loops=1)

  • Group Key: ta_1.referring_entity_id, ta_1.organization_uuid, ta_1.person_uuid
  • Buffers: shared hit=4226
37. 0.027 68.292 ↓ 60.0 60 1

Nested Loop (cost=115.74..233.70 rows=1 width=48) (actual time=67.445..68.292 rows=60 loops=1)

  • Buffers: shared hit=4226
38. 0.004 0.005 ↑ 2.0 1 1

HashAggregate (cost=0.04..0.07 rows=2 width=16) (actual time=0.005..0.005 rows=1 loops=1)

  • Group Key: blasts_2.blast_content_id
39. 0.001 0.001 ↑ 2.0 1 1

CTE Scan on blasts blasts_2 (cost=0.00..0.04 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=1)

40. 1.264 68.260 ↓ 60.0 60 1

Bitmap Heap Scan on t_activity ta_1 (cost=115.69..116.81 rows=1 width=48) (actual time=67.433..68.260 rows=60 loops=1)

  • Recheck Cond: ((referring_entity_id = blasts_2.blast_content_id) AND (organization_uuid = $2))
  • Filter: (activity_type <> ALL ('{Unsubscribe,SubMgmt}'::text[]))
  • Rows Removed by Filter: 699
  • Heap Blocks: exact=407
  • Buffers: shared hit=4226
41. 1.951 66.996 ↓ 0.0 0 1

BitmapAnd (cost=115.69..115.69 rows=1 width=0) (actual time=66.996..66.996 rows=0 loops=1)

  • Buffers: shared hit=3819
42. 0.203 0.203 ↓ 3.7 759 1

Bitmap Index Scan on "t_activity$idx2" (cost=0.00..4.29 rows=204 width=0) (actual time=0.203..0.203 rows=759 loops=1)

  • Index Cond: (referring_entity_id = blasts_2.blast_content_id)
  • Buffers: shared hit=9
43. 64.842 64.842 ↓ 59.9 329,437 1

Bitmap Index Scan on "t_activity$idx1" (cost=0.00..110.00 rows=5,499 width=0) (actual time=64.842..64.842 rows=329,437 loops=1)

  • Index Cond: (organization_uuid = $2)
  • Buffers: shared hit=3810
44. 0.005 0.022 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=12) (actual time=0.022..0.022 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
45. 0.017 0.017 ↑ 1.0 9 1

Seq Scan on t_email_bounce_cat_dim tebcd (cost=0.00..1.09 rows=9 width=12) (actual time=0.014..0.017 rows=9 loops=1)

  • Buffers: shared hit=1
46. 0.022 0.033 ↑ 1.0 22 1

Hash (cost=1.22..1.22 rows=22 width=18) (actual time=0.033..0.033 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
47. 0.011 0.011 ↑ 1.0 22 1

Seq Scan on t_email_bounce_dim tebd (cost=0.00..1.22 rows=22 width=18) (actual time=0.005..0.011 rows=22 loops=1)

  • Buffers: shared hit=1
48. 1,184.256 1,184.256 ↑ 1.0 1 394,752

Index Scan using "t_person_dim$idx1" on t_person_dim_backend p (cost=0.56..1.81 rows=1 width=93) (actual time=0.003..0.003 rows=1 loops=394,752)

  • Index Cond: ((organization_uuid = $2) AND (person_uuid = t_email_f_current.person_uuid))
  • Buffers: shared hit=1976179
Planning time : 14.689 ms
Execution time : 2,889.206 ms