explain.depesz.com

PostgreSQL's explain analyze made readable

Result: orO

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 3,014.577 ↑ 1.0 40 1

Sort (cost=2,308,639.48..2,308,639.58 rows=40 width=132) (actual time=3,014.575..3,014.577 rows=40 loops=1)

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

CTE filtered_accounts_cte

3. 18.492 246.251 ↑ 1.2 35,856 1

Hash Join (cost=48,118.87..1,465,977.59 rows=43,344 width=40) (actual time=212.446..246.251 rows=35,856 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t1103_accountdim.sid)
4. 15.637 78.278 ↑ 1.0 35,856 1

Bitmap Heap Scan on t1401_accountfact (cost=13,619.72..1,426,093.09 rows=37,377 width=4) (actual time=62.822..78.278 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,097
5. 62.158 62.158 ↓ 1.0 60,144 1

Bitmap Index Scan on idx_933_t1401_accountfact_sid_end_stamp_start_stamp_null (cost=0.00..13,610.37 rows=59,211 width=0) (actual time=62.158..62.158 rows=60,144 loops=1)

  • Index Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
6.          

SubPlan (for Bitmap Heap Scan)

7. 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)
8. 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))
9. 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))
10. 0.137 0.413 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
11. 0.174 0.222 ↑ 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.066..0.222 rows=354 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 0.048 0.048 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..6.82 rows=18 width=4) (actual time=0.007..0.048 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
15. 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))
16. 0.048 0.070 ↑ 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.070 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
17. 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))
18. 11.391 149.481 ↑ 1.6 35,941 1

Hash (cost=33,321.68..33,321.68 rows=57,958 width=40) (actual time=149.481..149.481 rows=35,941 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,801kB
19. 138.090 138.090 ↑ 1.6 35,941 1

Seq Scan on t1103_accountdim (cost=0.00..33,321.68 rows=57,958 width=40) (actual time=0.006..138.090 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint))
  • Rows Removed by Filter: 398,050
20.          

CTE account_activity_count_cte_meeting_current

21. 0.004 370.098 ↑ 1.0 40 1

Limit (cost=10,909.03..10,909.13 rows=40 width=76) (actual time=370.093..370.098 rows=40 loops=1)

22. 6.021 370.094 ↑ 108.3 40 1

Sort (cost=10,909.03..10,919.86 rows=4,334 width=76) (actual time=370.092..370.094 rows=40 loops=1)

  • Sort Key: (count(DISTINCT t1662_meetingactivityaccountfact.c7801_activitychecksum)) DESC, cte0_1.c3 NULLS FIRST
  • Sort Method: top-N heapsort Memory: 30kB
23. 35.052 364.073 ↓ 8.3 35,856 1

GroupAggregate (cost=10,186.89..10,772.03 rows=4,334 width=76) (actual time=324.450..364.073 rows=35,856 loops=1)

  • Group Key: cte0_1.c1, cte0_1.c2, cte0_1.c3
24. 19.992 329.021 ↑ 1.2 35,964 1

Sort (cost=10,186.89..10,295.25 rows=43,344 width=72) (actual time=324.431..329.021 rows=35,964 loops=1)

  • Sort Key: cte0_1.c1, cte0_1.c2, cte0_1.c3 NULLS FIRST
  • Sort Method: quicksort Memory: 4,015kB
25. 6.590 309.029 ↑ 1.2 35,964 1

Hash Left Join (cost=5,111.49..6,848.63 rows=43,344 width=72) (actual time=256.765..309.029 rows=35,964 loops=1)

  • Hash Cond: (cte0_1.c1 = t1662_meetingactivityaccountfact.c7809_accountid)
26. 258.137 258.137 ↑ 1.2 35,856 1

CTE Scan on filtered_accounts_cte cte0_1 (cost=0.00..866.88 rows=43,344 width=68) (actual time=212.447..258.137 rows=35,856 loops=1)

27. 0.006 44.302 ↓ 17.8 249 1

Hash (cost=5,111.32..5,111.32 rows=14 width=8) (actual time=44.302..44.302 rows=249 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
28. 29.073 44.296 ↓ 17.8 249 1

Gather (cost=1,000.00..5,111.32 rows=14 width=8) (actual time=1.495..44.296 rows=249 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
29. 15.223 15.223 ↓ 15.5 124 2 / 2

Parallel Seq Scan on t1662_meetingactivityaccountfact (cost=0.00..4,109.92 rows=8 width=8) (actual time=0.357..15.223 rows=124 loops=2)

  • Filter: ((NOT deleted) AND c7805_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c7794_activitydate >= '1592550000000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 60,466
30.          

CTE account_activity_count_cte_meeting_upcoming

31. 0.112 41.935 ↑ 1.0 40 1

GroupAggregate (cost=5,114.68..5,115.58 rows=40 width=76) (actual time=41.838..41.935 rows=40 loops=1)

  • Group Key: cte0_2.c2, cte0_2.c3, cte0_2.c4
32. 0.083 41.823 ↓ 2.9 114 1

Sort (cost=5,114.68..5,114.78 rows=40 width=72) (actual time=41.814..41.823 rows=114 loops=1)

  • Sort Key: cte0_2.c2, cte0_2.c3, cte0_2.c4
  • Sort Method: quicksort Memory: 34kB
33. 0.056 41.740 ↓ 2.9 114 1

Hash Right Join (cost=1,001.30..5,113.61 rows=40 width=72) (actual time=9.935..41.740 rows=114 loops=1)

  • Hash Cond: (t1662_meetingactivityaccountfact_1.c7809_accountid = cte0_2.c2)
34. 26.337 41.665 ↓ 15.1 348 1

Gather (cost=1,000.00..5,112.22 rows=23 width=8) (actual time=1.452..41.665 rows=348 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
35. 15.328 15.328 ↓ 12.4 174 2 / 2

Parallel Seq Scan on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.00..4,109.92 rows=14 width=8) (actual time=0.302..15.328 rows=174 loops=2)

  • Filter: ((NOT deleted) AND c7805_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c7794_activitydate >= '1593241200000'::bigint) AND (c7794_activitydate < '1595833200000'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 60,416
36. 0.009 0.019 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=68) (actual time=0.019..0.019 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
37. 0.010 0.010 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0_2 (cost=0.00..0.80 rows=40 width=68) (actual time=0.001..0.010 rows=40 loops=1)

38.          

CTE account_activity_count_cte_email_sent_current

39. 0.122 1,239.455 ↑ 1.0 40 1

GroupAggregate (cost=413,016.69..413,017.59 rows=40 width=76) (actual time=1,239.333..1,239.455 rows=40 loops=1)

  • Group Key: cte0_3.c2, cte0_3.c3, cte0_3.c4
40. 0.185 1,239.333 ↓ 7.2 286 1

Sort (cost=413,016.69..413,016.79 rows=40 width=72) (actual time=1,239.318..1,239.333 rows=286 loops=1)

  • Sort Key: cte0_3.c2, cte0_3.c3, cte0_3.c4
  • Sort Method: quicksort Memory: 50kB
41. 0.313 1,239.148 ↓ 7.2 286 1

Hash Right Join (cost=1,001.30..413,015.63 rows=40 width=72) (actual time=207.795..1,239.148 rows=286 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact.c8021_accountid = cte0_3.c2)
42. 43.765 1,238.812 ↓ 3.9 2,102 1

Gather (cost=1,000.00..413,012.12 rows=544 width=8) (actual time=182.780..1,238.812 rows=2,102 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
43. 1,195.047 1,195.047 ↓ 3.1 701 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact (cost=0.00..411,957.72 rows=227 width=8) (actual time=140.353..1,195.047 rows=701 loops=3)

  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c8036_activitydate >= '1592550000000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 4,361,025
44. 0.012 0.023 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=68) (actual time=0.023..0.023 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
45. 0.011 0.011 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0_3 (cost=0.00..0.80 rows=40 width=68) (actual time=0.002..0.011 rows=40 loops=1)

46.          

CTE account_activity_count_cte_email_received_current

47. 0.084 1,727.042 ↑ 1.0 40 1

GroupAggregate (cost=413,005.67..413,006.57 rows=40 width=76) (actual time=1,726.965..1,727.042 rows=40 loops=1)

  • Group Key: cte0_4.c2, cte0_4.c3, cte0_4.c4
48. 0.136 1,726.958 ↓ 4.5 179 1

Sort (cost=413,005.67..413,005.77 rows=40 width=72) (actual time=1,726.950..1,726.958 rows=179 loops=1)

  • Sort Key: cte0_4.c2, cte0_4.c3, cte0_4.c4
  • Sort Method: quicksort Memory: 40kB
49. 0.183 1,726.822 ↓ 4.5 179 1

Hash Right Join (cost=1,001.30..413,004.60 rows=40 width=72) (actual time=563.485..1,726.822 rows=179 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact_1.c8021_accountid = cte0_4.c2)
50. 48.402 1,356.513 ↓ 1.8 798 1

Gather (cost=1,000.00..413,001.52 rows=438 width=8) (actual time=193.346..1,356.513 rows=798 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
51. 1,308.111 1,308.111 ↓ 1.5 266 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..411,957.72 rows=182 width=8) (actual time=152.335..1,308.111 rows=266 loops=3)

  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c8036_activitydate >= '1592550000000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 4,361,460
52. 0.014 370.126 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=68) (actual time=370.126..370.126 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
53. 370.112 370.112 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0_4 (cost=0.00..0.80 rows=40 width=68) (actual time=370.095..370.112 rows=40 loops=1)

54.          

CTE aggregated_domains_cte

55. 0.045 5.851 ↑ 1.2 40 1

GroupAggregate (cost=602.53..603.53 rows=50 width=36) (actual time=5.813..5.851 rows=40 loops=1)

  • Group Key: t1402_accountdomaindistinct.c7150_accountid
56. 0.035 5.806 ↓ 2.0 99 1

Sort (cost=602.53..602.65 rows=50 width=17) (actual time=5.800..5.806 rows=99 loops=1)

  • Sort Key: t1402_accountdomaindistinct.c7150_accountid
  • Sort Method: quicksort Memory: 31kB
57. 1.919 5.771 ↓ 2.0 99 1

Hash Semi Join (cost=1.30..601.12 rows=50 width=17) (actual time=0.043..5.771 rows=99 loops=1)

  • Hash Cond: (t1402_accountdomaindistinct.c7150_accountid = cte0_5.c2)
58. 3.834 3.834 ↑ 1.0 17,600 1

Seq Scan on t1402_accountdomaindistinct (cost=0.00..552.23 rows=17,916 width=17) (actual time=0.009..3.834 rows=17,600 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint))
  • Rows Removed by Filter: 1,403
59. 0.009 0.018 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
60. 0.009 0.009 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0_5 (cost=0.00..0.80 rows=40 width=4) (actual time=0.001..0.009 rows=40 loops=1)

61. 0.020 3,014.532 ↑ 1.0 40 1

Hash Join (cost=5.20..8.44 rows=40 width=132) (actual time=3,014.427..3,014.532 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte3.c2)
62. 0.023 1,287.436 ↑ 1.0 40 1

Hash Join (cost=3.90..6.59 rows=40 width=132) (actual time=1,287.344..1,287.436 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte2.c2)
63. 0.026 47.922 ↑ 1.0 40 1

Hash Join (cost=2.60..4.74 rows=40 width=120) (actual time=47.841..47.922 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte1.c2)
64. 0.026 5.918 ↑ 1.0 40 1

Hash Right Join (cost=1.30..2.89 rows=40 width=108) (actual time=5.848..5.918 rows=40 loops=1)

  • Hash Cond: (cte4.c1 = cte0.c2)
65. 5.870 5.870 ↑ 1.2 40 1

CTE Scan on aggregated_domains_cte cte4 (cost=0.00..1.00 rows=50 width=36) (actual time=5.815..5.870 rows=40 loops=1)

66. 0.011 0.022 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=76) (actual time=0.022..0.022 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
67. 0.011 0.011 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0 (cost=0.00..0.80 rows=40 width=76) (actual time=0.002..0.011 rows=40 loops=1)

68. 0.016 41.978 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=12) (actual time=41.978..41.978 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
69. 41.962 41.962 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte1 (cost=0.00..0.80 rows=40 width=12) (actual time=41.841..41.962 rows=40 loops=1)

70. 0.011 1,239.491 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=12) (actual time=1,239.491..1,239.491 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
71. 1,239.480 1,239.480 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_email_sent_current cte2 (cost=0.00..0.80 rows=40 width=12) (actual time=1,239.339..1,239.480 rows=40 loops=1)

72. 0.014 1,727.076 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=12) (actual time=1,727.076..1,727.076 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
73. 1,727.062 1,727.062 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_email_received_current cte3 (cost=0.00..0.80 rows=40 width=12) (actual time=1,726.967..1,727.062 rows=40 loops=1)

Planning time : 42.425 ms
Execution time : 3,017.072 ms