explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xLIR

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 3,873.823 ↑ 1.0 40 1

Sort (cost=3,684,190.91..3,684,191.01 rows=40 width=100) (actual time=3,873.820..3,873.823 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.549 1.177 ↓ 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.756..1.177 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.010 0.040 ↑ 1.0 1 1

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

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

Bitmap Index Scan on t773_userdim_sidendstampunique (cost=0.00..37.07 rows=388 width=0) (actual time=0.077..0.077 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.164 0.511 ↓ 2.3 354 1

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

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

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

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
15. 0.011 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
16. 0.070 0.070 ↓ 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.070 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 131.477 ↑ 1.0 40 1

Limit (cost=2,868,427.36..2,868,427.46 rows=40 width=76) (actual time=131.441..131.477 rows=40 loops=1)

19.          

CTE innerprimarysortedusercte

20. 1.744 129.497 ↑ 4.5 37 1

GroupAggregate (cost=0.42..2,868,403.50 rows=166 width=12) (actual time=52.910..129.497 rows=37 loops=1)

  • Group Key: t1662_meetingactivityaccountfact.c7807_activityownersfuserid
21. 127.221 127.753 ↓ 16.6 5,776 1

Index Scan using t1662_meetingactivityaccountfact_920_ptimestamp on t1662_meetingactivityaccountfact (cost=0.42..2,868,400.10 rows=348 width=8) (actual time=49.555..127.753 rows=5,776 loops=1)

  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561532400000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 113,041
22.          

SubPlan (for Index Scan)

23. 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)
24. 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))
25. 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))
26. 0.149 0.532 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
27. 0.213 0.299 ↑ 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.108..0.299 rows=354 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.072 0.072 ↓ 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.072 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
31. 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))
32. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t773_userdim t773_userdim_3 (cost=4.98..53.05 rows=67 width=4) (never executed)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
33. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (never executed)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
34. 0.391 131.472 ↑ 7.2 40 1

Sort (cost=23.86..24.58 rows=290 width=76) (actual time=131.440..131.472 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: 30kB
35. 0.135 131.081 ↓ 1.3 384 1

Hash Left Join (cost=5.40..14.69 rows=290 width=76) (actual time=130.367..131.081 rows=384 loops=1)

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

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

37. 0.042 129.598 ↑ 4.5 37 1

Hash (cost=3.32..3.32 rows=166 width=12) (actual time=129.598..129.598 rows=37 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
38. 129.556 129.556 ↑ 4.5 37 1

CTE Scan on innerprimarysortedusercte cte0_1 (cost=0.00..3.32 rows=166 width=12) (actual time=52.922..129.556 rows=37 loops=1)

39.          

CTE user_activity_count_cte_email_received_current

40. 11.181 1,628.296 ↑ 1.0 40 1

GroupAggregate (cost=401,987.83..402,023.51 rows=40 width=76) (actual time=1,616.044..1,628.296 rows=40 loops=1)

  • Group Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
41. 36.348 1,617.115 ↓ 9.1 25,820 1

Sort (cost=401,987.83..401,994.89 rows=2,822 width=72) (actual time=1,615.192..1,617.115 rows=25,820 loops=1)

  • Sort Key: cte0_2.c1, cte0_2.c2, cte0_2.c3
  • Sort Method: quicksort Memory: 2,786kB
42. 7.494 1,580.767 ↓ 9.1 25,820 1

Hash Right Join (cost=1,001.30..401,826.10 rows=2,822 width=72) (actual time=1.260..1,580.767 rows=25,820 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact.c8045_activityownersfuserid = cte0_2.c2)
43. 35.630 1,573.249 ↓ 1.1 25,870 1

Gather (cost=1,000.00..401,707.28 rows=23,812 width=8) (actual time=1.222..1,573.249 rows=25,870 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 1,537.619 1,537.619 ↑ 1.2 8,623 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact (cost=0.00..398,326.08 rows=9,922 width=8) (actual time=22.435..1,537.619 rows=8,623 loops=3)

  • 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) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 4,353,102
45. 0.012 0.024 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
46. 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.002..0.012 rows=40 loops=1)

47.          

CTE user_activity_count_cte_email_sent_current

48. 15.196 2,073.832 ↑ 1.0 40 1

GroupAggregate (cost=402,638.42..402,682.64 rows=40 width=76) (actual time=2,053.878..2,073.832 rows=40 loops=1)

  • Group Key: cte0_3.c1, cte0_3.c2, cte0_3.c3
49. 441.663 2,058.636 ↓ 14.7 51,685 1

Sort (cost=402,638.42..402,647.18 rows=3,506 width=72) (actual time=2,052.949..2,058.636 rows=51,685 loops=1)

  • Sort Key: cte0_3.c1, cte0_3.c2, cte0_3.c3
  • Sort Method: external merge Disk: 2,696kB
50. 15.120 1,616.973 ↓ 14.7 51,685 1

Hash Right Join (cost=1,001.30..402,431.99 rows=3,506 width=72) (actual time=1.458..1,616.973 rows=51,685 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact_1.c8045_activityownersfuserid = cte0_3.c2)
51. 0.000 1,601.832 ↓ 1.8 53,217 1

Gather (cost=1,000.00..402,284.68 rows=29,586 width=8) (actual time=1.425..1,601.832 rows=53,217 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
52. 1,667.356 1,667.356 ↓ 1.4 17,739 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..398,326.08 rows=12,328 width=8) (actual time=0.069..1,667.356 rows=17,739 loops=3)

  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 4,343,987
53. 0.012 0.021 ↑ 1.0 40 1

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

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

55.          

CTE user_activity_count_cte_meeting_upcoming

56. 0.197 171.398 ↑ 1.0 40 1

GroupAggregate (cost=4,951.04..4,951.94 rows=40 width=76) (actual time=171.207..171.398 rows=40 loops=1)

  • Group Key: cte0_4.c1, cte0_4.c2, cte0_4.c3
57. 0.600 171.201 ↓ 9.0 360 1

Sort (cost=4,951.04..4,951.14 rows=40 width=72) (actual time=171.177..171.201 rows=360 loops=1)

  • Sort Key: cte0_4.c1, cte0_4.c2, cte0_4.c3
  • Sort Method: quicksort Memory: 53kB
58. 1.387 170.601 ↓ 9.0 360 1

Nested Loop Left Join (cost=0.00..4,949.98 rows=40 width=72) (actual time=141.228..170.601 rows=360 loops=1)

  • Join Filter: (t1662_meetingactivityaccountfact_1.c7807_activityownersfuserid = cte0_4.c2)
  • Rows Removed by Join Filter: 13,572
59. 131.494 131.494 ↑ 1.0 40 1

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

60. 0.839 37.720 ↓ 15.1 348 40

Materialize (cost=0.00..4,935.43 rows=23 width=8) (actual time=0.019..0.943 rows=348 loops=40)

61. 36.881 36.881 ↓ 15.1 348 1

Seq Scan on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.00..4,935.32 rows=23 width=8) (actual time=0.761..36.881 rows=348 loops=1)

  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1593241200000'::bigint) AND (c7794_activitydate < '1595833200000'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 120,832
62. 0.029 3,873.792 ↑ 1.0 40 1

Hash Join (cost=3.90..6.35 rows=40 width=100) (actual time=3,873.733..3,873.792 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte3.c2)
63. 0.028 3,702.324 ↑ 1.0 40 1

Hash Join (cost=2.60..4.50 rows=40 width=100) (actual time=3,702.284..3,702.324 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte2.c2)
64. 0.030 1,628.397 ↑ 1.0 40 1

Hash Join (cost=1.30..2.65 rows=40 width=88) (actual time=1,628.373..1,628.397 rows=40 loops=1)

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

66. 0.023 1,628.357 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
67. 1,628.334 1,628.334 ↑ 1.0 40 1

CTE Scan on user_activity_count_cte_email_received_current cte1 (cost=0.00..0.80 rows=40 width=12) (actual time=1,616.047..1,628.334 rows=40 loops=1)

68. 0.024 2,073.899 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
69. 2,073.875 2,073.875 ↑ 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=2,053.881..2,073.875 rows=40 loops=1)

70. 0.017 171.439 ↑ 1.0 40 1

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

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

CTE Scan on user_activity_count_cte_meeting_upcoming cte3 (cost=0.00..0.80 rows=40 width=12) (actual time=171.209..171.422 rows=40 loops=1)

Planning time : 6.147 ms
Execution time : 3,876.061 ms