explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h73T

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 11,113.870 ↑ 1.0 40 1

Sort (cost=3,950,090.70..3,950,090.80 rows=40 width=100) (actual time=11,113.868..11,113.870 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.293 0.643 ↓ 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.423..0.643 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.007 0.023 ↑ 1.0 1 1

Bitmap Heap Scan on t773_userdim (cost=4.97..53.21 rows=1 width=0) (actual time=0.023..0.023 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.052 0.052 ↑ 1.0 388 1

Bitmap Index Scan on t773_userdim_sidendstampunique (cost=0.00..37.14 rows=391 width=0) (actual time=0.052..0.052 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.086 0.275 ↓ 1.5 359 1

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

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

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
15. 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))
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.004..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 128.706 ↑ 1.0 40 1

Limit (cost=2,241,257.35..2,241,257.45 rows=40 width=76) (actual time=128.701..128.706 rows=40 loops=1)

20.          

CTE innerprimarysortedusercte

21. 15.100 127.665 ↑ 1.5 137 1

GroupAggregate (cost=2,240,705.44..2,241,224.74 rows=200 width=12) (actual time=107.023..127.665 rows=137 loops=1)

  • Group Key: t2273_meetingactivityaccountfact.c9004_activityownersfuserid
22. 44.774 112.565 ↓ 1.2 79,692 1

Sort (cost=2,240,705.44..2,240,877.88 rows=68,973 width=8) (actual time=106.396..112.565 rows=79,692 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9004_activityownersfuserid
  • Sort Method: external merge Disk: 1,416kB
23. 67.540 67.791 ↓ 1.2 79,692 1

Index Scan using idx_accountid_sentha on t2273_meetingactivityaccountfact (cost=0.42..2,235,162.17 rows=68,973 width=8) (actual time=0.273..67.791 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
24.          

SubPlan (for Index Scan)

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

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

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

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
30. 0.070 0.096 ↑ 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.035..0.096 rows=359 loops=1)

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

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
32. 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
33. 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
34. 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))
35. 0.033 0.054 ↑ 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.054 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
36. 0.021 0.021 ↑ 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.021..0.021 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
37. 0.150 128.702 ↑ 7.3 40 1

Sort (cost=32.61..33.34 rows=293 width=76) (actual time=128.700..128.702 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
38. 0.079 128.552 ↓ 1.3 387 1

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

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

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

40. 0.027 127.726 ↑ 1.5 137 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
41. 127.699 127.699 ↑ 1.5 137 1

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

42.          

CTE user_activity_count_cte_meeting_upcoming

43. 1.296 15.190 ↑ 1.0 40 1

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

  • Group Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
44. 7.013 13.894 ↓ 2.8 5,588 1

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

  • Sort Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
  • Sort Method: quicksort Memory: 629kB
45. 2.710 6.881 ↓ 2.8 5,588 1

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

  • Hash Cond: (t2273_meetingactivityaccountfact_1.c9004_activityownersfuserid = cte0_2.c2)
46. 3.271 4.155 ↓ 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.070..4.155 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
47. 0.884 0.884 ↓ 1.2 12,108 1

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

  • Index Cond: ((c8986_activitydate >= '1593241200000'::bigint) AND (c8986_activitydate < '1595833200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
48. 0.009 0.016 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
49. 0.007 0.007 ↑ 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.007 rows=40 loops=1)

50.          

CTE user_activity_count_cte_email_sent_current

51. 310.580 6,593.351 ↑ 1.0 40 1

GroupAggregate (cost=909,773.71..918,858.00 rows=40 width=76) (actual time=6,040.158..6,593.351 rows=40 loops=1)

  • Group Key: cte0_3.c1, cte0_3.c2, cte0_3.c3
52. 1,762.620 6,282.771 ↓ 1.4 1,025,810 1

Sort (cost=909,773.71..911,590.49 rows=726,711 width=72) (actual time=6,011.192..6,282.771 rows=1,025,810 loops=1)

  • Sort Key: cte0_3.c1, cte0_3.c2, cte0_3.c3
  • Sort Method: external merge Disk: 53,928kB
53. 487.005 4,520.151 ↓ 1.4 1,025,810 1

Hash Right Join (cost=1.30..779,405.68 rows=726,711 width=72) (actual time=0.040..4,520.151 rows=1,025,810 loops=1)

  • Hash Cond: (t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid = cte0_3.c2)
54. 4,033.129 4,033.129 ↑ 1.0 6,135,534 1

Seq Scan on t2608_emailactivityaccountfact_copy_sentha (cost=0.00..748,499.29 rows=6,303,462 width=8) (actual time=0.013..4,033.129 rows=6,135,534 loops=1)

  • Filter: ((NOT deleted) AND c9660_isinternalinitiated AND c9657_isshared AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 12,729,055
55. 0.010 0.017 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
56. 0.007 0.007 ↑ 1.0 40 1

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

57.          

CTE user_activity_count_cte_email_received_current

58. 275.284 4,505.005 ↑ 1.0 40 1

GroupAggregate (cost=772,319.60..779,028.69 rows=40 width=76) (actual time=4,033.236..4,505.005 rows=40 loops=1)

  • Group Key: cte0_4.c1, cte0_4.c2, cte0_4.c3
59. 1,658.964 4,229.721 ↓ 1.7 927,472 1

Sort (cost=772,319.60..773,661.34 rows=536,695 width=72) (actual time=4,007.032..4,229.721 rows=927,472 loops=1)

  • Sort Key: cte0_4.c1, cte0_4.c2, cte0_4.c3
  • Sort Method: external merge Disk: 48,584kB
60. 420.351 2,570.757 ↓ 1.7 927,472 1

Hash Right Join (cost=163,060.64..699,228.03 rows=536,695 width=72) (actual time=390.814..2,570.757 rows=927,472 loops=1)

  • Hash Cond: (t2608_emailactivityaccountfact_copy_sentha_1.c9662_activityownersfuserid = cte0_4.c2)
61. 1,767.885 2,021.680 ↓ 1.0 4,690,428 1

Bitmap Heap Scan on t2608_emailactivityaccountfact_copy_sentha t2608_emailactivityaccountfact_copy_sentha_1 (cost=163,059.34..676,402.52 rows=4,655,269 width=8) (actual time=261.298..2,021.680 rows=4,690,428 loops=1)

  • Recheck Cond: ((end_stamp = '32503680000000'::bigint) AND c9657_isshared AND (NOT deleted))
  • Rows Removed by Index Recheck: 701,800
  • Filter: ((NOT c9660_isinternalinitiated) AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint))
  • Rows Removed by Filter: 1,651,906
  • Heap Blocks: exact=40,821 lossy=132,333
62. 253.795 253.795 ↓ 1.0 5,540,791 1

Bitmap Index Scan on idx_internal_p_sentha (cost=0.00..161,895.52 rows=5,427,096 width=0) (actual time=253.795..253.795 rows=5,540,791 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = false) AND (end_stamp = '32503680000000'::bigint))
63. 0.011 128.726 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
64. 128.715 128.715 ↑ 1.0 40 1

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

65. 0.017 11,113.852 ↑ 1.0 40 1

Hash Join (cost=3.90..6.35 rows=40 width=100) (actual time=11,113.819..11,113.852 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte3.c2)
66. 0.019 6,608.724 ↑ 1.0 40 1

Hash Join (cost=2.60..4.50 rows=40 width=100) (actual time=6,608.701..6,608.724 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte2.c2)
67. 0.017 15.240 ↑ 1.0 40 1

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

  • Hash Cond: (cte0.c2 = cte1.c2)
68. 0.006 0.006 ↑ 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.006 rows=40 loops=1)

69. 0.008 15.217 ↑ 1.0 40 1

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

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

71. 0.039 6,593.465 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
72. 6,593.426 6,593.426 ↑ 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=6,040.162..6,593.426 rows=40 loops=1)

73. 0.038 4,505.111 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
74. 4,505.073 4,505.073 ↑ 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=4,033.239..4,505.073 rows=40 loops=1)

Planning time : 3.951 ms
Execution time : 11,130.960 ms