explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Blr4

Settings
# exclusive inclusive rows x rows loops node
1. 0.061 3,667.928 ↑ 1.0 50 1

Sort (cost=1,752,950.99..1,752,951.12 rows=50 width=133) (actual time=3,667.924..3,667.928 rows=50 loops=1)

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

CTE account_activity_cte

3. 0.005 3,619.060 ↑ 1.0 50 1

Limit (cost=1,751,377.00..1,751,377.12 rows=50 width=100) (actual time=3,619.054..3,619.060 rows=50 loops=1)

4.          

CTE account_activity_count_cte_email_received_current

5. 9.577 2,006.917 ↑ 3.0 432 1

GroupAggregate (cost=405,802.38..406,091.43 rows=1,283 width=12) (actual time=1,994.619..2,006.917 rows=432 loops=1)

  • Group Key: t1816_emailactivityaccountfact.c8021_accountid
6. 12.520 1,997.340 ↓ 1.1 40,602 1

Sort (cost=405,802.38..405,894.45 rows=36,830 width=8) (actual time=1,994.570..1,997.340 rows=40,602 loops=1)

  • Sort Key: t1816_emailactivityaccountfact.c8021_accountid
  • Sort Method: quicksort Memory: 3,269kB
7. 616.602 1,984.820 ↓ 1.1 40,602 1

Gather (cost=1,000.00..403,009.08 rows=36,830 width=8) (actual time=44.651..1,984.820 rows=40,602 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 1,368.218 1,368.218 ↑ 1.1 13,534 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact (cost=0.00..398,326.08 rows=15,346 width=8) (actual time=15.405..1,368.218 rows=13,534 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 = 97))
  • Rows Removed by Filter: 4,348,192
9.          

CTE account_activity_count_cte_email_sent_current

10. 10.586 1,260.164 ↑ 2.9 436 1

GroupAggregate (cost=407,444.32..407,800.35 rows=1,283 width=12) (actual time=1,246.641..1,260.164 rows=436 loops=1)

  • Group Key: t1816_emailactivityaccountfact_1.c8021_accountid
11. 13.611 1,249.578 ↓ 1.1 50,833 1

Sort (cost=407,444.32..407,558.72 rows=45,760 width=8) (actual time=1,246.561..1,249.578 rows=50,833 loops=1)

  • Sort Key: t1816_emailactivityaccountfact_1.c8021_accountid
  • Sort Method: quicksort Memory: 3,749kB
12. 44.571 1,235.967 ↓ 1.1 50,833 1

Gather (cost=1,000.00..403,902.08 rows=45,760 width=8) (actual time=23.773..1,235.967 rows=50,833 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 1,191.396 1,191.396 ↑ 1.1 16,944 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..398,326.08 rows=19,067 width=8) (actual time=34.486..1,191.396 rows=16,944 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 = 97))
  • Rows Removed by Filter: 4,344,781
14.          

CTE account_activity_count_cte_meeting_current

15. 1.093 41.827 ↑ 9.6 156 1

GroupAggregate (cost=5,122.60..5,161.74 rows=1,502 width=12) (actual time=40.477..41.827 rows=156 loops=1)

  • Group Key: t1662_meetingactivityaccountfact.c7809_accountid
16. 1.100 40.734 ↓ 1.1 3,378 1

Sort (cost=5,122.60..5,130.64 rows=3,215 width=8) (actual time=40.444..40.734 rows=3,378 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact.c7809_accountid
  • Sort Method: quicksort Memory: 255kB
17. 39.634 39.634 ↓ 1.1 3,378 1

Seq Scan on t1662_meetingactivityaccountfact (cost=0.00..4,935.32 rows=3,215 width=8) (actual time=2.453..39.634 rows=3,378 loops=1)

  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561532400000'::bigint) AND (c7794_activitydate < '1593241200000'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c7807_activityownersfuserid = 97))
  • Rows Removed by Filter: 117,802
18.          

CTE account_activity_count_cte_meeting_upcoming

19. 0.106 36.722 ↑ 5.8 26 1

GroupAggregate (cost=4,941.13..4,943.85 rows=152 width=12) (actual time=36.625..36.722 rows=26 loops=1)

  • Group Key: t1662_meetingactivityaccountfact_1.c7809_accountid
20. 0.104 36.616 ↓ 1.4 223 1

Sort (cost=4,941.13..4,941.53 rows=159 width=8) (actual time=36.597..36.616 rows=223 loops=1)

  • Sort Key: t1662_meetingactivityaccountfact_1.c7809_accountid
  • Sort Method: quicksort Memory: 35kB
21. 36.512 36.512 ↓ 1.4 223 1

Seq Scan on t1662_meetingactivityaccountfact t1662_meetingactivityaccountfact_1 (cost=0.00..4,935.32 rows=159 width=8) (actual time=14.898..36.512 rows=223 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 = 97))
  • Rows Removed by Filter: 120,957
22.          

CTE filtered_accounts_cte

23. 26.328 232.748 ↑ 1.6 35,941 1

Hash Join (cost=16,624.37..48,999.51 rows=57,741 width=40) (actual time=27.566..232.748 rows=35,941 loops=1)

  • Hash Cond: (t1103_accountdim.sid = t1401_accountfact.sid)
24. 179.268 179.268 ↑ 1.6 35,942 1

Seq Scan on t1103_accountdim (cost=0.00..32,223.06 rows=57,929 width=40) (actual time=0.006..179.268 rows=35,942 loops=1)

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 398,049
25. 7.935 27.152 ↑ 1.4 35,941 1

Hash (cost=16,001.66..16,001.66 rows=49,817 width=4) (actual time=27.152..27.152 rows=35,941 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,776kB
26. 14.216 19.217 ↑ 1.4 35,941 1

Bitmap Heap Scan on t1401_accountfact (cost=4,312.81..16,001.66 rows=49,817 width=4) (actual time=5.160..19.217 rows=35,941 loops=1)

  • Recheck Cond: (end_stamp = '32503680000000'::bigint)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 23,652
  • Heap Blocks: exact=1,097
27. 5.001 5.001 ↓ 1.0 60,145 1

Bitmap Index Scan on t1401_accountfact_848_ptimestamp (cost=0.00..4,300.35 rows=59,188 width=0) (actual time=5.001..5.001 rows=60,145 loops=1)

28. 0.243 3,619.055 ↑ 356,879.8 50 1

Sort (cost=878,380.12..922,990.10 rows=17,843,990 width=100) (actual time=3,619.052..3,619.055 rows=50 loops=1)

  • Sort Key: (CASE WHEN (cte2_1.c1 IS NULL) THEN '0'::bigint ELSE cte2_1.c1 END) DESC, cte4.c3 NULLS FIRST
  • Sort Method: top-N heapsort Memory: 37kB
29. 3.703 3,618.812 ↑ 36,048.5 495 1

Merge Right Join (cost=17,512.53..285,615.60 rows=17,843,990 width=100) (actual time=3,612.531..3,618.812 rows=495 loops=1)

  • Merge Cond: (cte4.c1 = (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 WHEN (cte2_1.c2 IS NOT NULL) THEN cte2_1.c2 WHEN (cte3.c2 IS NOT NULL) THEN cte3.c2 ELSE NULL::integer END))
30. 18.127 267.976 ↑ 1.6 35,903 1

Sort (cost=8,090.86..8,235.21 rows=57,741 width=68) (actual time=265.487..267.976 rows=35,903 loops=1)

  • Sort Key: cte4.c1
  • Sort Method: quicksort Memory: 4,013kB
31. 249.849 249.849 ↑ 1.6 35,941 1

CTE Scan on filtered_accounts_cte cte4 (cost=0.00..1,154.82 rows=57,741 width=68) (actual time=27.568..249.849 rows=35,941 loops=1)

32. 0.065 3,347.133 ↑ 124.9 495 1

Materialize (cost=9,421.68..9,730.71 rows=61,807 width=48) (actual time=3,347.034..3,347.133 rows=495 loops=1)

33. 0.212 3,347.068 ↑ 124.9 495 1

Sort (cost=9,421.68..9,576.19 rows=61,807 width=48) (actual time=3,347.030..3,347.068 rows=495 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 WHEN (cte2_1.c2 IS NOT NULL) THEN cte2_1.c2 WHEN (cte3.c2 IS NOT NULL) THEN cte3.c2 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 72kB
34. 0.178 3,346.856 ↑ 124.9 495 1

Hash Full Join (cost=963.11..2,599.24 rows=61,807 width=48) (actual time=3,346.469..3,346.856 rows=495 loops=1)

  • Hash Cond: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 WHEN (cte2_1.c2 IS NOT NULL) THEN cte2_1.c2 ELSE NULL::integer END = cte3.c2)
35. 0.182 3,309.933 ↑ 124.9 495 1

Merge Full Join (cost=958.17..1,892.79 rows=61,807 width=36) (actual time=3,309.700..3,309.933 rows=495 loops=1)

  • Merge Cond: (cte2_1.c2 = (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 ELSE NULL::integer END))
36. 0.048 41.934 ↑ 9.6 156 1

Sort (cost=109.29..113.05 rows=1,502 width=12) (actual time=41.914..41.934 rows=156 loops=1)

  • Sort Key: cte2_1.c2
  • Sort Method: quicksort Memory: 32kB
37. 41.886 41.886 ↑ 9.6 156 1

CTE Scan on account_activity_count_cte_meeting_current cte2_1 (cost=0.00..30.04 rows=1,502 width=12) (actual time=40.487..41.886 rows=156 loops=1)

38. 0.118 3,267.817 ↑ 16.7 492 1

Sort (cost=848.88..869.46 rows=8,230 width=24) (actual time=3,267.781..3,267.817 rows=492 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 WHEN (cte1_1.c2 IS NOT NULL) THEN cte1_1.c2 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 59kB
39. 0.150 3,267.699 ↑ 16.7 492 1

Merge Full Join (cost=183.79..313.66 rows=8,230 width=24) (actual time=3,267.507..3,267.699 rows=492 loops=1)

  • Merge Cond: ((CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END) = cte1_1.c2)
40. 0.108 2,007.190 ↑ 3.0 432 1

Sort (cost=91.90..95.10 rows=1,283 width=12) (actual time=2,007.165..2,007.190 rows=432 loops=1)

  • Sort Key: (CASE WHEN (cte0_1.c2 IS NOT NULL) THEN cte0_1.c2 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 45kB
41. 2,007.082 2,007.082 ↑ 3.0 432 1

CTE Scan on account_activity_count_cte_email_received_current cte0_1 (cost=0.00..25.66 rows=1,283 width=12) (actual time=1,994.622..2,007.082 rows=432 loops=1)

42. 0.082 1,260.359 ↑ 2.9 436 1

Sort (cost=91.90..95.10 rows=1,283 width=12) (actual time=1,260.336..1,260.359 rows=436 loops=1)

  • Sort Key: cte1_1.c2
  • Sort Method: quicksort Memory: 45kB
43. 1,260.277 1,260.277 ↑ 2.9 436 1

CTE Scan on account_activity_count_cte_email_sent_current cte1_1 (cost=0.00..25.66 rows=1,283 width=12) (actual time=1,246.643..1,260.277 rows=436 loops=1)

44. 0.012 36.745 ↑ 5.8 26 1

Hash (cost=3.04..3.04 rows=152 width=12) (actual time=36.744..36.745 rows=26 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
45. 36.733 36.733 ↑ 5.8 26 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte3 (cost=0.00..3.04 rows=152 width=12) (actual time=36.627..36.733 rows=26 loops=1)

46.          

CTE aggregated_domains_cte

47. 0.060 4.138 ↑ 1.2 50 1

GroupAggregate (cost=556.22..557.46 rows=62 width=36) (actual time=4.083..4.138 rows=50 loops=1)

  • Group Key: t1402_accountdomaindistinct.c7150_accountid
48. 0.057 4.078 ↓ 2.8 173 1

Sort (cost=556.22..556.37 rows=62 width=17) (actual time=4.069..4.078 rows=173 loops=1)

  • Sort Key: t1402_accountdomaindistinct.c7150_accountid
  • Sort Method: quicksort Memory: 35kB
49. 1.353 4.021 ↓ 2.8 173 1

Hash Semi Join (cost=1.62..554.37 rows=62 width=17) (actual time=0.047..4.021 rows=173 loops=1)

  • Hash Cond: (t1402_accountdomaindistinct.c7150_accountid = cte0_2.c5)
50. 2.652 2.652 ↑ 1.0 17,601 1

Seq Scan on t1402_accountdomaindistinct (cost=0.00..505.02 rows=17,916 width=17) (actual time=0.010..2.652 rows=17,601 loops=1)

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 1,402
51. 0.008 0.016 ↑ 1.0 50 1

Hash (cost=1.00..1.00 rows=50 width=4) (actual time=0.016..0.016 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
52. 0.008 0.008 ↑ 1.0 50 1

CTE Scan on account_activity_cte cte0_2 (cost=0.00..1.00 rows=50 width=4) (actual time=0.001..0.008 rows=50 loops=1)

53.          

CTE accounts_with_open_opportunities

54. 0.009 44.516 ↑ 2.6 25 1

Group (cost=1,008.44..1,008.76 rows=64 width=4) (actual time=44.506..44.516 rows=25 loops=1)

  • Group Key: t1323_oppfact.c6938_opp_accountid
55. 0.025 44.507 ↑ 1.9 33 1

Sort (cost=1,008.44..1,008.60 rows=64 width=4) (actual time=44.504..44.507 rows=33 loops=1)

  • Sort Key: t1323_oppfact.c6938_opp_accountid
  • Sort Method: quicksort Memory: 26kB
56. 0.107 44.482 ↑ 1.9 33 1

Nested Loop (cost=1.82..1,006.52 rows=64 width=4) (actual time=1.140..44.482 rows=33 loops=1)

57. 0.179 10.427 ↓ 2.1 246 1

Nested Loop (cost=1.41..555.97 rows=115 width=8) (actual time=0.040..10.427 rows=246 loops=1)

58. 0.041 0.048 ↑ 1.0 50 1

HashAggregate (cost=1.12..1.62 rows=50 width=4) (actual time=0.022..0.048 rows=50 loops=1)

  • Group Key: cte0_3.c5
59. 0.007 0.007 ↑ 1.0 50 1

CTE Scan on account_activity_cte cte0_3 (cost=0.00..1.00 rows=50 width=4) (actual time=0.001..0.007 rows=50 loops=1)

60. 10.200 10.200 ↓ 2.5 5 50

Index Scan using t1323_oppfact_249_ptimestamp on t1323_oppfact (cost=0.28..11.07 rows=2 width=8) (actual time=0.107..0.204 rows=5 loops=50)

  • Index Cond: (c6938_opp_accountid = cte0_3.c5)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
61. 33.948 33.948 ↓ 0.0 0 246

Index Scan using idx_827_t1321_oppdim_sid_end_stamp_start_stamp_null on t1321_oppdim (cost=0.42..3.92 rows=1 width=4) (actual time=0.138..0.138 rows=0 loops=246)

  • Index Cond: ((sid = t1323_oppfact.sid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (NOT c6749_isclosed))
  • Rows Removed by Filter: 1
62. 0.035 3,667.867 ↑ 1.0 50 1

Hash Right Join (cost=4.22..6.24 rows=50 width=133) (actual time=3,667.820..3,667.867 rows=50 loops=1)

  • Hash Cond: (cte2.c1 = cte0.c5)
63. 44.530 44.530 ↑ 2.6 25 1

CTE Scan on accounts_with_open_opportunities cte2 (cost=0.00..1.28 rows=64 width=4) (actual time=44.507..44.530 rows=25 loops=1)

64. 0.017 3,623.302 ↑ 1.0 50 1

Hash (cost=3.60..3.60 rows=50 width=132) (actual time=3,623.302..3,623.302 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
65. 0.028 3,623.285 ↑ 1.0 50 1

Hash Right Join (cost=1.62..3.60 rows=50 width=132) (actual time=3,623.193..3,623.285 rows=50 loops=1)

  • Hash Cond: (cte1.c1 = cte0.c5)
66. 4.157 4.157 ↑ 1.2 50 1

CTE Scan on aggregated_domains_cte cte1 (cost=0.00..1.24 rows=62 width=36) (actual time=4.085..4.157 rows=50 loops=1)

67. 0.024 3,619.100 ↑ 1.0 50 1

Hash (cost=1.00..1.00 rows=50 width=100) (actual time=3,619.100..3,619.100 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
68. 3,619.076 3,619.076 ↑ 1.0 50 1

CTE Scan on account_activity_cte cte0 (cost=0.00..1.00 rows=50 width=100) (actual time=3,619.056..3,619.076 rows=50 loops=1)

Planning time : 4.337 ms
Execution time : 3,671.418 ms