explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yi3H

Settings
# exclusive inclusive rows x rows loops node
1. 646.090 24,870.368 ↓ 98,687.8 394,751 1

Nested Loop (cost=499.53..6,889.50 rows=4 width=242) (actual time=60.891..24,870.368 rows=394,751 loops=1)

  • Buffers: shared hit=2337331
2.          

CTE blasts

3. 0.001 0.098 ↑ 2.0 1 1

Append (cost=0.42..2.98 rows=2 width=32) (actual time=0.063..0.098 rows=1 loops=1)

  • Buffers: shared hit=6
4. 0.063 0.063 ↑ 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..1.54 rows=1 width=32) (actual time=0.062..0.063 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.034 0.034 ↓ 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.034..0.034 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.001 0.065 ↑ 1.0 1 1

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

  • Buffers: shared hit=4
8. 0.064 0.064 ↑ 2.0 1 1

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

  • Buffers: shared hit=4
9.          

Initplan (forNested Loop)

10. 0.067 0.067 ↑ 1.0 1 1

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

  • Buffers: shared hit=4
11. 92.679 22,645.203 ↓ 113.2 394,752 1

Hash Left Join (cost=495.95..578.20 rows=3,487 width=161) (actual time=60.817..22,645.203 rows=394,752 loops=1)

  • Hash Cond: (t_email_f_current.bounce_code_id = tebd.bounce_id)
  • Buffers: shared hit=361152
12. 144.408 22,552.505 ↓ 113.2 394,752 1

Hash Left Join (cost=494.46..559.78 rows=3,487 width=151) (actual time=60.793..22,552.505 rows=394,752 loops=1)

  • Hash Cond: (t_email_f_current.bounce_category_id = tebcd.bounce_cat_id)
  • Buffers: shared hit=361151
13. 2,688.084 22,408.074 ↓ 113.2 394,752 1

Merge Left Join (cost=493.25..543.94 rows=3,487 width=147) (actual time=60.724..22,408.074 rows=394,752 loops=1)

  • Merge Cond: (t_email_f_current.blast_content_id = ta.referring_entity_id)
  • Join Filter: ((ta.organization_uuid = t_email_f_current.organization_uuid) AND (ta.person_uuid = t_email_f_current.person_uuid))
  • Rows Removed by Join Filter: 23288615
  • Buffers: shared hit=361147
14. 13,126.816 18,781.451 ↓ 113.2 394,752 1

Merge Left Join (cost=259.55..301.14 rows=3,487 width=155) (actual time=4.158..18,781.451 rows=394,752 loops=1)

  • Merge Cond: (t_email_f_current.blast_content_id = ti.entity_id)
  • Join Filter: ((ti.organization_uuid = t_email_f_current.organization_uuid) AND (ti.targeted_person_uuid = t_email_f_current.person_uuid))
  • Rows Removed by Join Filter: 114830346
  • Buffers: shared hit=356921
15. 63.999 1,540.557 ↓ 113.2 394,752 1

Nested Loop (cost=82.84..115.37 rows=3,487 width=147) (actual time=0.739..1,540.557 rows=394,752 loops=1)

  • Buffers: shared hit=355321
16. 0.018 0.716 ↑ 1.0 1 1

Merge Join (cost=82.15..82.51 rows=1 width=99) (actual time=0.672..0.716 rows=1 loops=1)

  • Merge Cond: (t_email_blast_dim.bc_identity_uuid = blasts.blast_content_id)
  • Buffers: shared hit=57
17. 0.069 0.641 ↑ 1.7 40 1

Sort (cost=82.08..82.25 rows=69 width=68) (actual time=0.614..0.641 rows=40 loops=1)

  • Sort Key: t_email_blast_dim.bc_identity_uuid
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=55
18. 0.006 0.572 ↑ 1.5 47 1

Append (cost=0.42..79.97 rows=69 width=68) (actual time=0.123..0.572 rows=47 loops=1)

  • Buffers: shared hit=55
19. 0.431 0.431 ↑ 1.7 37 1

Index Scan using "t_email_blast_dim$idx1" on t_email_blast_dim (cost=0.42..70.80 rows=62 width=67) (actual time=0.122..0.431 rows=37 loops=1)

  • Index Cond: (bc_organization_uuid = $2)
  • Buffers: shared hit=44
20. 0.135 0.135 ↓ 1.4 10 1

Index Scan using "t_communication_series_dim$idx3" on t_communication_series_dim (cost=0.28..9.17 rows=7 width=73) (actual time=0.053..0.135 rows=10 loops=1)

  • Index Cond: (csc_organization_uuid = $2)
  • Buffers: shared hit=11
21. 0.018 0.057 ↑ 1.0 1 1

Sort (cost=0.07..0.08 rows=1 width=32) (actual time=0.051..0.057 rows=1 loops=1)

  • Sort Key: blasts.blast_content_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
22. 0.003 0.039 ↑ 1.0 1 1

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

  • Group Key: blasts.organization_uuid, blasts.blast_content_id
  • Buffers: shared hit=2
23. 0.036 0.036 ↑ 1.0 1 1

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

  • Filter: (organization_uuid = $2)
  • Buffers: shared hit=2
24. 32.418 1,475.842 ↓ 15,182.8 394,752 1

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

  • Buffers: shared hit=355264
25. 1,443.301 1,443.301 ↓ 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.065..1,443.301 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
26. 0.123 0.123 ↓ 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.123..0.123 rows=0 loops=1)

  • Index Cond: ((organization_uuid = $2) AND (blast_content_id = t_email_blast_dim.bc_identity_uuid))
  • Buffers: shared hit=5
27. 4,110.750 4,114.078 ↓ 114,435,886.0 114,435,886 1

Materialize (cost=176.70..176.71 rows=1 width=56) (actual time=3.318..4,114.078 rows=114,435,886 loops=1)

  • Buffers: shared hit=1600
28. 0.104 3.328 ↓ 291.0 291 1

Sort (cost=176.70..176.71 rows=1 width=56) (actual time=3.315..3.328 rows=291 loops=1)

  • Sort Key: ti.entity_id
  • Sort Method: quicksort Memory: 65kB
  • Buffers: shared hit=1600
29. 0.030 3.224 ↓ 291.0 291 1

Subquery Scan on ti (cost=176.66..176.69 rows=1 width=56) (actual time=3.091..3.224 rows=291 loops=1)

  • Buffers: shared hit=1600
30. 0.091 3.194 ↓ 291.0 291 1

GroupAggregate (cost=176.66..176.68 rows=1 width=56) (actual time=3.090..3.194 rows=291 loops=1)

  • Group Key: i.targeted_person_uuid, tl.entity_id, i.organization_uuid
  • Buffers: shared hit=1600
31. 0.153 3.103 ↓ 321.0 321 1

Sort (cost=176.66..176.66 rows=1 width=48) (actual time=3.088..3.103 rows=321 loops=1)

  • Sort Key: i.targeted_person_uuid, tl.entity_id
  • Sort Method: quicksort Memory: 50kB
  • Buffers: shared hit=1600
32. 0.060 2.950 ↓ 321.0 321 1

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

  • Buffers: shared hit=1600
33. 0.050 2.248 ↓ 321.0 321 1

Nested Loop (cost=0.61..174.01 rows=1 width=48) (actual time=0.082..2.248 rows=321 loops=1)

  • Buffers: shared hit=316
34. 0.001 0.009 ↑ 2.0 1 1

Unique (cost=0.05..0.06 rows=2 width=16) (actual time=0.008..0.009 rows=1 loops=1)

35. 0.007 0.008 ↑ 2.0 1 1

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Sort Key: blasts_1.blast_content_id
  • Sort Method: quicksort Memory: 25kB
36. 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.000..0.001 rows=1 loops=1)

37. 2.189 2.189 ↓ 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.073..2.189 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
38. 0.642 0.642 ↑ 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.002..0.002 rows=1 loops=321)

  • Index Cond: (uuid = i.entity_id)
  • Buffers: shared hit=1284
39. 882.003 938.539 ↓ 22,893,922.0 22,893,922 1

Materialize (cost=233.71..233.74 rows=1 width=56) (actual time=56.521..938.539 rows=22,893,922 loops=1)

  • Buffers: shared hit=4226
40. 0.021 56.536 ↓ 59.0 59 1

GroupAggregate (cost=233.71..233.73 rows=1 width=56) (actual time=56.517..56.536 rows=59 loops=1)

  • Group Key: ta.referring_entity_id, ta.organization_uuid, ta.person_uuid
  • Buffers: shared hit=4226
41. 0.053 56.515 ↓ 60.0 60 1

Sort (cost=233.71..233.71 rows=1 width=48) (actual time=56.512..56.515 rows=60 loops=1)

  • Sort Key: ta.referring_entity_id, ta.person_uuid
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=4226
42. 0.022 56.462 ↓ 60.0 60 1

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

  • Buffers: shared hit=4226
43. 0.001 0.012 ↑ 2.0 1 1

Unique (cost=0.05..0.06 rows=2 width=16) (actual time=0.010..0.012 rows=1 loops=1)

44. 0.010 0.011 ↑ 2.0 1 1

Sort (cost=0.05..0.06 rows=2 width=16) (actual time=0.009..0.011 rows=1 loops=1)

  • Sort Key: blasts_2.blast_content_id
  • Sort Method: quicksort Memory: 25kB
45. 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.000..0.001 rows=1 loops=1)

46. 3.628 56.428 ↓ 60.0 60 1

Bitmap Heap Scan on t_activity ta (cost=115.69..116.81 rows=1 width=48) (actual time=54.146..56.428 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
47. 0.704 52.800 ↓ 0.0 0 1

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

  • Buffers: shared hit=3819
48. 0.153 0.153 ↓ 3.7 759 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
51. 0.018 0.018 ↑ 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.016..0.018 rows=9 loops=1)

  • Buffers: shared hit=1
52. 0.008 0.019 ↑ 1.0 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
53. 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.007..0.011 rows=22 loops=1)

  • Buffers: shared hit=1
54. 1,579.008 1,579.008 ↑ 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.004..0.004 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 : 13.797 ms
Execution time : 24,895.180 ms