explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VqSC

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 14,288.635 ↑ 1.0 40 1

Sort (cost=632,653,835.98..632,653,836.08 rows=40 width=132) (actual time=14,288.633..14,288.635 rows=40 loops=1)

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

CTE account_activity_cte

3. 0.005 14,284.552 ↑ 1.0 40 1

Limit (cost=632,653,275.61..632,653,275.71 rows=40 width=100) (actual time=14,284.546..14,284.552 rows=40 loops=1)

4.          

CTE account_activity_count_cte_meeting_current

5. 16.245 74.980 ↓ 1.1 2,027 1

GroupAggregate (cost=0.42..2,869,903.31 rows=1,794 width=12) (actual time=0.579..74.980 rows=2,027 loops=1)

  • Group Key: t1662_meetingactivityaccountfact.c7809_accountid
6. 58.324 58.735 ↓ 1.2 82,158 1

Index Scan using idx_1362_t1662_meetingactivityaccountfact_c7809_accountid_end_s on t1662_meetingactivityaccountfact (cost=0.42..2,869,537.58 rows=69,559 width=8) (actual time=0.531..58.735 rows=82,158 loops=1)

  • Index Cond: ((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: 36,659
7.          

SubPlan (for Index Scan)

8. 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)
9. 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))
10. 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))
11. 0.107 0.342 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
12. 0.134 0.184 ↑ 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.064..0.184 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
13. 0.050 0.050 ↑ 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.050..0.050 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
14. 0.008 0.051 ↓ 2.4 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
15. 0.043 0.043 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..6.82 rows=18 width=4) (actual time=0.006..0.043 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
16. 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))
17. 0.045 0.069 ↑ 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.034..0.069 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
18. 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))
19.          

CTE account_activity_count_cte_meeting_upcoming

20. 1.483 46.939 ↑ 2.6 600 1

GroupAggregate (cost=0.42..2,869,570.17 rows=1,538 width=12) (actual time=0.577..46.939 rows=600 loops=1)

  • Group Key: t1662_meetingactivityaccountfact_1.c7809_accountid
21. 45.063 45.456 ↓ 1.6 5,484 1

Index Scan using idx_1362_t1662_meetingactivityaccountfact_c7809_accountid_end_s on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.42..2,869,537.58 rows=3,443 width=8) (actual time=0.459..45.456 rows=5,484 loops=1)

  • Index Cond: ((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: 113,333
22.          

SubPlan (for Index Scan)

23. 0.000 0.000 ↓ 0.0 0

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

  • Join Filter: (t786_userrolehierarchy_2.c4812_user_role_id = t859_userroledim_2.sid)
24. 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_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

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

Seq Scan on t859_userroledim t859_userroledim_2 (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))
26. 0.112 0.329 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
27. 0.124 0.167 ↑ 1.0 354 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_3 (cost=16.00..87.45 rows=363 width=8) (actual time=0.059..0.167 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
28. 0.043 0.043 ↑ 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.043..0.043 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
29. 0.006 0.050 ↓ 2.4 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.044 0.044 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..6.82 rows=18 width=4) (actual time=0.007..0.044 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
31. 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_2 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • 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))
32. 0.042 0.064 ↑ 2.3 29 1

Bitmap Heap Scan on t773_userdim t773_userdim_3 (cost=4.98..53.05 rows=67 width=4) (actual time=0.032..0.064 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
33. 0.022 0.022 ↑ 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.022..0.022 rows=38 loops=1)

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

CTE account_activity_count_cte_email_sent_current

35. 493.207 6,774.254 ↓ 4.7 6,073 1

GroupAggregate (cost=0.56..309,555,331.85 rows=1,283 width=12) (actual time=1.293..6,774.254 rows=6,073 loops=1)

  • Group Key: t1816_emailactivityaccountfact.c8021_accountid
36. 6,280.635 6,281.047 ↑ 1.8 2,531,918 1

Index Scan using idx_1492_t1816_emailactivityaccountfact_c8021_accountid_end_sta on t1816_emailactivityaccountfact (cost=0.56..309,533,129.22 rows=4,437,961 width=8) (actual time=0.458..6,281.047 rows=2,531,918 loops=1)

  • Index Cond: ((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: 10,484,250
37.          

SubPlan (for Index Scan)

38. 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)
39. 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))
40. 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))
41. 0.121 0.345 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_5.c4812_user_role_id = t859_userroledim_5.sid)
42. 0.128 0.172 ↑ 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.059..0.172 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
43. 0.044 0.044 ↑ 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.044..0.044 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
44. 0.009 0.052 ↓ 2.4 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
45. 0.043 0.043 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_5 (cost=0.00..6.82 rows=18 width=4) (actual time=0.007..0.043 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
46. 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))
47. 0.044 0.067 ↑ 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.034..0.067 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
48. 0.023 0.023 ↑ 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.023..0.023 rows=38 loops=1)

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

CTE account_activity_count_cte_email_received_current

50. 575.565 7,143.316 ↓ 3.1 3,930 1

GroupAggregate (cost=0.56..309,551,001.22 rows=1,283 width=12) (actual time=1.743..7,143.316 rows=3,930 loops=1)

  • Group Key: t1816_emailactivityaccountfact_1.c8021_accountid
51. 6,567.308 6,567.751 ↑ 1.4 2,533,148 1

Index Scan using idx_1492_t1816_emailactivityaccountfact_c8021_accountid_end_sta on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.56..309,533,129.22 rows=3,571,835 width=8) (actual time=0.791..6,567.751 rows=2,533,148 loops=1)

  • Index Cond: ((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: 10,483,020
52.          

SubPlan (for Index Scan)

53. 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)
54. 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))
55. 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))
56. 0.116 0.366 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_7.c4812_user_role_id = t859_userroledim_7.sid)
57. 0.150 0.195 ↑ 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.062..0.195 rows=354 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=61
58. 0.045 0.045 ↑ 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.045..0.045 rows=354 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
59. 0.008 0.055 ↓ 2.4 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
60. 0.047 0.047 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_7 (cost=0.00..6.82 rows=18 width=4) (actual time=0.007..0.047 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
61. 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))
62. 0.052 0.077 ↑ 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.038..0.077 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
63. 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))
64.          

CTE filtered_accounts_cte

65. 37.673 195.107 ↑ 1.6 35,940 1

Gather (cost=19,390.88..58,147.52 rows=57,792 width=40) (actual time=78.529..195.107 rows=35,940 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
66. 6.286 157.434 ↑ 2.0 11,980 3 / 3

Hash Join (cost=18,390.88..51,368.32 rows=24,080 width=40) (actual time=99.769..157.434 rows=11,980 loops=3)

  • Hash Cond: (t1103_accountdim.sid = t1401_accountfact.sid)
67. 51.596 51.596 ↑ 2.0 11,980 3 / 3

Parallel Seq Scan on t1103_accountdim (cost=0.00..29,476.53 rows=24,149 width=40) (actual time=0.029..51.596 rows=11,980 loops=3)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 132,683
68. 7.883 99.552 ↑ 1.4 35,940 3 / 3

Hash (cost=17,767.92..17,767.92 rows=49,836 width=4) (actual time=99.552..99.552 rows=35,940 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,776kB
69. 91.669 91.669 ↑ 1.4 35,940 3 / 3

Seq Scan on t1401_accountfact (cost=0.00..17,767.92 rows=49,836 width=4) (actual time=0.046..91.669 rows=35,940 loops=3)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 420,878
70. 1.167 14,284.547 ↑ 4,101,310.4 40 1

Sort (cost=7,749,321.53..8,159,452.57 rows=164,052,417 width=100) (actual time=14,284.544..14,284.547 rows=40 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: top-N heapsort Memory: 34kB
71. 5.582 14,283.380 ↑ 25,258.3 6,495 1

Merge Right Join (cost=101,189.06..2,563,683.61 rows=164,052,417 width=100) (actual time=14,274.155..14,283.380 rows=6,495 loops=1)

  • Merge Cond: (cte4.c1 = (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))
72. 14.247 221.030 ↑ 1.6 35,940 1

Sort (cost=8,099.78..8,244.26 rows=57,792 width=68) (actual time=218.397..221.030 rows=35,940 loops=1)

  • Sort Key: cte4.c1
  • Sort Method: quicksort Memory: 4,013kB
73. 206.783 206.783 ↑ 1.6 35,940 1

CTE Scan on filtered_accounts_cte cte4 (cost=0.00..1,155.84 rows=57,792 width=68) (actual time=78.531..206.783 rows=35,940 loops=1)

74. 0.677 14,056.768 ↑ 87.4 6,495 1

Materialize (cost=93,089.28..95,927.95 rows=567,734 width=48) (actual time=14,055.753..14,056.768 rows=6,495 loops=1)

75. 1.564 14,056.091 ↑ 87.4 6,495 1

Sort (cost=93,089.28..94,508.62 rows=567,734 width=48) (actual time=14,055.749..14,056.091 rows=6,495 loops=1)

  • Sort Key: (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)
  • Sort Method: quicksort Memory: 822kB
76. 1.912 14,054.527 ↑ 87.4 6,495 1

Merge Full Join (cost=12,619.84..21,363.52 rows=567,734 width=48) (actual time=14,051.593..14,054.527 rows=6,495 loops=1)

  • Merge Cond: (cte3.c2 = (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))
77. 1.265 7,146.880 ↓ 3.1 3,930 1

Sort (cost=91.90..95.10 rows=1,283 width=12) (actual time=7,146.696..7,146.880 rows=3,930 loops=1)

  • Sort Key: cte3.c2
  • Sort Method: quicksort Memory: 281kB
78. 7,145.615 7,145.615 ↓ 3.1 3,930 1

CTE Scan on account_activity_count_cte_email_received_current cte3 (cost=0.00..25.66 rows=1,283 width=12) (actual time=1.745..7,145.615 rows=3,930 loops=1)

79. 0.577 6,905.735 ↑ 14.4 6,132 1

Materialize (cost=12,527.94..12,970.45 rows=88,501 width=36) (actual time=6,904.893..6,905.735 rows=6,132 loops=1)

80. 1.100 6,905.158 ↑ 14.4 6,132 1

Sort (cost=12,527.94..12,749.19 rows=88,501 width=36) (actual time=6,904.885..6,905.158 rows=6,132 loops=1)

  • Sort Key: (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)
  • Sort Method: quicksort Memory: 542kB
81. 1.266 6,904.058 ↑ 14.4 6,132 1

Merge Full Join (cost=1,500.15..2,834.08 rows=88,501 width=36) (actual time=6,902.434..6,904.058 rows=6,132 loops=1)

  • Merge 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))
82. 1.638 6,778.772 ↓ 4.7 6,073 1

Sort (cost=91.90..95.10 rows=1,283 width=12) (actual time=6,778.502..6,778.772 rows=6,073 loops=1)

  • Sort Key: cte2.c2
  • Sort Method: quicksort Memory: 477kB
83. 6,777.134 6,777.134 ↓ 4.7 6,073 1

CTE Scan on account_activity_count_cte_email_sent_current cte2 (cost=0.00..25.66 rows=1,283 width=12) (actual time=1.294..6,777.134 rows=6,073 loops=1)

84. 0.347 124.020 ↑ 6.8 2,041 1

Sort (cost=1,408.25..1,442.74 rows=13,796 width=24) (actual time=123.929..124.020 rows=2,041 loops=1)

  • Sort Key: (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)
  • Sort Method: quicksort Memory: 162kB
85. 0.376 123.673 ↑ 6.8 2,041 1

Merge Full Join (cost=245.01..459.64 rows=13,796 width=24) (actual time=123.183..123.673 rows=2,041 loops=1)

  • Merge Cond: (cte1_1.c2 = (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END))
86. 0.130 47.257 ↑ 2.6 600 1

Sort (cost=112.17..116.02 rows=1,538 width=12) (actual time=47.228..47.257 rows=600 loops=1)

  • Sort Key: cte1_1.c2
  • Sort Method: quicksort Memory: 53kB
87. 47.127 47.127 ↑ 2.6 600 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte1_1 (cost=0.00..30.76 rows=1,538 width=12) (actual time=0.578..47.127 rows=600 loops=1)

88. 0.424 76.040 ↓ 1.1 2,027 1

Sort (cost=132.84..137.32 rows=1,794 width=12) (actual time=75.950..76.040 rows=2,027 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 144kB
89. 75.616 75.616 ↓ 1.1 2,027 1

CTE Scan on account_activity_count_cte_meeting_current cte0_1 (cost=0.00..35.88 rows=1,794 width=12) (actual time=0.581..75.616 rows=2,027 loops=1)

90.          

CTE aggregated_domains_cte

91. 0.049 3.974 ↑ 1.2 40 1

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

  • Group Key: t1402_accountdomaindistinct.c7150_accountid
92. 0.048 3.925 ↓ 3.1 156 1

Sort (cost=555.32..555.45 rows=50 width=17) (actual time=3.917..3.925 rows=156 loops=1)

  • Sort Key: t1402_accountdomaindistinct.c7150_accountid
  • Sort Method: quicksort Memory: 34kB
93. 1.308 3.877 ↓ 3.1 156 1

Hash Semi Join (cost=1.30..553.91 rows=50 width=17) (actual time=0.035..3.877 rows=156 loops=1)

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

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

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 1,402
95. 0.004 0.011 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
96. 0.007 0.007 ↑ 1.0 40 1

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

97. 0.028 14,288.607 ↑ 1.0 40 1

Hash Right Join (cost=1.30..2.89 rows=40 width=132) (actual time=14,288.521..14,288.607 rows=40 loops=1)

  • Hash Cond: (cte1.c1 = cte0.c5)
98. 3.992 3.992 ↑ 1.2 40 1

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

99. 0.018 14,284.587 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
100. 14,284.569 14,284.569 ↑ 1.0 40 1

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

Planning time : 7.476 ms
Execution time : 14,293.934 ms