explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Y2R

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 3,206.776 ↑ 1.0 40 1

Sort (cost=2,317,056.93..2,317,057.03 rows=40 width=132) (actual time=3,206.775..3,206.776 rows=40 loops=1)

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

CTE filtered_accounts_cte

3. 20.118 250.064 ↑ 1.2 35,856 1

Hash Join (cost=48,118.87..1,465,977.59 rows=43,344 width=40) (actual time=207.627..250.064 rows=35,856 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t1103_accountdim.sid)
4. 22.857 41.083 ↑ 1.0 35,856 1

Bitmap Heap Scan on t1401_accountfact (cost=13,619.72..1,426,093.09 rows=37,377 width=4) (actual time=18.406..41.083 rows=35,856 loops=1)

  • Recheck Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 23,736
  • Heap Blocks: exact=1,097
5. 17.685 17.685 ↓ 1.0 60,144 1

Bitmap Index Scan on idx_933_t1401_accountfact_sid_end_stamp_start_stamp_null (cost=0.00..13,610.37 rows=59,211 width=0) (actual time=17.685..17.685 rows=60,144 loops=1)

  • Index Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
6.          

SubPlan (for Bitmap Heap Scan)

7. 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)
8. 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 = t1401_accountfact.c7136_ownerid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
9. 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))
10. 0.137 0.457 ↓ 2.3 354 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
11. 0.174 0.249 ↑ 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.091..0.249 rows=354 loops=1)

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 0.059 0.059 ↓ 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.059 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
15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t1401_accountfact.c7136_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
16. 0.055 0.084 ↑ 2.3 29 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=4.98..53.05 rows=67 width=4) (actual time=0.043..0.084 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
17. 0.029 0.029 ↑ 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.029 rows=38 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
18. 13.344 188.863 ↑ 1.6 35,941 1

Hash (cost=33,321.68..33,321.68 rows=57,958 width=40) (actual time=188.863..188.863 rows=35,941 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,801kB
19. 175.519 175.519 ↑ 1.6 35,941 1

Seq Scan on t1103_accountdim (cost=0.00..33,321.68 rows=57,958 width=40) (actual time=0.007..175.519 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint))
  • Rows Removed by Filter: 398,050
20.          

CTE account_activity_count_cte_meeting_current

21. 0.004 416.137 ↑ 1.0 40 1

Limit (cost=13,773.54..13,773.64 rows=40 width=76) (actual time=416.131..416.137 rows=40 loops=1)

22. 6.412 416.133 ↑ 108.3 40 1

Sort (cost=13,773.54..13,784.37 rows=4,334 width=76) (actual time=416.131..416.133 rows=40 loops=1)

  • Sort Key: (count(DISTINCT t1662_meetingactivityaccountfact.c7801_activitychecksum)) DESC, cte0_1.c3 NULLS FIRST
  • Sort Method: top-N heapsort Memory: 30kB
23. 38.279 409.721 ↓ 8.3 35,856 1

GroupAggregate (cost=13,051.40..13,636.54 rows=4,334 width=76) (actual time=366.584..409.721 rows=35,856 loops=1)

  • Group Key: cte0_1.c1, cte0_1.c2, cte0_1.c3
24. 30.261 371.442 ↑ 1.1 40,817 1

Sort (cost=13,051.40..13,159.76 rows=43,344 width=72) (actual time=366.570..371.442 rows=40,817 loops=1)

  • Sort Key: cte0_1.c1, cte0_1.c2, cte0_1.c3 NULLS FIRST
  • Sort Method: external sort Disk: 2,000kB
25. 6.821 341.181 ↑ 1.1 40,817 1

Merge Right Join (cost=9,382.00..9,713.14 rows=43,344 width=72) (actual time=330.459..341.181 rows=40,817 loops=1)

  • Merge Cond: (t1662_meetingactivityaccountfact.c7809_accountid = cte0_1.c1)
26. 1.965 57.352 ↓ 12.4 5,776 1

Sort (cost=5,176.87..5,178.03 rows=464 width=8) (actual time=56.810..57.352 rows=5,776 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact.c7809_accountid
  • Sort Method: quicksort Memory: 463kB
27. 33.203 55.387 ↓ 12.4 5,776 1

Gather (cost=1,000.00..5,156.32 rows=464 width=8) (actual time=1.100..55.387 rows=5,776 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
28. 22.184 22.184 ↓ 10.6 2,888 2 / 2

Parallel Seq Scan on t1662_meetingactivityaccountfact (cost=0.00..4,109.92 rows=273 width=8) (actual time=0.006..22.184 rows=2,888 loops=2)

  • Filter: ((NOT deleted) AND c7805_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c7794_activitydate >= '1561532400000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 57,702
29. 14.171 277.008 ↑ 1.1 40,817 1

Sort (cost=4,205.14..4,313.50 rows=43,344 width=68) (actual time=273.643..277.008 rows=40,817 loops=1)

  • Sort Key: cte0_1.c1
  • Sort Method: quicksort Memory: 4,005kB
30. 262.837 262.837 ↑ 1.2 35,856 1

CTE Scan on filtered_accounts_cte cte0_1 (cost=0.00..866.88 rows=43,344 width=68) (actual time=207.629..262.837 rows=35,856 loops=1)

31.          

CTE account_activity_count_cte_email_received_current

32. 2.050 1,228.013 ↑ 1.0 40 1

GroupAggregate (cost=415,472.31..415,481.98 rows=40 width=76) (actual time=1,225.593..1,228.013 rows=40 loops=1)

  • Group Key: cte0_2.c2, cte0_2.c3, cte0_2.c4
33. 3.020 1,225.963 ↓ 11.4 8,458 1

Sort (cost=415,472.31..415,474.16 rows=742 width=72) (actual time=1,225.559..1,225.963 rows=8,458 loops=1)

  • Sort Key: cte0_2.c2, cte0_2.c3, cte0_2.c4
  • Sort Method: quicksort Memory: 1,117kB
34. 2.844 1,222.943 ↓ 11.4 8,458 1

Hash Right Join (cost=1,001.30..415,436.93 rows=742 width=72) (actual time=18.083..1,222.943 rows=8,458 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact.c8021_accountid = cte0_2.c2)
35. 35.981 1,220.082 ↓ 1.1 25,870 1

Gather (cost=1,000.00..415,338.92 rows=23,812 width=8) (actual time=0.778..1,220.082 rows=25,870 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
36. 1,184.101 1,184.101 ↑ 1.2 8,623 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact (cost=0.00..411,957.72 rows=9,922 width=8) (actual time=11.814..1,184.101 rows=8,623 loops=3)

  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 4,353,102
37. 0.009 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
38. 0.008 0.008 ↑ 1.0 40 1

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

39.          

CTE account_activity_count_cte_email_sent_current

40. 2.683 1,505.798 ↑ 1.0 40 1

GroupAggregate (cost=416,083.19..416,095.11 rows=40 width=76) (actual time=1,502.671..1,505.798 rows=40 loops=1)

  • Group Key: cte0_3.c2, cte0_3.c3, cte0_3.c4
41. 4.211 1,503.115 ↓ 11.9 10,941 1

Sort (cost=416,083.19..416,085.49 rows=922 width=72) (actual time=1,502.610..1,503.115 rows=10,941 loops=1)

  • Sort Key: cte0_3.c2, cte0_3.c3, cte0_3.c4
  • Sort Method: quicksort Memory: 1,330kB
42. 5.461 1,498.904 ↓ 11.9 10,941 1

Hash Right Join (cost=1,001.30..416,037.79 rows=922 width=72) (actual time=59.087..1,498.904 rows=10,941 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact_1.c8021_accountid = cte0_3.c2)
43. 96.199 1,493.420 ↓ 1.8 53,217 1

Gather (cost=1,000.00..415,916.32 rows=29,586 width=8) (actual time=1.124..1,493.420 rows=53,217 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 1,397.221 1,397.221 ↓ 1.4 17,739 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..411,957.72 rows=12,328 width=8) (actual time=5.854..1,397.221 rows=17,739 loops=3)

  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c8036_activitydate >= '1561532400000'::bigint) AND (c8036_activitydate < '1593241200000'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 4,343,987
45. 0.011 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
46. 0.012 0.012 ↑ 1.0 40 1

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

47.          

CTE account_activity_count_cte_meeting_upcoming

48. 0.089 468.556 ↑ 1.0 40 1

GroupAggregate (cost=5,114.68..5,115.58 rows=40 width=76) (actual time=468.477..468.556 rows=40 loops=1)

  • Group Key: cte0_4.c2, cte0_4.c3, cte0_4.c4
49. 0.081 468.467 ↓ 2.4 94 1

Sort (cost=5,114.68..5,114.78 rows=40 width=72) (actual time=468.461..468.467 rows=94 loops=1)

  • Sort Key: cte0_4.c2, cte0_4.c3, cte0_4.c4
  • Sort Method: quicksort Memory: 33kB
50. 0.084 468.386 ↓ 2.4 94 1

Hash Right Join (cost=1,001.30..5,113.61 rows=40 width=72) (actual time=424.963..468.386 rows=94 loops=1)

  • Hash Cond: (t1662_meetingactivityaccountfact_1.c7809_accountid = cte0_4.c2)
51. 31.075 52.135 ↓ 15.1 348 1

Gather (cost=1,000.00..5,112.22 rows=23 width=8) (actual time=1.395..52.135 rows=348 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
52. 21.060 21.060 ↓ 12.4 174 2 / 2

Parallel Seq Scan on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.00..4,109.92 rows=14 width=8) (actual time=0.317..21.060 rows=174 loops=2)

  • Filter: ((NOT deleted) AND c7805_isshared AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (c7794_activitydate >= '1593241200000'::bigint) AND (c7794_activitydate < '1595833200000'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 60,416
53. 0.018 416.167 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
54. 416.149 416.149 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0_4 (cost=0.00..0.80 rows=40 width=68) (actual time=416.133..416.149 rows=40 loops=1)

55.          

CTE aggregated_domains_cte

56. 0.047 4.148 ↑ 1.2 40 1

GroupAggregate (cost=602.53..603.53 rows=50 width=36) (actual time=4.104..4.148 rows=40 loops=1)

  • Group Key: t1402_accountdomaindistinct.c7150_accountid
57. 0.045 4.101 ↓ 2.8 138 1

Sort (cost=602.53..602.65 rows=50 width=17) (actual time=4.095..4.101 rows=138 loops=1)

  • Sort Key: t1402_accountdomaindistinct.c7150_accountid
  • Sort Method: quicksort Memory: 33kB
58. 1.346 4.056 ↓ 2.8 138 1

Hash Semi Join (cost=1.30..601.12 rows=50 width=17) (actual time=0.035..4.056 rows=138 loops=1)

  • Hash Cond: (t1402_accountdomaindistinct.c7150_accountid = cte0_5.c2)
59. 2.698 2.698 ↑ 1.0 17,600 1

Seq Scan on t1402_accountdomaindistinct (cost=0.00..552.23 rows=17,916 width=17) (actual time=0.008..2.698 rows=17,600 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint))
  • Rows Removed by Filter: 1,403
60. 0.007 0.012 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
61. 0.005 0.005 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_current cte0_5 (cost=0.00..0.80 rows=40 width=4) (actual time=0.001..0.005 rows=40 loops=1)

62. 0.021 3,206.748 ↑ 1.0 40 1

Hash Join (cost=5.20..8.44 rows=40 width=132) (actual time=3,206.636..3,206.748 rows=40 loops=1)

  • Hash Cond: (cte0.c2 = cte3.c2)
63. 0.022 2,738.128 ↑ 1.0 40 1

Hash Join (cost=3.90..6.59 rows=40 width=132) (actual time=2,738.029..2,738.128 rows=40 loops=1)

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

Hash Join (cost=2.60..4.74 rows=40 width=120) (actual time=1,232.190..1,232.278 rows=40 loops=1)

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

Hash Right Join (cost=1.30..2.89 rows=40 width=108) (actual time=4.137..4.215 rows=40 loops=1)

  • Hash Cond: (cte4.c1 = cte0.c2)
66. 4.170 4.170 ↑ 1.2 40 1

CTE Scan on aggregated_domains_cte cte4 (cost=0.00..1.00 rows=50 width=36) (actual time=4.106..4.170 rows=40 loops=1)

67. 0.008 0.017 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
68. 0.009 0.009 ↑ 1.0 40 1

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

69. 0.010 1,228.043 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
70. 1,228.033 1,228.033 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_email_received_current cte1 (cost=0.00..0.80 rows=40 width=12) (actual time=1,225.595..1,228.033 rows=40 loops=1)

71. 0.011 1,505.828 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
72. 1,505.817 1,505.817 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_email_sent_current cte2 (cost=0.00..0.80 rows=40 width=12) (actual time=1,502.673..1,505.817 rows=40 loops=1)

73. 0.019 468.599 ↑ 1.0 40 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
74. 468.580 468.580 ↑ 1.0 40 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte3 (cost=0.00..0.80 rows=40 width=12) (actual time=468.479..468.580 rows=40 loops=1)

Planning time : 5.042 ms
Execution time : 3,209.696 ms