explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4PW6

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 1,805.147 ↑ 40.0 1 1

Sort (cost=3,452,012.58..3,452,012.68 rows=40 width=132) (actual time=1,805.147..1,805.147 rows=1 loops=1)

  • Sort Key: cte0.c1 DESC, cte0.c7 NULLS FIRST
  • Sort Method: quicksort Memory: 25kB
2.          

CTE account_activity_cte

3. 0.002 1,800.690 ↑ 40.0 1 1

Limit (cost=3,451,452.21..3,451,452.31 rows=40 width=100) (actual time=1,800.689..1,800.690 rows=1 loops=1)

4.          

CTE account_activity_count_cte_meeting_current

5. 0.559 243.184 ↑ 9.0 1 1

GroupAggregate (cost=41,908.70..41,908.86 rows=9 width=12) (actual time=243.184..243.184 rows=1 loops=1)

  • Group Key: t1662_meetingactivityaccountfact.c7809_accountid
6. 0.341 242.625 ↓ 156.4 1,408 1

Sort (cost=41,908.70..41,908.72 rows=9 width=8) (actual time=242.506..242.625 rows=1,408 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact.c7809_accountid
  • Sort Method: quicksort Memory: 115kB
7. 0.129 242.284 ↓ 156.4 1,408 1

Nested Loop (cost=1,000.42..41,908.56 rows=9 width=8) (actual time=18.331..242.284 rows=1,408 loops=1)

8. 91.348 240.051 ↑ 1.5 4 1

Gather (cost=1,000.00..30,934.89 rows=6 width=4) (actual time=17.553..240.051 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 148.703 148.703 ↑ 2.0 1 3 / 3

Parallel Seq Scan on t1103_accountdim (cost=0.00..29,934.29 rows=2 width=4) (actual time=37.456..148.703 rows=1 loops=3)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (c6163_name ~~* '%Adobe%'::text))
  • Rows Removed by Filter: 144,662
10. 1.515 2.104 ↓ 9.0 352 4

Index Scan using idx_1362_t1662_meetingactivityaccountfact_c7809_accountid_end_s on t1662_meetingactivityaccountfact (cost=0.42..1,828.55 rows=39 width=8) (actual time=0.199..0.526 rows=352 loops=4)

  • Index Cond: ((c7809_accountid = t1103_accountdim.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561532400000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 98
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..15.35 rows=1 width=0) (never executed)

  • Join Filter: (t786_userrolehierarchy.c4812_user_role_id = t859_userroledim.sid)
13. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t1662_meetingactivityaccountfact.c7807_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim (cost=0.00..6.82 rows=18 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
15. 0.157 0.496 ↓ 2.3 354 1

Hash Join (cost=23.05..95.54 rows=152 width=4) (actual time=0.169..0.496 rows=354 loops=1)

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
16. 0.205 0.260 ↑ 1.0 354 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_1 (cost=16.00..87.45 rows=363 width=8) (actual time=0.075..0.260 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
17. 0.055 0.055 ↑ 1.0 354 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.91 rows=363 width=0) (actual time=0.055..0.055 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
18. 0.010 0.079 ↓ 2.4 44 1

Hash (cost=6.82..6.82 rows=18 width=4) (actual time=0.079..0.079 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
19. 0.069 0.069 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..6.82 rows=18 width=4) (actual time=0.009..0.069 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
20. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t1662_meetingactivityaccountfact.c7807_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
21. 0.068 0.093 ↑ 2.3 29 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=4.98..53.05 rows=67 width=4) (actual time=0.039..0.093 rows=29 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=24
22. 0.025 0.025 ↑ 1.8 38 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.025..0.025 rows=38 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
23.          

CTE account_activity_count_cte_meeting_upcoming

24. 0.033 367.640 ↑ 1.0 1 1

GroupAggregate (cost=41,906.35..41,906.37 rows=1 width=12) (actual time=367.640..367.640 rows=1 loops=1)

  • Group Key: t1662_meetingactivityaccountfact_1.c7809_accountid
25. 0.037 367.607 ↓ 41.0 41 1

Sort (cost=41,906.35..41,906.35 rows=1 width=8) (actual time=367.603..367.607 rows=41 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact_1.c7809_accountid
  • Sort Method: quicksort Memory: 26kB
26. 0.000 367.570 ↓ 41.0 41 1

Nested Loop (cost=1,000.42..41,906.34 rows=1 width=8) (actual time=35.354..367.570 rows=41 loops=1)

27. 71.847 256.265 ↑ 1.5 4 1

Gather (cost=1,000.00..30,934.89 rows=6 width=4) (actual time=20.013..256.265 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
28. 184.418 184.418 ↑ 2.0 1 3 / 3

Parallel Seq Scan on t1103_accountdim t1103_accountdim_1 (cost=0.00..29,934.29 rows=2 width=4) (actual time=54.515..184.418 rows=1 loops=3)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (c6163_name ~~* '%Adobe%'::text))
  • Rows Removed by Filter: 144,662
29. 1.542 111.328 ↓ 5.0 10 4

Index Scan using idx_1362_t1662_meetingactivityaccountfact_c7809_accountid_end_s on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.42..1,828.55 rows=2 width=8) (actual time=3.841..27.832 rows=10 loops=4)

  • Index Cond: ((c7809_accountid = t1103_accountdim_1.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1593241200000'::bigint) AND (c7794_activitydate < '1595833200000'::bigint) AND ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9)))
  • Rows Removed by Filter: 440
30.          

SubPlan (for Index Scan)

31. 0.173 92.192 ↑ 1.0 1 43

Nested Loop (cost=0.28..15.35 rows=1 width=0) (actual time=2.144..2.144 rows=1 loops=43)

  • Join Filter: (t786_userrolehierarchy_2.c4812_user_role_id = t859_userroledim_2.sid)
  • Rows Removed by Join Filter: 12
32. 91.117 91.117 ↑ 1.0 1 43

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy t786_userrolehierarchy_2 (cost=0.28..8.30 rows=1 width=4) (actual time=2.119..2.119 rows=1 loops=43)

  • Index Cond: ((sid = t1662_meetingactivityaccountfact_1.c7807_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
33. 0.902 0.902 ↑ 1.3 14 41

Seq Scan on t859_userroledim t859_userroledim_2 (cost=0.00..6.82 rows=18 width=4) (actual time=0.003..0.022 rows=14 loops=41)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 45
34. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=23.05..95.54 rows=152 width=4) (never executed)

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_3 (cost=16.00..87.45 rows=363 width=8) (never executed)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.91 rows=363 width=0) (never executed)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
37. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.82..6.82 rows=18 width=4) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..6.82 rows=18 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
39. 17.594 17.594 ↓ 0.0 0 2

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim t773_userdim_2 (cost=0.28..8.30 rows=1 width=0) (actual time=8.797..8.797 rows=0 loops=2)

  • Index Cond: ((sid = t1662_meetingactivityaccountfact_1.c7807_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 0
40. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t773_userdim t773_userdim_3 (cost=4.98..53.05 rows=67 width=4) (never executed)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
41. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (never executed)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
42.          

CTE account_activity_count_cte_email_sent_current

43. 25.532 554.179 ↑ 566.0 1 1

GroupAggregate (cost=1,668,296.31..1,668,306.21 rows=566 width=12) (actual time=554.179..554.179 rows=1 loops=1)

  • Group Key: t1816_emailactivityaccountfact.c8021_accountid
44. 34.536 528.647 ↓ 150.1 84,974 1

Sort (cost=1,668,296.31..1,668,297.72 rows=566 width=8) (actual time=519.534..528.647 rows=84,974 loops=1)

  • Sort Key: t1816_emailactivityaccountfact.c8021_accountid
  • Sort Method: external sort Disk: 1,504kB
45. 10.997 494.111 ↓ 150.1 84,974 1

Nested Loop (cost=332.20..1,668,270.43 rows=566 width=8) (actual time=31.211..494.111 rows=84,974 loops=1)

46. 296.242 296.242 ↑ 1.5 4 1

Seq Scan on t1103_accountdim t1103_accountdim_2 (cost=0.00..34,420.29 rows=6 width=4) (actual time=7.327..296.242 rows=4 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (c6163_name ~~* '%Adobe%'::text))
  • Rows Removed by Filter: 433,987
47. 164.485 186.872 ↓ 6.1 21,244 4

Bitmap Heap Scan on t1816_emailactivityaccountfact (cost=332.20..272,273.77 rows=3,459 width=8) (actual time=5.985..46.718 rows=21,244 loops=4)

  • Recheck Cond: ((c8021_accountid = t1103_accountdim_2.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 11 or hashed SubPlan 12) OR (alternatives: SubPlan 13 or hashed SubPlan 14)))
  • Rows Removed by Filter: 42,993
  • Heap Blocks: exact=6,365
48. 21.764 21.764 ↓ 6.3 64,237 4

Bitmap Index Scan on idx_1492_t1816_emailactivityaccountfact_c8021_accountid_end_sta (cost=0.00..331.33 rows=10,142 width=0) (actual time=5.441..5.441 rows=64,237 loops=4)

  • Index Cond: ((c8021_accountid = t1103_accountdim_2.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
49.          

SubPlan (for Bitmap Heap Scan)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..15.35 rows=1 width=0) (never executed)

  • Join Filter: (t786_userrolehierarchy_4.c4812_user_role_id = t859_userroledim_4.sid)
51. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy t786_userrolehierarchy_4 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t1816_emailactivityaccountfact.c8045_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
52. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_4 (cost=0.00..6.82 rows=18 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
53. 0.168 0.523 ↓ 2.3 354 1

Hash Join (cost=23.05..95.54 rows=152 width=4) (actual time=0.185..0.523 rows=354 loops=1)

  • Hash Cond: (t786_userrolehierarchy_5.c4812_user_role_id = t859_userroledim_5.sid)
54. 0.208 0.269 ↑ 1.0 354 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_5 (cost=16.00..87.45 rows=363 width=8) (actual time=0.082..0.269 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
55. 0.061 0.061 ↑ 1.0 354 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.91 rows=363 width=0) (actual time=0.061..0.061 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
56. 0.011 0.086 ↓ 2.4 44 1

Hash (cost=6.82..6.82 rows=18 width=4) (actual time=0.086..0.086 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
57. 0.075 0.075 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_5 (cost=0.00..6.82 rows=18 width=4) (actual time=0.011..0.075 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
58. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim t773_userdim_4 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t1816_emailactivityaccountfact.c8045_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
59. 0.074 0.100 ↑ 2.3 29 1

Bitmap Heap Scan on t773_userdim t773_userdim_5 (cost=4.98..53.05 rows=67 width=4) (actual time=0.041..0.100 rows=29 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=24
60. 0.026 0.026 ↑ 1.8 38 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.026..0.026 rows=38 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
61.          

CTE account_activity_count_cte_email_received_current

62. 2.662 495.725 ↑ 456.0 1 1

GroupAggregate (cost=1,668,249.05..1,668,257.03 rows=456 width=12) (actual time=495.724..495.725 rows=1 loops=1)

  • Group Key: t1816_emailactivityaccountfact_1.c8021_accountid
63. 2.006 493.063 ↓ 17.3 7,869 1

Sort (cost=1,668,249.05..1,668,250.19 rows=456 width=8) (actual time=492.531..493.063 rows=7,869 loops=1)

  • Sort Key: t1816_emailactivityaccountfact_1.c8021_accountid
  • Sort Method: quicksort Memory: 561kB
64. 1.119 491.057 ↓ 17.3 7,869 1

Nested Loop (cost=332.03..1,668,228.92 rows=456 width=8) (actual time=30.302..491.057 rows=7,869 loops=1)

65. 352.150 352.150 ↑ 1.5 4 1

Seq Scan on t1103_accountdim t1103_accountdim_3 (cost=0.00..34,420.29 rows=6 width=4) (actual time=7.166..352.150 rows=4 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (c6163_name ~~* '%Adobe%'::text))
  • Rows Removed by Filter: 433,987
66. 115.942 137.788 ↑ 1.4 1,967 4

Bitmap Heap Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=332.03..272,273.60 rows=2,784 width=8) (actual time=5.800..34.447 rows=1,967 loops=4)

  • Recheck Cond: ((c8021_accountid = t1103_accountdim_3.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 16 or hashed SubPlan 17) OR (alternatives: SubPlan 18 or hashed SubPlan 19)))
  • Rows Removed by Filter: 62,270
  • Heap Blocks: exact=6,365
67. 21.260 21.260 ↓ 6.3 64,237 4

Bitmap Index Scan on idx_1492_t1816_emailactivityaccountfact_c8021_accountid_end_sta (cost=0.00..331.33 rows=10,142 width=0) (actual time=5.315..5.315 rows=64,237 loops=4)

  • Index Cond: ((c8021_accountid = t1103_accountdim_3.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
68.          

SubPlan (for Bitmap Heap Scan)

69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..15.35 rows=1 width=0) (never executed)

  • Join Filter: (t786_userrolehierarchy_6.c4812_user_role_id = t859_userroledim_6.sid)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy t786_userrolehierarchy_6 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t1816_emailactivityaccountfact_1.c8045_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
71. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_6 (cost=0.00..6.82 rows=18 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
72. 0.154 0.494 ↓ 2.3 354 1

Hash Join (cost=23.05..95.54 rows=152 width=4) (actual time=0.181..0.494 rows=354 loops=1)

  • Hash Cond: (t786_userrolehierarchy_7.c4812_user_role_id = t859_userroledim_7.sid)
73. 0.199 0.259 ↑ 1.0 354 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_7 (cost=16.00..87.45 rows=363 width=8) (actual time=0.081..0.259 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
74. 0.060 0.060 ↑ 1.0 354 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.91 rows=363 width=0) (actual time=0.060..0.060 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
75. 0.010 0.081 ↓ 2.4 44 1

Hash (cost=6.82..6.82 rows=18 width=4) (actual time=0.081..0.081 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
76. 0.071 0.071 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_7 (cost=0.00..6.82 rows=18 width=4) (actual time=0.010..0.071 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
77. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim t773_userdim_6 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t1816_emailactivityaccountfact_1.c8045_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
78. 0.068 0.092 ↑ 2.3 29 1

Bitmap Heap Scan on t773_userdim t773_userdim_7 (cost=4.98..53.05 rows=67 width=4) (actual time=0.037..0.092 rows=29 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=24
79. 0.024 0.024 ↑ 1.8 38 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.024..0.024 rows=38 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
80.          

CTE filtered_accounts_cte

81. 44.288 139.914 ↑ 1.5 4 1

Gather (cost=1,000.42..30,951.80 rows=6 width=40) (actual time=6.939..139.914 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
82. 0.006 95.626 ↑ 2.0 1 3 / 3

Nested Loop (cost=0.42..29,951.20 rows=2 width=40) (actual time=27.854..95.626 rows=1 loops=3)

83. 95.593 95.593 ↑ 2.0 1 3 / 3

Parallel Seq Scan on t1103_accountdim t1103_accountdim_4 (cost=0.00..29,934.29 rows=2 width=40) (actual time=27.834..95.593 rows=1 loops=3)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (c6163_name ~~* '%Adobe%'::text))
  • Rows Removed by Filter: 144,662
84. 0.027 0.027 ↑ 1.0 1 4 / 3

Index Scan using idx_933_t1401_accountfact_sid_end_stamp_start_stamp_null on t1401_accountfact (cost=0.42..8.44 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=4)

  • Index Cond: ((sid = t1103_accountdim_4.sid) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: (NOT deleted)
85. 0.011 1,800.688 ↑ 1,290.0 1 1

Sort (cost=121.94..125.17 rows=1,290 width=100) (actual time=1,800.688..1,800.688 rows=1 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c1 IS NULL) THEN '0'::bigint ELSE cte0_1.c1 END) DESC, cte4.c3 NULLS FIRST
  • Sort Method: quicksort Memory: 25kB
86. 0.018 1,800.677 ↑ 1,290.0 1 1

Hash Left Join (cost=15.38..81.16 rows=1,290 width=100) (actual time=1,800.669..1,800.677 rows=1 loops=1)

  • Hash Cond: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 WHEN (cte2.c2 IS NOT NULL) THEN cte2.c2 WHEN (cte3.c2 IS NOT NULL) THEN cte3.c2 ELSE NULL::integer END = cte4.c1)
87. 0.011 1,660.799 ↑ 1,290.0 1 1

Hash Full Join (cost=15.19..75.74 rows=1,290 width=48) (actual time=1,660.792..1,660.799 rows=1 loops=1)

  • Hash Cond: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 WHEN (cte2.c2 IS NOT NULL) THEN cte2.c2 ELSE NULL::integer END = cte3.c2)
88. 0.018 1,165.057 ↑ 566.0 1 1

Hash Full Join (cost=0.37..14.06 rows=566 width=36) (actual time=1,165.052..1,165.057 rows=1 loops=1)

  • Hash Cond: (cte2.c2 = CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 ELSE NULL::integer END)
89. 554.185 554.185 ↑ 566.0 1 1

CTE Scan on account_activity_count_cte_email_sent_current cte2 (cost=0.00..11.32 rows=566 width=12) (actual time=554.184..554.185 rows=1 loops=1)

90. 0.004 610.854 ↑ 9.0 1 1

Hash (cost=0.26..0.26 rows=9 width=24) (actual time=610.854..610.854 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
91. 0.015 610.850 ↑ 9.0 1 1

Hash Full Join (cost=0.03..0.26 rows=9 width=24) (actual time=610.845..610.850 rows=1 loops=1)

  • Hash Cond: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END = cte1_1.c2)
92. 243.188 243.188 ↑ 9.0 1 1

CTE Scan on account_activity_count_cte_meeting_current cte0_1 (cost=0.00..0.18 rows=9 width=12) (actual time=243.187..243.188 rows=1 loops=1)

93. 0.004 367.647 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=367.647..367.647 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
94. 367.643 367.643 ↑ 1.0 1 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte1_1 (cost=0.00..0.02 rows=1 width=12) (actual time=367.643..367.643 rows=1 loops=1)

95. 0.004 495.731 ↑ 456.0 1 1

Hash (cost=9.12..9.12 rows=456 width=12) (actual time=495.731..495.731 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
96. 495.727 495.727 ↑ 456.0 1 1

CTE Scan on account_activity_count_cte_email_received_current cte3 (cost=0.00..9.12 rows=456 width=12) (actual time=495.727..495.727 rows=1 loops=1)

97. 0.007 139.860 ↑ 1.5 4 1

Hash (cost=0.12..0.12 rows=6 width=68) (actual time=139.860..139.860 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
98. 139.853 139.853 ↑ 1.5 4 1

CTE Scan on filtered_accounts_cte cte4 (cost=0.00..0.12 rows=6 width=68) (actual time=6.942..139.853 rows=4 loops=1)

99.          

CTE aggregated_domains_cte

100. 0.018 4.418 ↑ 50.0 1 1

GroupAggregate (cost=555.32..556.32 rows=50 width=36) (actual time=4.418..4.418 rows=1 loops=1)

  • Group Key: t1402_accountdomaindistinct.c7150_accountid
101. 0.021 4.400 ↑ 10.0 5 1

Sort (cost=555.32..555.45 rows=50 width=17) (actual time=4.399..4.400 rows=5 loops=1)

  • Sort Key: t1402_accountdomaindistinct.c7150_accountid
  • Sort Method: quicksort Memory: 25kB
102. 1.426 4.379 ↑ 10.0 5 1

Hash Semi Join (cost=1.30..553.91 rows=50 width=17) (actual time=1.118..4.379 rows=5 loops=1)

  • Hash Cond: (t1402_accountdomaindistinct.c7150_accountid = cte0_2.c5)
103. 2.949 2.949 ↑ 1.0 17,601 1

Seq Scan on t1402_accountdomaindistinct (cost=0.00..505.02 rows=17,916 width=17) (actual time=0.010..2.949 rows=17,601 loops=1)

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 1,402
104. 0.003 0.004 ↑ 40.0 1 1

Hash (cost=0.80..0.80 rows=40 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
105. 0.001 0.001 ↑ 40.0 1 1

CTE Scan on account_activity_cte cte0_2 (cost=0.00..0.80 rows=40 width=4) (actual time=0.001..0.001 rows=1 loops=1)

106. 0.012 1,805.134 ↑ 40.0 1 1

Hash Right Join (cost=1.30..2.89 rows=40 width=132) (actual time=1,805.130..1,805.134 rows=1 loops=1)

  • Hash Cond: (cte1.c1 = cte0.c5)
107. 4.423 4.423 ↑ 50.0 1 1

CTE Scan on aggregated_domains_cte cte1 (cost=0.00..1.00 rows=50 width=36) (actual time=4.422..4.423 rows=1 loops=1)

108. 0.007 1,800.699 ↑ 40.0 1 1

Hash (cost=0.80..0.80 rows=40 width=100) (actual time=1,800.699..1,800.699 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
109. 1,800.692 1,800.692 ↑ 40.0 1 1

CTE Scan on account_activity_cte cte0 (cost=0.00..0.80 rows=40 width=100) (actual time=1,800.691..1,800.692 rows=1 loops=1)

Planning time : 21.267 ms
Execution time : 1,807.344 ms