explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ipew

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 4,306.985 ↑ 1.1 38 1

Sort (cost=4,527,592.93..4,527,593.03 rows=40 width=100) (actual time=4,306.981..4,306.985 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.287 0.632 ↓ 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.424..0.632 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.049 0.049 ↑ 1.0 388 1

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

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.037 0.037 ↓ 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.037 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.736 ↑ 1.0 40 1

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

20.          

CTE innerprimarysortedusercte

21. 14.175 79.657 ↑ 1.5 137 1

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

  • Group Key: t2273_meetingactivityaccountfact.c9004_activityownersfuserid
22. 65.234 65.482 ↓ 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.549..65.482 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.067 0.190 ↓ 1.5 359 1

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

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

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

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

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.032..0.032 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.058 ↑ 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.030..0.058 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.025 0.025 ↑ 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.025..0.025 rows=36 loops=1)

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

Sort (cost=32.61..33.34 rows=293 width=76) (actual time=80.730..80.732 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.082 80.583 ↓ 1.3 387 1

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

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

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

39. 0.038 79.753 ↑ 1.5 137 1

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

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

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

41.          

CTE user_activity_count_cte_meeting_upcoming

42. 1.298 95.370 ↑ 1.0 40 1

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

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

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

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

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

  • Hash Cond: (t2273_meetingactivityaccountfact_1.c9004_activityownersfuserid = cte0_2.c2)
45. 2.644 3.584 ↓ 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.121..3.584 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.940 0.940 ↓ 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.940..0.940 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.755 ↑ 1.0 40 1

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

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

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

49.          

CTE user_activity_count_cte_email_sent_current

50. 233.773 2,145.634 ↑ 5.2 38 1

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

  • Group Key: t2608_emailactivityaccountfact.c9662_activityownersfuserid
51. 308.952 1,911.861 ↓ 1.0 965,113 1

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

  • Sort Key: t2608_emailactivityaccountfact.c9662_activityownersfuserid
  • Sort Method: external merge Disk: 17,080kB
52. 166.473 1,602.909 ↓ 1.0 965,113 1

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

  • Workers Planned: 2
  • Workers Launched: 2
53. 1,436.436 1,436.436 ↑ 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.459..1,436.436 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. 214.594 2,065.669 ↑ 5.2 38 1

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

  • Group Key: t2608_emailactivityaccountfact_1.c9662_activityownersfuserid
56. 275.091 1,851.075 ↓ 1.3 881,096 1

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

  • Sort Key: t2608_emailactivityaccountfact_1.c9662_activityownersfuserid
  • Sort Method: external merge Disk: 15,608kB
57. 139.830 1,575.984 ↓ 1.3 881,096 1

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

  • Workers Planned: 2
  • Workers Launched: 2
58. 1,436.154 1,436.154 ↓ 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=0.563..1,436.154 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.032 4,306.942 ↑ 1.1 38 1

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

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

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

  • Hash Cond: (cte3.c2 = cte0.c2)
61. 2,065.707 2,065.707 ↑ 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,774.148..2,065.707 rows=38 loops=1)

62. 0.027 2,145.755 ↑ 1.1 38 1

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

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

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

  • Hash Cond: (cte2.c2 = cte0.c2)
64. 2,145.670 2,145.670 ↑ 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,823.437..2,145.670 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.012 95.400 ↑ 1.0 40 1

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

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

Planning time : 4.061 ms
Execution time : 4,313.301 ms