explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SaC4

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 18,314.909 ↑ 1.0 40 1

Sort (cost=4,185,670.47..4,185,670.57 rows=40 width=100) (actual time=18,314.907..18,314.909 rows=40 loops=1)

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

CTE filtered_users_cte

3. 0.458 0.913 ↓ 1.3 384 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=90.35..6,097.94 rows=290 width=37) (actual time=0.542..0.913 rows=384 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.009 0.035 ↑ 1.0 1 1

Bitmap Heap Scan on t773_userdim (cost=4.97..53.21 rows=1 width=0) (actual time=0.035..0.035 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.026 0.026 ↑ 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.026..0.026 rows=38 loops=1)

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

Bitmap Index Scan on t773_userdim_sidendstampunique (cost=0.00..37.07 rows=388 width=0) (actual time=0.062..0.062 rows=385 loops=1)

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

SubPlan (for Bitmap Heap Scan)

9. 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)
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 = t773_userdim_1.sid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
11. 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))
12. 0.109 0.358 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
13. 0.153 0.195 ↑ 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.061..0.195 rows=354 loops=1)

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

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
15. 0.009 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
16. 0.045 0.045 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..6.82 rows=18 width=4) (actual time=0.005..0.045 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
17.          

CTE primarysortedusercte_meeting_current

18. 0.005 166.421 ↑ 1.0 40 1

Limit (cost=2,867,051.23..2,867,051.33 rows=40 width=76) (actual time=166.415..166.421 rows=40 loops=1)

19.          

CTE innerprimarysortedusercte

20. 22.125 164.905 ↑ 1.5 138 1

GroupAggregate (cost=2,866,494.99..2,867,018.70 rows=202 width=12) (actual time=133.774..164.905 rows=138 loops=1)

  • Group Key: t1662_meetingactivityaccountfact.c7807_activityownersfuserid
21. 73.662 142.780 ↓ 1.2 82,158 1

Sort (cost=2,866,494.99..2,866,668.89 rows=69,559 width=8) (actual time=132.896..142.780 rows=82,158 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact.c7807_activityownersfuserid
  • Sort Method: external merge Disk: 1,456kB
22. 62.207 69.118 ↓ 1.2 82,158 1

Bitmap Heap Scan on t1662_meetingactivityaccountfact (cost=1,939.49..2,860,900.38 rows=69,559 width=8) (actual time=7.433..69.118 rows=82,158 loops=1)

  • Recheck Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561532400000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 36,659
  • Heap Blocks: exact=2,482
23. 6.305 6.305 ↓ 1.0 120,762 1

Bitmap Index Scan on t1662_meetingactivityaccountfact_931_ptimestamp (cost=0.00..1,922.10 rows=120,666 width=0) (actual time=6.305..6.305 rows=120,762 loops=1)

24.          

SubPlan (for Bitmap Heap Scan)

25. 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)
26. 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.c7807_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
27. 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))
28. 0.153 0.498 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
29. 0.201 0.264 ↑ 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.083..0.264 rows=354 loops=1)

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

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
31. 0.012 0.081 ↓ 2.4 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
32. 0.069 0.069 ↓ 2.4 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..6.82 rows=18 width=4) (actual time=0.010..0.069 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 = 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))
34. 0.073 0.108 ↑ 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.049..0.108 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
35. 0.035 0.035 ↑ 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.035..0.035 rows=38 loops=1)

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

Sort (cost=32.53..33.26 rows=293 width=76) (actual time=166.414..166.416 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.129 166.210 ↓ 1.3 384 1

Hash Left Join (cost=6.57..23.27 rows=293 width=76) (actual time=165.587..166.210 rows=384 loops=1)

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

CTE Scan on filtered_users_cte cte1_1 (cost=0.00..5.80 rows=290 width=68) (actual time=0.545..1.053 rows=384 loops=1)

39. 0.049 165.028 ↑ 1.5 138 1

Hash (cost=4.04..4.04 rows=202 width=12) (actual time=165.028..165.028 rows=138 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
40. 164.979 164.979 ↑ 1.5 138 1

CTE Scan on innerprimarysortedusercte cte0_1 (cost=0.00..4.04 rows=202 width=12) (actual time=133.775..164.979 rows=138 loops=1)

41.          

CTE user_activity_count_cte_meeting_upcoming

42. 1.019 33.794 ↑ 1.0 40 1

GroupAggregate (cost=4,700.50..4,711.87 rows=40 width=76) (actual time=32.698..33.794 rows=40 loops=1)

  • Group Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
43. 4.885 32.775 ↓ 3.2 2,845 1

Sort (cost=4,700.50..4,702.69 rows=878 width=72) (actual time=32.567..32.775 rows=2,845 loops=1)

  • Sort Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
  • Sort Method: quicksort Memory: 319kB
44. 1.093 27.890 ↓ 3.2 2,845 1

Hash Right Join (cost=1.30..4,657.57 rows=878 width=72) (actual time=0.245..27.890 rows=2,845 loops=1)

  • Hash Cond: (t1662_meetingactivityaccountfact_1.c7807_activityownersfuserid = cte0_2.c2)
45. 26.772 26.772 ↓ 1.2 5,708 1

Seq Scan on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.00..4,630.28 rows=4,590 width=8) (actual time=0.207..26.772 rows=5,708 loops=1)

  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1593241200000'::bigint) AND (c7794_activitydate < '1595833200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 115,472
46. 0.013 0.025 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
47. 0.012 0.012 ↑ 1.0 40 1

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

48.          

CTE user_activity_count_cte_email_sent_current

49. 445.045 9,467.550 ↑ 1.0 40 1

GroupAggregate (cost=673,116.42..681,883.04 rows=40 width=76) (actual time=8,660.460..9,467.550 rows=40 loops=1)

  • Group Key: cte0_3.c1, cte0_3.c2, cte0_3.c3
50. 2,872.662 9,022.505 ↓ 1.4 994,494 1

Sort (cost=673,116.42..674,869.66 rows=701,298 width=72) (actual time=8,616.538..9,022.505 rows=994,494 loops=1)

  • Sort Key: cte0_3.c1, cte0_3.c2, cte0_3.c3
  • Sort Method: external merge Disk: 52,144kB
51. 732.019 6,149.843 ↓ 1.4 994,494 1

Hash Right Join (cost=1.30..547,488.55 rows=701,298 width=72) (actual time=0.082..6,149.843 rows=994,494 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact.c8045_activityownersfuserid = cte0_3.c2)
52. 5,417.801 5,417.801 ↓ 1.0 6,051,147 1

Seq Scan on t1816_emailactivityaccountfact (cost=0.00..518,284.48 rows=5,917,278 width=8) (actual time=0.016..5,417.801 rows=6,051,147 loops=1)

  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 7,034,030
53. 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
54. 0.011 0.011 ↑ 1.0 40 1

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

55.          

CTE user_activity_count_cte_email_received_current

56. 383.019 8,812.899 ↑ 1.0 40 1

GroupAggregate (cost=618,863.08..625,918.87 rows=40 width=76) (actual time=8,124.034..8,812.899 rows=40 loops=1)

  • Group Key: cte0_4.c1, cte0_4.c2, cte0_4.c3
57. 2,509.345 8,429.880 ↓ 1.6 903,743 1

Sort (cost=618,863.08..620,274.16 rows=564,431 width=72) (actual time=8,093.999..8,429.880 rows=903,743 loops=1)

  • Sort Key: cte0_4.c1, cte0_4.c2, cte0_4.c3
  • Sort Method: external merge Disk: 47,256kB
58. 608.163 5,920.535 ↓ 1.6 903,743 1

Hash Right Join (cost=1.30..541,789.25 rows=564,431 width=72) (actual time=166.477..5,920.535 rows=903,743 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact_1.c8045_activityownersfuserid = cte0_4.c2)
59. 5,145.920 5,145.920 ↑ 1.0 4,639,803 1

Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..518,284.48 rows=4,762,444 width=8) (actual time=0.013..5,145.920 rows=4,639,803 loops=1)

  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 8,445,374
60. 0.016 166.452 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
61. 166.436 166.436 ↑ 1.0 40 1

CTE Scan on primarysortedusercte_meeting_current cte0_4 (cost=0.00..0.80 rows=40 width=68) (actual time=166.417..166.436 rows=40 loops=1)

62. 0.029 18,314.858 ↑ 1.0 40 1

Hash Join (cost=3.90..6.35 rows=40 width=100) (actual time=18,314.800..18,314.858 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte3.c2)
63. 0.031 9,501.689 ↑ 1.0 40 1

Hash Join (cost=2.60..4.50 rows=40 width=100) (actual time=9,501.650..9,501.689 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte2.c2)
64. 0.029 33.880 ↑ 1.0 40 1

Hash Join (cost=1.30..2.65 rows=40 width=88) (actual time=33.857..33.880 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte1.c2)
65. 0.011 0.011 ↑ 1.0 40 1

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

66. 0.019 33.840 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
67. 33.821 33.821 ↑ 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=32.700..33.821 rows=40 loops=1)

68. 0.094 9,467.778 ↑ 1.0 40 1

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

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

CTE Scan on user_activity_count_cte_email_sent_current cte2 (cost=0.00..0.80 rows=40 width=12) (actual time=8,660.464..9,467.684 rows=40 loops=1)

70. 0.108 8,813.140 ↑ 1.0 40 1

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

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

CTE Scan on user_activity_count_cte_email_received_current cte3 (cost=0.00..0.80 rows=40 width=12) (actual time=8,124.038..8,813.032 rows=40 loops=1)

Planning time : 8.269 ms
Execution time : 18,338.808 ms