explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nj28

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 7,706.965 ↑ 1.0 40 1

Sort (cost=52,426,168.09..52,426,168.19 rows=40 width=132) (actual time=7,706.963..7,706.965 rows=40 loops=1)

  • Sort Key: cte0.c4
  • Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=669,895, temp read=1,241 written=1,328
2.          

CTE filtered_accounts_cte

3. 14.164 180.449 ↑ 1.5 35,856 1

Hash Join (cost=73,196.49..1,568,434.48 rows=55,530 width=40) (actual time=152.537..180.449 rows=35,856 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t2268_accountdim.sid)
  • Buffers: shared hit=54,567, temp read=190 written=188
4. 14.149 25.698 ↑ 1.1 35,856 1

Bitmap Heap Scan on t1401_accountfact (cost=13,897.51..1,503,241.29 rows=38,721 width=4) (actual time=11.668..25.698 rows=35,856 loops=1)

  • Recheck Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 23,736
  • Heap Blocks: exact=1,137
  • Buffers: shared hit=3,558
5. 11.195 11.195 ↑ 1.0 59,616 1

Bitmap Index Scan on idx_933_t1401_accountfact_sid_end_stamp_start_stamp_null (cost=0.00..13,887.83 rows=61,341 width=0) (actual time=11.195..11.195 rows=59,616 loops=1)

  • Index Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Buffers: shared hit=2,318
6.          

SubPlan (for Bitmap Heap Scan)

7. 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)
8. 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))
9. 0.000 0.000 ↓ 0.0 0

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

10. 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 = t1401_accountfact.c7136_ownerid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
11. 0.099 0.288 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
  • Buffers: shared hit=76
12. 0.111 0.143 ↑ 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.045..0.143 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
13. 0.032 0.032 ↑ 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.032..0.032 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=8
14. 0.007 0.046 ↓ 1.5 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=6
15. 0.039 0.039 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.005..0.039 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
  • Buffers: shared hit=6
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 = t1401_accountfact.c7136_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
17. 0.041 0.066 ↑ 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.036..0.066 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
  • Buffers: shared hit=27
18. 0.025 0.025 ↑ 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.025..0.025 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
19. 9.615 140.587 ↑ 2.1 35,941 1

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

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,801kB
  • Buffers: shared hit=51,009, temp written=134
20. 130.972 130.972 ↑ 2.1 35,941 1

Seq Scan on t2268_accountdim (cost=0.00..57,786.71 rows=74,422 width=40) (actual time=0.004..130.972 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint))
  • Rows Removed by Filter: 438,189
  • Buffers: shared hit=51,009
21.          

CTE account_activity_count_cte_meeting_current

22. 4.565 104.149 ↓ 1.1 1,861 1

Unique (cost=1,731,261.89..1,731,527.63 rows=1,710 width=4) (actual time=93.544..104.149 rows=1,861 loops=1)

  • Buffers: shared hit=5,110, temp read=117 written=118
23. 39.821 99.584 ↓ 1.3 67,811 1

Sort (cost=1,731,261.89..1,731,394.76 rows=53,149 width=4) (actual time=93.541..99.584 rows=67,811 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9010_accountid
  • Sort Method: external merge Disk: 936kB
  • Buffers: shared hit=5,110, temp read=117 written=118
24. 59.388 59.763 ↓ 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=4) (actual time=0.412..59.763 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 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9))
  • Rows Removed by Filter: 6,979
  • Buffers: shared hit=5,110
25.          

SubPlan (for Index Scan)

26. 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)
27. 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))
28. 0.000 0.000 ↓ 0.0 0

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

29. 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.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
30. 0.106 0.315 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
  • Buffers: shared hit=76
31. 0.116 0.158 ↑ 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.055..0.158 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
32. 0.042 0.042 ↑ 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.042..0.042 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=8
33. 0.010 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
  • Buffers: shared hit=6
34. 0.041 0.041 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.006..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
  • Buffers: shared hit=6
35. 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.c9004_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
36. 0.040 0.060 ↑ 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.030..0.060 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
  • Buffers: shared hit=27
37. 0.020 0.020 ↑ 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.020..0.020 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
38.          

CTE uniontop40sidfromothersctes

39. 0.005 7,702.995 ↑ 1.0 40 1

Limit (cost=49,125,668.26..49,125,669.36 rows=40 width=100) (actual time=7,702.953..7,702.995 rows=40 loops=1)

  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
40.          

CTE account_activity_count_cte_email_received_current

41. 0.005 2,844.635 ↑ 1.0 40 1

Limit (cost=20,807,522.79..20,813,698.32 rows=40 width=76) (actual time=2,844.512..2,844.635 rows=40 loops=1)

  • Buffers: shared hit=250,060, temp read=485 written=484
42. 0.100 2,844.630 ↑ 138.8 40 1

GroupAggregate (cost=20,807,522.79..21,664,841.08 rows=5,553 width=76) (actual time=2,844.511..2,844.630 rows=40 loops=1)

  • Group Key: cte0_1.c3, cte0_1.c1
  • Buffers: shared hit=250,060, temp read=485 written=484
43. 121.808 2,844.530 ↑ 323,495.4 265 1

Sort (cost=20,807,522.79..21,021,838.48 rows=85,726,276 width=40) (actual time=2,844.491..2,844.530 rows=265 loops=1)

  • Sort Key: cte0_1.c3, cte0_1.c1
  • Sort Method: external merge Disk: 1,424kB
  • Buffers: shared hit=250,060, temp read=485 written=484
44. 10.502 2,722.722 ↑ 1,790.5 47,879 1

Merge Join (cost=1,188,627.16..2,479,461.18 rows=85,726,276 width=40) (actual time=2,698.654..2,722.722 rows=47,879 loops=1)

  • Merge Cond: (cte0_1.c1 = t2608_emailactivityaccountfact_copy_sentha.c9638_accountid)
  • Buffers: shared hit=250,060, temp read=307 written=306
45. 14.716 207.537 ↑ 1.5 35,847 1

Sort (cost=5,486.64..5,625.46 rows=55,530 width=36) (actual time=203.300..207.537 rows=35,847 loops=1)

  • Sort Key: cte0_1.c1
  • Sort Method: quicksort Memory: 3,464kB
  • Buffers: shared hit=54,567, temp read=190 written=188
46. 192.821 192.821 ↑ 1.5 35,856 1

CTE Scan on filtered_accounts_cte cte0_1 (cost=0.00..1,110.60 rows=55,530 width=36) (actual time=152.540..192.821 rows=35,856 loops=1)

  • Buffers: shared hit=54,567, temp read=190 written=188
47. 5.509 2,504.683 ↑ 38.9 47,925 1

Materialize (cost=1,183,140.53..1,192,464.98 rows=1,864,890 width=8) (actual time=2,495.348..2,504.683 rows=47,925 loops=1)

  • Buffers: shared hit=195,493, temp read=117 written=118
48. 12.811 2,499.174 ↑ 38.9 47,925 1

Sort (cost=1,183,140.53..1,187,802.75 rows=1,864,890 width=8) (actual time=2,495.345..2,499.174 rows=47,925 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: quicksort Memory: 3,612kB
  • Buffers: shared hit=195,493, temp read=117 written=118
49. 400.781 2,486.363 ↑ 38.9 47,925 1

Hash Anti Join (cost=222,400.83..963,408.59 rows=1,864,890 width=8) (actual time=423.514..2,486.363 rows=47,925 loops=1)

  • Hash Cond: (t2608_emailactivityaccountfact_copy_sentha.c9638_accountid = account_activity_count_cte_meeting_current.c1)
  • Buffers: shared hit=195,493, temp read=117 written=118
50. 1,672.784 1,980.893 ↓ 1.1 3,924,982 1

Bitmap Heap Scan on t2608_emailactivityaccountfact_copy_sentha (cost=222,345.26..727,677.54 rows=3,729,780 width=8) (actual time=318.790..1,980.893 rows=3,924,982 loops=1)

  • Recheck Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint) AND c9657_isshared AND (NOT deleted))
  • Rows Removed by Index Recheck: 910,911
  • Filter: ((NOT c9660_isinternalinitiated) AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint))
  • Rows Removed by Filter: 1,555,624
  • Heap Blocks: exact=57,725 lossy=99,514
  • Buffers: shared hit=190,383
51. 308.109 308.109 ↓ 1.0 4,371,784 1

Bitmap Index Scan on idx_isinternal_sentha1 (cost=0.00..221,412.81 rows=4,348,164 width=0) (actual time=308.108..308.109 rows=4,371,784 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = false) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Buffers: shared hit=33,144
52. 0.225 104.689 ↓ 1.1 1,861 1

Hash (cost=34.20..34.20 rows=1,710 width=4) (actual time=104.689..104.689 rows=1,861 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=5,110, temp read=117 written=118
53. 104.464 104.464 ↓ 1.1 1,861 1

CTE Scan on account_activity_count_cte_meeting_current (cost=0.00..34.20 rows=1,710 width=4) (actual time=93.547..104.464 rows=1,861 loops=1)

  • Buffers: shared hit=5,110, temp read=117 written=118
54.          

CTE account_activity_count_cte_email_sent_current

55. 0.005 4,832.361 ↑ 1.0 40 1

Limit (cost=28,276,533.86..28,284,895.70 rows=40 width=76) (actual time=4,831.401..4,832.361 rows=40 loops=1)

  • Buffers: shared hit=418,369, temp read=756 written=844
56. 0.441 4,832.356 ↑ 138.8 40 1

GroupAggregate (cost=28,276,533.86..29,437,365.21 rows=5,553 width=76) (actual time=4,831.399..4,832.356 rows=40 loops=1)

  • Group Key: cte0_2.c3, cte0_2.c1
  • Buffers: shared hit=418,369, temp read=756 written=844
57. 187.245 4,831.915 ↑ 66,942.1 1,734 1

Sort (cost=28,276,533.86..28,566,727.82 rows=116,077,582 width=40) (actual time=4,831.382..4,831.915 rows=1,734 loops=1)

  • Sort Key: cte0_2.c3, cte0_2.c1
  • Sort Method: external merge Disk: 4,360kB
  • Buffers: shared hit=418,369, temp read=756 written=844
58. 23.798 4,644.670 ↑ 868.2 133,693 1

Merge Join (cost=1,457,890.96..3,205,644.86 rows=116,077,582 width=40) (actual time=4,587.145..4,644.670 rows=133,693 loops=1)

  • Merge Cond: (cte0_2.c1 = t2608_emailactivityaccountfact_copy_sentha_1.c9638_accountid)
  • Buffers: shared hit=418,369, temp read=296 written=297
59. 14.352 19.026 ↑ 1.5 35,851 1

Sort (cost=5,486.64..5,625.46 rows=55,530 width=36) (actual time=14.538..19.026 rows=35,851 loops=1)

  • Sort Key: cte0_2.c1
  • Sort Method: quicksort Memory: 3,464kB
60. 4.674 4.674 ↑ 1.5 35,856 1

CTE Scan on filtered_accounts_cte cte0_2 (cost=0.00..1,110.60 rows=55,530 width=36) (actual time=0.001..4.674 rows=35,856 loops=1)

61. 15.397 4,601.846 ↑ 18.9 133,896 1

Materialize (cost=1,452,404.32..1,465,030.08 rows=2,525,152 width=8) (actual time=4,572.602..4,601.846 rows=133,896 loops=1)

  • Buffers: shared hit=418,369, temp read=296 written=297
62. 72.819 4,586.449 ↑ 18.9 133,896 1

Sort (cost=1,452,404.32..1,458,717.20 rows=2,525,152 width=8) (actual time=4,572.598..4,586.449 rows=133,896 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha_1.c9638_accountid
  • Sort Method: external merge Disk: 2,368kB
  • Buffers: shared hit=418,369, temp read=296 written=297
63. 491.461 4,513.630 ↑ 18.9 133,896 1

Hash Anti Join (cost=55.58..1,114,832.43 rows=2,525,152 width=8) (actual time=0.347..4,513.630 rows=133,896 loops=1)

  • Hash Cond: (t2608_emailactivityaccountfact_copy_sentha_1.c9638_accountid = account_activity_count_cte_meeting_current_1.c1)
  • Buffers: shared hit=418,369
64. 4,021.844 4,021.844 ↑ 1.0 4,829,863 1

Seq Scan on t2608_emailactivityaccountfact_copy_sentha t2608_emailactivityaccountfact_copy_sentha_1 (cost=0.00..795,660.76 rows=5,050,305 width=8) (actual time=0.011..4,021.844 rows=4,829,863 loops=1)

  • Filter: ((NOT deleted) AND c9660_isinternalinitiated AND c9657_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint))
  • Rows Removed by Filter: 14,034,726
  • Buffers: shared hit=418,369
65. 0.170 0.325 ↓ 1.1 1,861 1

Hash (cost=34.20..34.20 rows=1,710 width=4) (actual time=0.324..0.325 rows=1,861 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
66. 0.155 0.155 ↓ 1.1 1,861 1

CTE Scan on account_activity_count_cte_meeting_current account_activity_count_cte_meeting_current_1 (cost=0.00..34.20 rows=1,710 width=4) (actual time=0.001..0.155 rows=1,861 loops=1)

67.          

CTE account_activity_count_cte_meeting_upcoming

68. 0.004 25.671 ↑ 1.3 30 1

Limit (cost=27,053.24..27,062.89 rows=40 width=76) (actual time=25.631..25.671 rows=30 loops=1)

  • Buffers: shared hit=1,211
69. 0.047 25.667 ↑ 185.1 30 1

GroupAggregate (cost=27,053.24..28,393.59 rows=5,553 width=76) (actual time=25.629..25.667 rows=30 loops=1)

  • Group Key: cte0_3.c3, cte0_3.c1
  • Buffers: shared hit=1,211
70. 0.060 25.620 ↑ 2,072.3 62 1

Sort (cost=27,053.24..27,374.44 rows=128,482 width=40) (actual time=25.616..25.620 rows=62 loops=1)

  • Sort Key: cte0_3.c3, cte0_3.c1
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=1,211
71. 2.675 25.560 ↑ 2,072.3 62 1

Merge Join (cost=10,572.64..12,636.77 rows=128,482 width=40) (actual time=18.999..25.560 rows=62 loops=1)

  • Merge Cond: (t2273_meetingactivityaccountfact_1.c9010_accountid = cte0_3.c1)
  • Buffers: shared hit=1,211
72. 0.613 5.494 ↑ 63.8 62 1

Merge Anti Join (cost=5,086.01..5,213.25 rows=3,956 width=8) (actual time=4.461..5.494 rows=62 loops=1)

  • Merge Cond: (t2273_meetingactivityaccountfact_1.c9010_accountid = account_activity_count_cte_meeting_current_2.c1)
  • Buffers: shared hit=1,211
73. 1.248 4.464 ↑ 1.7 4,766 1

Sort (cost=4,959.98..4,979.76 rows=7,913 width=8) (actual time=4.127..4.464 rows=4,766 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact_1.c9010_accountid
  • Sort Method: quicksort Memory: 416kB
  • Buffers: shared hit=1,211
74. 2.414 3.216 ↑ 1.7 4,766 1

Bitmap Heap Scan on t2273_meetingactivityaccountfact t2273_meetingactivityaccountfact_1 (cost=492.35..4,447.61 rows=7,913 width=8) (actual time=0.922..3.216 rows=4,766 loops=1)

  • Recheck Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint) AND c8998_isshared AND (NOT deleted))
  • Heap Blocks: exact=1,124
  • Buffers: shared hit=1,211
75. 0.802 0.802 ↑ 1.7 4,766 1

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

  • Index Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Buffers: shared hit=87
76. 0.260 0.417 ↓ 1.1 1,861 1

Sort (cost=126.03..130.30 rows=1,710 width=4) (actual time=0.307..0.417 rows=1,861 loops=1)

  • Sort Key: account_activity_count_cte_meeting_current_2.c1
  • Sort Method: quicksort Memory: 136kB
77. 0.157 0.157 ↓ 1.1 1,861 1

CTE Scan on account_activity_count_cte_meeting_current account_activity_count_cte_meeting_current_2 (cost=0.00..34.20 rows=1,710 width=4) (actual time=0.001..0.157 rows=1,861 loops=1)

78. 12.715 17.391 ↑ 1.5 35,854 1

Sort (cost=5,486.64..5,625.46 rows=55,530 width=36) (actual time=14.524..17.391 rows=35,854 loops=1)

  • Sort Key: cte0_3.c1
  • Sort Method: quicksort Memory: 3,464kB
79. 4.676 4.676 ↑ 1.5 35,856 1

CTE Scan on filtered_accounts_cte cte0_3 (cost=0.00..1,110.60 rows=55,530 width=36) (actual time=0.001..4.676 rows=35,856 loops=1)

80. 0.046 7,702.990 ↑ 3.0 40 1

GroupAggregate (cost=11.34..14.64 rows=120 width=100) (actual time=7,702.952..7,702.990 rows=40 loops=1)

  • Group Key: a.c3, a.c2
  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
81. 0.130 7,702.944 ↑ 1.9 63 1

Sort (cost=11.34..11.64 rows=120 width=76) (actual time=7,702.941..7,702.944 rows=63 loops=1)

  • Sort Key: a.c3, a.c2
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
82. 0.013 7,702.814 ↑ 1.1 110 1

Subquery Scan on a (cost=4.80..7.20 rows=120 width=76) (actual time=7,702.783..7,702.814 rows=110 loops=1)

  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
83. 0.071 7,702.801 ↑ 1.1 110 1

HashAggregate (cost=4.80..6.00 rows=120 width=76) (actual time=7,702.783..7,702.801 rows=110 loops=1)

  • Group Key: account_activity_count_cte_email_received_current.c1, account_activity_count_cte_email_received_current.c2, account_activity_count_cte_email_received_current.c3, account_activity_count_cte_email_received_current.c4
  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
84. 0.016 7,702.730 ↑ 1.1 110 1

Append (cost=0.00..3.60 rows=120 width=76) (actual time=2,844.515..7,702.730 rows=110 loops=1)

  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
85. 2,844.652 2,844.652 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_email_received_current (cost=0.00..0.80 rows=40 width=76) (actual time=2,844.515..2,844.652 rows=40 loops=1)

  • Buffers: shared hit=250,060, temp read=485 written=484
86. 4,832.378 4,832.378 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_email_sent_current (cost=0.00..0.80 rows=40 width=76) (actual time=4,831.403..4,832.378 rows=40 loops=1)

  • Buffers: shared hit=418,369, temp read=756 written=844
87. 25.684 25.684 ↑ 1.3 30 1

CTE Scan on account_activity_count_cte_meeting_upcoming (cost=0.00..0.80 rows=40 width=76) (actual time=25.632..25.684 rows=30 loops=1)

  • Buffers: shared hit=1,211
88.          

CTE aggregated_domains_cte

89. 0.043 3.849 ↑ 1.2 40 1

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

  • Group Key: t2275_accountdomaindistinct.c9105_accountid
  • Buffers: shared hit=255
90. 0.019 3.806 ↓ 1.2 62 1

Sort (cost=531.67..531.79 rows=50 width=18) (actual time=3.802..3.806 rows=62 loops=1)

  • Sort Key: t2275_accountdomaindistinct.c9105_accountid
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=255
91. 1.447 3.787 ↓ 1.2 62 1

Hash Semi Join (cost=1.30..530.26 rows=50 width=18) (actual time=0.208..3.787 rows=62 loops=1)

  • Hash Cond: (t2275_accountdomaindistinct.c9105_accountid = cte0_4.c3)
  • Buffers: shared hit=255
92. 2.328 2.328 ↑ 1.0 17,757 1

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

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 419
  • Buffers: shared hit=255
93. 0.006 0.012 ↑ 1.0 40 1

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

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

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

95. 0.024 7,706.917 ↑ 1.0 40 1

Hash Right Join (cost=1.30..2.89 rows=40 width=132) (actual time=7,706.847..7,706.917 rows=40 loops=1)

  • Hash Cond: (cte1.c1 = cte0.c3)
  • Buffers: shared hit=669,895, temp read=1,241 written=1,328
96. 3.865 3.865 ↑ 1.2 40 1

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

  • Buffers: shared hit=255
97. 0.016 7,703.028 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
98. 7,703.012 7,703.012 ↑ 1.0 40 1

CTE Scan on uniontop40sidfromothersctes cte0 (cost=0.00..0.80 rows=40 width=100) (actual time=7,702.955..7,703.012 rows=40 loops=1)

  • Buffers: shared hit=669,640, temp read=1,241 written=1,328
Planning time : 4.923 ms
Execution time : 7,712.288 ms