explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWUj

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 4,283.406 ↑ 1.1 38 1

Sort (cost=4,527,592.93..4,527,593.03 rows=40 width=100) (actual time=4,283.403..4,283.406 rows=38 loops=1)

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

CTE filtered_users_cte

3. 0.283 0.622 ↓ 1.3 387 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=90.42..6,312.73 rows=293 width=37) (actual time=0.414..0.622 rows=387 loops=1)

  • Recheck Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR $2))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=45
4.          

Initplan (for Bitmap Heap Scan)

5. 0.006 0.022 ↑ 1.0 1 1

Bitmap Heap Scan on t773_userdim (cost=4.97..53.21 rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (sid = sid) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
6. 0.016 0.016 ↑ 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.016..0.016 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
7. 0.050 0.050 ↑ 1.0 388 1

Bitmap Index Scan on t773_userdim_sidendstampunique (cost=0.00..37.14 rows=391 width=0) (actual time=0.050..0.050 rows=388 loops=1)

  • Index Cond: (end_stamp = '32503680000000'::bigint)
8.          

SubPlan (for Bitmap Heap 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 = t773_userdim_1.sid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
13. 0.084 0.267 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
14. 0.109 0.140 ↑ 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.043..0.140 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
15. 0.031 0.031 ↑ 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.031..0.031 rows=359 loops=1)

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

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

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

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.003..0.035 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.          

CTE primarysortedusercte_meeting_current

19. 0.004 80.642 ↑ 1.0 40 1

Limit (cost=2,237,399.16..2,237,399.26 rows=40 width=76) (actual time=80.637..80.642 rows=40 loops=1)

20.          

CTE innerprimarysortedusercte

21. 13.877 79.576 ↑ 1.5 137 1

GroupAggregate (cost=0.42..2,237,366.56 rows=200 width=12) (actual time=2.925..79.576 rows=137 loops=1)

  • Group Key: t2273_meetingactivityaccountfact.c9004_activityownersfuserid
22. 65.446 65.699 ↓ 1.2 79,692 1

Index Scan using "idx_ownersfuserid_activitydate_issharedp_del_P_sentha" on t2273_meetingactivityaccountfact (cost=0.42..2,237,019.69 rows=68,973 width=8) (actual time=0.558..65.699 rows=79,692 loops=1)

  • Index Cond: ((c8986_activitydate >= '1561532400000'::bigint) AND (c8986_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8))
  • Rows Removed by Filter: 8,346
23.          

SubPlan (for Index Scan)

24. 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)
25. 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))
26. 0.000 0.000 ↓ 0.0 0

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

27. 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))
28. 0.068 0.198 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
29. 0.067 0.097 ↑ 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.037..0.097 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
30. 0.030 0.030 ↑ 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.030..0.030 rows=359 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
32. 0.026 0.026 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.002..0.026 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
33. 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))
34. 0.033 0.055 ↑ 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.026..0.055 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
35. 0.022 0.022 ↑ 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.022..0.022 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
36. 0.149 80.638 ↑ 7.3 40 1

Sort (cost=32.61..33.34 rows=293 width=76) (actual time=80.636..80.638 rows=40 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c1 IS NULL) THEN '0'::bigint ELSE cte0_1.c1 END) DESC, cte1_1.c3 NULLS FIRST
  • Sort Method: top-N heapsort Memory: 31kB
37. 0.083 80.489 ↓ 1.3 387 1

Hash Left Join (cost=6.50..23.35 rows=293 width=76) (actual time=80.092..80.489 rows=387 loops=1)

  • Hash Cond: (cte1_1.c1 = cte0_1.c2)
38. 0.736 0.736 ↓ 1.3 387 1

CTE Scan on filtered_users_cte cte1_1 (cost=0.00..5.86 rows=293 width=68) (actual time=0.416..0.736 rows=387 loops=1)

39. 0.038 79.670 ↑ 1.5 137 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=79.670..79.670 rows=137 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
40. 79.632 79.632 ↑ 1.5 137 1

CTE Scan on innerprimarysortedusercte cte0_1 (cost=0.00..4.00 rows=200 width=12) (actual time=2.926..79.632 rows=137 loops=1)

41.          

CTE user_activity_count_cte_meeting_upcoming

42. 1.295 95.247 ↑ 1.0 40 1

GroupAggregate (cost=4,601.48..4,626.42 rows=40 width=76) (actual time=93.782..95.247 rows=40 loops=1)

  • Group Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
43. 7.094 93.952 ↓ 2.8 5,588 1

Sort (cost=4,601.48..4,606.39 rows=1,963 width=72) (actual time=93.703..93.952 rows=5,588 loops=1)

  • Sort Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
  • Sort Method: quicksort Memory: 629kB
44. 2.653 86.858 ↓ 2.8 5,588 1

Hash Right Join (cost=459.25..4,494.12 rows=1,963 width=72) (actual time=81.789..86.858 rows=5,588 loops=1)

  • Hash Cond: (t2273_meetingactivityaccountfact_1.c9004_activityownersfuserid = cte0_2.c2)
45. 2.605 3.544 ↓ 1.2 12,108 1

Bitmap Heap Scan on t2273_meetingactivityaccountfact t2273_meetingactivityaccountfact_1 (cost=457.95..4,434.68 rows=10,270 width=8) (actual time=1.120..3.544 rows=12,108 loops=1)

  • Recheck Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp = '32503680000000'::bigint) AND c8998_isshared AND (NOT deleted))
  • Heap Blocks: exact=1,841
46. 0.939 0.939 ↓ 1.2 12,108 1

Bitmap Index Scan on "idx_activitydate_issharedp_del_P_sentha" (cost=0.00..455.38 rows=10,270 width=0) (actual time=0.939..0.939 rows=12,108 loops=1)

  • Index Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
47. 0.011 80.661 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
48. 80.650 80.650 ↑ 1.0 40 1

CTE Scan on primarysortedusercte_meeting_current cte0_2 (cost=0.00..0.80 rows=40 width=68) (actual time=80.638..80.650 rows=40 loops=1)

49.          

CTE user_activity_count_cte_email_sent_current

50. 237.024 2,134.232 ↑ 5.2 38 1

GroupAggregate (cost=1,160,080.03..1,167,111.29 rows=198 width=12) (actual time=1,808.080..2,134.232 rows=38 loops=1)

  • Group Key: t2608_emailactivityaccountfact.c9662_activityownersfuserid
51. 315.396 1,897.208 ↓ 1.0 965,113 1

Sort (cost=1,160,080.03..1,162,423.12 rows=937,237 width=8) (actual time=1,807.119..1,897.208 rows=965,113 loops=1)

  • Sort Key: t2608_emailactivityaccountfact.c9662_activityownersfuserid
  • Sort Method: external merge Disk: 17,088kB
52. 150.523 1,581.812 ↓ 1.0 965,113 1

Gather (cost=1,000.00..1,054,298.24 rows=937,237 width=8) (actual time=0.907..1,581.812 rows=965,113 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
53. 1,431.289 1,431.289 ↑ 1.2 321,704 3 / 3

Parallel Seq Scan on t2608_emailactivityaccountfact (cost=0.00..959,574.54 rows=390,515 width=8) (actual time=4.535..1,431.289 rows=321,704 loops=3)

  • Filter: ((NOT deleted) AND c9660_isinternalinitiated AND c9657_isshared AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c9662_activityownersfuserid = ANY ('{14,17,32,39,40,53,62,72,75,91,97,99,100,117,125,128,136,139,146,154,157,168,463,529,595,661,760,762,859,926,958,1025,1057,1195,1255,1453,1586,1651,2278,234}'::integer[])))
  • Rows Removed by Filter: 5,966,492
54.          

CTE user_activity_count_cte_email_received_current

55. 209.414 2,053.595 ↑ 5.2 38 1

GroupAggregate (cost=1,106,917.30..1,112,128.31 rows=198 width=12) (actual time=1,766.651..2,053.595 rows=38 loops=1)

  • Group Key: t2608_emailactivityaccountfact_1.c9662_activityownersfuserid
56. 278.587 1,844.181 ↓ 1.3 881,096 1

Sort (cost=1,106,917.30..1,108,653.64 rows=694,538 width=8) (actual time=1,762.088..1,844.181 rows=881,096 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_1.c9662_activityownersfuserid
  • Sort Method: external merge Disk: 15,600kB
57. 131.834 1,565.594 ↓ 1.3 881,096 1

Gather (cost=1,000.00..1,030,028.34 rows=694,538 width=8) (actual time=2.337..1,565.594 rows=881,096 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
58. 1,433.760 1,433.760 ↓ 1.0 293,699 3 / 3

Parallel Seq Scan on t2608_emailactivityaccountfact t2608_emailactivityaccountfact_1 (cost=0.00..959,574.54 rows=289,391 width=8) (actual time=3.161..1,433.760 rows=293,699 loops=3)

  • Filter: ((NOT deleted) AND (NOT c9660_isinternalinitiated) AND c9657_isshared AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c9662_activityownersfuserid = ANY ('{14,17,32,39,40,53,62,72,75,91,97,99,100,117,125,128,136,139,146,154,157,168,463,529,595,661,760,762,859,926,958,1025,1057,1195,1255,1453,1586,1651,2278,234}'::integer[])))
  • Rows Removed by Filter: 5,994,498
59. 0.036 4,283.367 ↑ 1.1 38 1

Hash Join (cost=8.20..13.86 rows=40 width=100) (actual time=3,996.315..4,283.367 rows=38 loops=1)

  • Hash Cond: (cte0.c2 = cte1.c2)
60. 0.050 4,188.055 ↑ 1.1 38 1

Hash Join (cost=6.90..12.00 rows=40 width=100) (actual time=3,901.032..4,188.055 rows=38 loops=1)

  • Hash Cond: (cte3.c2 = cte0.c2)
61. 2,053.637 2,053.637 ↑ 5.2 38 1

CTE Scan on user_activity_count_cte_email_received_current cte3 (cost=0.00..3.96 rows=198 width=12) (actual time=1,766.654..2,053.637 rows=38 loops=1)

62. 0.030 2,134.368 ↑ 1.1 38 1

Hash (cost=6.40..6.40 rows=40 width=88) (actual time=2,134.368..2,134.368 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
63. 0.055 2,134.338 ↑ 1.1 38 1

Hash Join (cost=1.30..6.40 rows=40 width=88) (actual time=1,808.104..2,134.338 rows=38 loops=1)

  • Hash Cond: (cte2.c2 = cte0.c2)
64. 2,134.271 2,134.271 ↑ 5.2 38 1

CTE Scan on user_activity_count_cte_email_sent_current cte2 (cost=0.00..3.96 rows=198 width=12) (actual time=1,808.083..2,134.271 rows=38 loops=1)

65. 0.007 0.012 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
66. 0.005 0.005 ↑ 1.0 40 1

CTE Scan on primarysortedusercte_meeting_current cte0 (cost=0.00..0.80 rows=40 width=76) (actual time=0.001..0.005 rows=40 loops=1)

67. 0.010 95.276 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
68. 95.266 95.266 ↑ 1.0 40 1

CTE Scan on user_activity_count_cte_meeting_upcoming cte1 (cost=0.00..0.80 rows=40 width=12) (actual time=93.784..95.266 rows=40 loops=1)

Planning time : 3.761 ms
Execution time : 4,289.930 ms