explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pxgs

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 8,974.557 ↑ 1.0 40 1

Sort (cost=226,033,185.78..226,033,185.88 rows=40 width=132) (actual time=8,974.555..8,974.557 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.004 8,970.951 ↑ 1.0 40 1

Limit (cost=226,032,649.06..226,032,649.16 rows=40 width=100) (actual time=8,970.946..8,970.951 rows=40 loops=1)

4.          

CTE account_activity_count_cte_meeting_current

5. 12.873 109.192 ↓ 1.1 1,861 1

GroupAggregate (cost=1,731,261.89..1,731,677.60 rows=1,710 width=12) (actual time=91.191..109.192 rows=1,861 loops=1)

  • Group Key: t2273_meetingactivityaccountfact.c9010_accountid
6. 38.576 96.319 ↓ 1.3 67,811 1

Sort (cost=1,731,261.89..1,731,394.76 rows=53,149 width=8) (actual time=91.172..96.319 rows=67,811 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9010_accountid
  • Sort Method: external merge Disk: 1,200kB
7. 57.433 57.743 ↓ 1.3 67,811 1

Index Scan using "idx_activitydate_issharedp_del_P_sentha" on t2273_meetingactivityaccountfact (cost=0.42..1,727,090.29 rows=53,149 width=8) (actual time=0.341..57.743 rows=67,811 loops=1)

  • Index Cond: ((c8986_activitydate >= '1561532400000'::bigint) AND (c8986_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 6,979
8.          

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim.sid = t786_userrolehierarchy.c4812_user_role_id)
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim (cost=0.00..7.35 rows=29 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.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

12. 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 = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
13. 0.082 0.257 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.096..0.257 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
14. 0.099 0.134 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_1 (cost=16.01..88.47 rows=364 width=8) (actual time=0.047..0.134 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
15. 0.035 0.035 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.035..0.035 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
16. 0.007 0.041 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.041..0.041 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.034 0.034 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.004..0.034 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
18. 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 = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
19. 0.034 0.053 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=4.98..53.05 rows=68 width=4) (actual time=0.027..0.053 rows=27 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=23
20. 0.019 0.019 ↑ 1.9 36 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.018..0.019 rows=36 loops=1)

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

CTE account_activity_count_cte_meeting_upcoming

22. 1.017 6.276 ↑ 3.2 519 1

GroupAggregate (cost=195,437.70..195,498.81 rows=1,660 width=12) (actual time=5.029..6.276 rows=519 loops=1)

  • Group Key: t2273_meetingactivityaccountfact_1.c9010_accountid
23. 1.023 5.259 ↑ 1.3 4,596 1

Sort (cost=195,437.70..195,452.54 rows=5,935 width=8) (actual time=5.018..5.259 rows=4,596 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact_1.c9010_accountid
  • Sort Method: quicksort Memory: 408kB
24. 3.053 4.236 ↑ 1.3 4,596 1

Bitmap Heap Scan on t2273_meetingactivityaccountfact t2273_meetingactivityaccountfact_1 (cost=491.86..195,065.72 rows=5,935 width=8) (actual time=1.311..4.236 rows=4,596 loops=1)

  • Recheck Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint) AND c8998_isshared AND (NOT deleted))
  • Filter: ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9))
  • Rows Removed by Filter: 170
  • Heap Blocks: exact=1,124
25. 0.830 0.830 ↑ 1.7 4,766 1

Bitmap Index Scan on "idx_activitydate_issharedp_del_P_sentha" (cost=0.00..490.38 rows=7,914 width=0) (actual time=0.830..0.830 rows=4,766 loops=1)

  • Index Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
26.          

SubPlan (for Bitmap Heap Scan)

27. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim_2.sid = t786_userrolehierarchy_2.c4812_user_role_id)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_2 (cost=0.00..7.35 rows=29 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))
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

30. 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 = t2273_meetingactivityaccountfact_1.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
31. 0.094 0.292 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.111..0.292 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
32. 0.113 0.150 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_3 (cost=16.01..88.47 rows=364 width=8) (actual time=0.049..0.150 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
33. 0.037 0.037 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.037..0.037 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
34. 0.008 0.048 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.048..0.048 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.040 0.040 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.007..0.040 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
36. 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 = t2273_meetingactivityaccountfact_1.c9004_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
37. 0.038 0.061 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_3 (cost=4.98..53.05 rows=68 width=4) (actual time=0.032..0.061 rows=27 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=23
38. 0.023 0.023 ↑ 1.9 36 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=36 loops=1)

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

CTE account_activity_count_cte_email_sent_current

40. 347.986 4,996.602 ↓ 4.8 5,838 1

GroupAggregate (cost=123,963,818.63..123,992,240.74 rows=1,208 width=12) (actual time=4,455.799..4,996.602 rows=5,838 loops=1)

  • Group Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
41. 746.454 4,648.616 ↑ 1.9 2,047,388 1

Sort (cost=123,963,818.63..123,973,288.64 rows=3,788,004 width=8) (actual time=4,455.698..4,648.616 rows=2,047,388 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: external merge Disk: 36,112kB
42. 3,901.817 3,902.162 ↑ 1.9 2,047,388 1

Index Scan using idx_isinternal_activitydate_sentha1 on t2608_emailactivityaccountfact_copy_sentha (cost=0.56..123,446,343.25 rows=3,788,004 width=8) (actual time=0.392..3,902.162 rows=2,047,388 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = true) AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: (c9660_isinternalinitiated AND ((alternatives: SubPlan 11 or hashed SubPlan 12) OR (alternatives: SubPlan 13 or hashed SubPlan 14)))
  • Rows Removed by Filter: 2,782,498
43.          

SubPlan (for Index Scan)

44. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim_4.sid = t786_userrolehierarchy_4.c4812_user_role_id)
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_4 (cost=0.00..7.35 rows=29 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))
46. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

47. 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 = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
48. 0.090 0.288 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.111..0.288 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_5.c4812_user_role_id = t859_userroledim_5.sid)
49. 0.113 0.149 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_5 (cost=16.01..88.47 rows=364 width=8) (actual time=0.049..0.149 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
50. 0.036 0.036 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.036..0.036 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
51. 0.008 0.049 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.048..0.049 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
52. 0.041 0.041 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_5 (cost=0.00..7.35 rows=29 width=4) (actual time=0.007..0.041 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
53. 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 = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
54. 0.040 0.057 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_5 (cost=4.98..53.05 rows=68 width=4) (actual time=0.027..0.057 rows=27 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=23
55. 0.017 0.017 ↑ 1.9 36 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.017..0.017 rows=36 loops=1)

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

CTE account_activity_count_cte_email_received_current

57. 440.446 3,609.522 ↓ 3.1 3,721 1

GroupAggregate (cost=90,975,534.23..90,996,527.84 rows=1,208 width=12) (actual time=2,965.925..3,609.522 rows=3,721 loops=1)

  • Group Key: t2608_emailactivityaccountfact_copy_sentha_1.c9638_accountid
58. 704.676 3,169.076 ↑ 1.3 2,138,476 1

Sort (cost=90,975,534.23..90,982,528.07 rows=2,797,538 width=8) (actual time=2,965.718..3,169.076 rows=2,138,476 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha_1.c9638_accountid
  • Sort Method: external merge Disk: 37,712kB
59. 2,041.556 2,464.400 ↑ 1.3 2,138,476 1

Bitmap Heap Scan on t2608_emailactivityaccountfact_copy_sentha t2608_emailactivityaccountfact_copy_sentha_1 (cost=263,570.77..90,599,481.69 rows=2,797,538 width=8) (actual time=432.764..2,464.400 rows=2,138,476 loops=1)

  • Recheck Cond: ((c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint) AND c9657_isshared AND (NOT deleted))
  • Rows Removed by Index Recheck: 1,091,479
  • Filter: ((NOT c9660_isinternalinitiated) AND ((alternatives: SubPlan 16 or hashed SubPlan 17) OR (alternatives: SubPlan 18 or hashed SubPlan 19)))
  • Rows Removed by Filter: 2,771,967
  • Heap Blocks: exact=53,573 lossy=99,696
60. 422.494 422.494 ↓ 1.1 3,925,022 1

Bitmap Index Scan on idx_isinternal_activitydate_sentha1 (cost=0.00..262,871.38 rows=3,730,051 width=0) (actual time=422.494..422.494 rows=3,925,022 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = false) AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
61.          

SubPlan (for Bitmap Heap Scan)

62. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim_6.sid = t786_userrolehierarchy_6.c4812_user_role_id)
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_6 (cost=0.00..7.35 rows=29 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))
64. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

65. 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 = t2608_emailactivityaccountfact_copy_sentha_1.c9662_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
66. 0.098 0.294 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.118..0.294 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_7.c4812_user_role_id = t859_userroledim_7.sid)
67. 0.109 0.145 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_7 (cost=16.01..88.47 rows=364 width=8) (actual time=0.049..0.145 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
68. 0.036 0.036 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.036..0.036 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
69. 0.009 0.051 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.051..0.051 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
70. 0.042 0.042 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_7 (cost=0.00..7.35 rows=29 width=4) (actual time=0.007..0.042 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
71. 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 = t2608_emailactivityaccountfact_copy_sentha_1.c9662_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
72. 0.039 0.056 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_7 (cost=4.98..53.05 rows=68 width=4) (actual time=0.027..0.056 rows=27 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=23
73. 0.017 0.017 ↑ 1.9 36 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.017..0.017 rows=36 loops=1)

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

CTE filtered_accounts_cte

75. 15.918 203.389 ↑ 2.1 35,940 1

Hash Join (cost=59,298.97..84,990.69 rows=74,039 width=40) (actual time=139.055..203.389 rows=35,940 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t2268_accountdim.sid)
76. 48.850 48.850 ↑ 1.4 35,940 1

Seq Scan on t1401_accountfact (cost=0.00..18,028.11 rows=51,628 width=4) (actual time=0.005..48.850 rows=35,940 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 422,961
77. 9.000 138.621 ↑ 2.1 35,941 1

Hash (cost=57,786.71..57,786.71 rows=74,421 width=40) (actual time=138.621..138.621 rows=35,941 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,801kB
78. 129.621 129.621 ↑ 2.1 35,941 1

Seq Scan on t2268_accountdim (cost=0.00..57,786.71 rows=74,421 width=40) (actual time=0.003..129.621 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 438,189
79. 1.152 8,970.947 ↑ 4,792,035.5 40 1

Sort (cost=9,031,713.38..9,510,916.92 rows=191,681,418 width=100) (actual time=8,970.944..8,970.947 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
80. 6.139 8,969.795 ↑ 30,595.6 6,265 1

Merge Right Join (cost=95,846.74..2,972,732.67 rows=191,681,418 width=100) (actual time=8,959.563..8,969.795 rows=6,265 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))
81. 15.051 230.011 ↑ 2.1 35,940 1

Sort (cost=10,507.05..10,692.15 rows=74,039 width=68) (actual time=226.877..230.011 rows=35,940 loops=1)

  • Sort Key: cte4.c1
  • Sort Method: quicksort Memory: 4,013kB
82. 214.960 214.960 ↑ 2.1 35,940 1

CTE Scan on filtered_accounts_cte cte4 (cost=0.00..1,480.78 rows=74,039 width=68) (actual time=139.057..214.960 rows=35,940 loops=1)

83. 0.648 8,733.645 ↑ 82.6 6,265 1

Materialize (cost=85,339.69..87,928.61 rows=517,785 width=48) (actual time=8,732.681..8,733.645 rows=6,265 loops=1)

84. 1.521 8,732.997 ↑ 82.6 6,265 1

Sort (cost=85,339.69..86,634.15 rows=517,785 width=48) (actual time=8,732.679..8,732.997 rows=6,265 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: 793kB
85. 1.757 8,731.476 ↑ 82.6 6,265 1

Merge Full Join (cost=12,281.10..20,268.23 rows=517,785 width=48) (actual time=8,728.762..8,731.476 rows=6,265 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))
86. 0.523 3,610.970 ↓ 3.1 3,721 1

Sort (cost=86.00..89.02 rows=1,208 width=12) (actual time=3,610.799..3,610.970 rows=3,721 loops=1)

  • Sort Key: cte3.c2
  • Sort Method: quicksort Memory: 271kB
87. 3,610.447 3,610.447 ↓ 3.1 3,721 1

CTE Scan on account_activity_count_cte_email_received_current cte3 (cost=0.00..24.16 rows=1,208 width=12) (actual time=2,965.933..3,610.447 rows=3,721 loops=1)

88. 0.534 5,118.749 ↑ 14.5 5,900 1

Materialize (cost=12,195.10..12,623.73 rows=85,726 width=36) (actual time=5,117.959..5,118.749 rows=5,900 loops=1)

89. 1.207 5,118.215 ↑ 14.5 5,900 1

Sort (cost=12,195.10..12,409.41 rows=85,726 width=36) (actual time=5,117.957..5,118.215 rows=5,900 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: 526kB
90. 1.185 5,117.008 ↑ 14.5 5,900 1

Merge Full Join (cost=1,534.02..2,825.95 rows=85,726 width=36) (actual time=5,115.483..5,117.008 rows=5,900 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))
91. 0.814 4,998.766 ↓ 4.8 5,838 1

Sort (cost=86.00..89.02 rows=1,208 width=12) (actual time=4,998.504..4,998.766 rows=5,838 loops=1)

  • Sort Key: cte2.c2
  • Sort Method: quicksort Memory: 466kB
92. 4,997.952 4,997.952 ↓ 4.8 5,838 1

CTE Scan on account_activity_count_cte_email_sent_current cte2 (cost=0.00..24.16 rows=1,208 width=12) (actual time=4,455.802..4,997.952 rows=5,838 loops=1)

93. 0.339 117.057 ↑ 7.6 1,874 1

Sort (cost=1,448.02..1,483.50 rows=14,193 width=24) (actual time=116.974..117.057 rows=1,874 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: 152kB
94. 0.331 116.718 ↑ 7.6 1,874 1

Merge Full Join (cost=248.01..469.20 rows=14,193 width=24) (actual time=116.283..116.718 rows=1,874 loops=1)

  • Merge Cond: (cte1_1.c2 = (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END))
95. 0.084 6.463 ↑ 3.2 519 1

Sort (cost=121.98..126.13 rows=1,660 width=12) (actual time=6.438..6.463 rows=519 loops=1)

  • Sort Key: cte1_1.c2
  • Sort Method: quicksort Memory: 49kB
96. 6.379 6.379 ↑ 3.2 519 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte1_1 (cost=0.00..33.20 rows=1,660 width=12) (actual time=5.030..6.379 rows=519 loops=1)

97. 0.298 109.924 ↓ 1.1 1,861 1

Sort (cost=126.03..130.30 rows=1,710 width=12) (actual time=109.841..109.924 rows=1,861 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 136kB
98. 109.626 109.626 ↓ 1.1 1,861 1

CTE Scan on account_activity_count_cte_meeting_current cte0_1 (cost=0.00..34.20 rows=1,710 width=12) (actual time=91.193..109.626 rows=1,861 loops=1)

99.          

CTE aggregated_domains_cte

100. 0.050 3.508 ↑ 1.2 40 1

GroupAggregate (cost=531.67..532.67 rows=50 width=36) (actual time=3.464..3.508 rows=40 loops=1)

  • Group Key: t2275_accountdomaindistinct.c9105_accountid
101. 0.045 3.458 ↓ 2.8 142 1

Sort (cost=531.67..531.79 rows=50 width=18) (actual time=3.451..3.458 rows=142 loops=1)

  • Sort Key: t2275_accountdomaindistinct.c9105_accountid
  • Sort Method: quicksort Memory: 33kB
102. 1.295 3.413 ↓ 2.8 142 1

Hash Semi Join (cost=1.30..530.26 rows=50 width=18) (actual time=0.042..3.413 rows=142 loops=1)

  • Hash Cond: (t2275_accountdomaindistinct.c9105_accountid = cte0_2.c5)
103. 2.105 2.105 ↑ 1.0 17,757 1

Seq Scan on t2275_accountdomaindistinct (cost=0.00..480.95 rows=18,076 width=18) (actual time=0.010..2.105 rows=17,757 loops=1)

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 419
104. 0.007 0.013 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
105. 0.006 0.006 ↑ 1.0 40 1

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

106. 0.022 8,974.526 ↑ 1.0 40 1

Hash Right Join (cost=1.30..2.89 rows=40 width=132) (actual time=8,974.456..8,974.526 rows=40 loops=1)

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

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

108. 0.015 8,970.983 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
109. 8,970.968 8,970.968 ↑ 1.0 40 1

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

Planning time : 7.141 ms
Execution time : 8,992.958 ms