explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xM2k

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 4,355.829 ↑ 1.5 34 1

Sort (cost=818,926.19..818,926.31 rows=50 width=133) (actual time=4,355.826..4,355.829 rows=34 loops=1)

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

CTE filtered_accounts_cte

3. 0.117 111.970 ↑ 4.2 34 1

Nested Loop (cost=0.84..1,412.23 rows=143 width=40) (actual time=2.290..111.970 rows=34 loops=1)

4. 0.129 0.129 ↑ 3.6 34 1

Index Scan using t1401_accountfact_848_ptimestamp on t1401_accountfact (cost=0.41..385.81 rows=123 width=4) (actual time=0.011..0.129 rows=34 loops=1)

  • Index Cond: (c7136_ownerid = 62)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 15
5. 111.724 111.724 ↑ 1.0 1 34

Index Scan using idx_596_t1103_accountdim_sid_end_stamp_start_stamp_null on t1103_accountdim (cost=0.42..8.34 rows=1 width=40) (actual time=3.286..3.286 rows=1 loops=34)

  • Index Cond: ((sid = t1401_accountfact.sid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: (NOT deleted)
6.          

CTE account_activity_count_cte_meeting_current

7. 0.005 164.696 ↑ 1.5 34 1

Limit (cost=4,955.16..4,955.29 rows=50 width=76) (actual time=164.691..164.696 rows=34 loops=1)

8. 0.034 164.691 ↑ 4.2 34 1

Sort (cost=4,955.16..4,955.52 rows=143 width=76) (actual time=164.689..164.691 rows=34 loops=1)

  • Sort Key: (count(DISTINCT t1662_meetingactivityaccountfact.c7801_activitychecksum)) DESC, cte0_1.c3 NULLS FIRST
  • Sort Method: quicksort Memory: 28kB
9. 0.463 164.657 ↑ 4.2 34 1

GroupAggregate (cost=4,947.20..4,950.41 rows=143 width=76) (actual time=164.161..164.657 rows=34 loops=1)

  • Group Key: cte0_1.c1, cte0_1.c2, cte0_1.c3
10. 0.528 164.194 ↓ 7.0 997 1

Sort (cost=4,947.20..4,947.55 rows=143 width=72) (actual time=164.128..164.194 rows=997 loops=1)

  • Sort Key: cte0_1.c1, cte0_1.c2, cte0_1.c3 NULLS FIRST
  • Sort Method: quicksort Memory: 103kB
11. 0.841 163.666 ↓ 7.0 997 1

Hash Right Join (cost=4.65..4,942.08 rows=143 width=72) (actual time=114.735..163.666 rows=997 loops=1)

  • Hash Cond: (t1662_meetingactivityaccountfact.c7809_accountid = cte0_1.c1)
12. 50.721 50.721 ↓ 12.4 5,776 1

Seq Scan on t1662_meetingactivityaccountfact (cost=0.00..4,935.32 rows=464 width=8) (actual time=0.020..50.721 rows=5,776 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 = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 115,404
13. 0.059 112.104 ↑ 4.2 34 1

Hash (cost=2.86..2.86 rows=143 width=68) (actual time=112.104..112.104 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
14. 112.045 112.045 ↑ 4.2 34 1

CTE Scan on filtered_accounts_cte cte0_1 (cost=0.00..2.86 rows=143 width=68) (actual time=2.296..112.045 rows=34 loops=1)

15.          

CTE account_activity_count_cte_meeting_upcoming

16. 0.079 39.014 ↑ 1.5 34 1

GroupAggregate (cost=4,955.04..4,956.16 rows=50 width=76) (actual time=38.956..39.014 rows=34 loops=1)

  • Group Key: cte0_2.c2, cte0_2.c3, cte0_2.c4
17. 0.048 38.935 ↓ 1.3 64 1

Sort (cost=4,955.04..4,955.16 rows=50 width=72) (actual time=38.931..38.935 rows=64 loops=1)

  • Sort Key: cte0_2.c2, cte0_2.c3, cte0_2.c4
  • Sort Method: quicksort Memory: 30kB
18. 1.205 38.887 ↓ 1.3 64 1

Nested Loop Left Join (cost=0.00..4,953.63 rows=50 width=72) (actual time=32.746..38.887 rows=64 loops=1)

  • Join Filter: (t1662_meetingactivityaccountfact_1.c7809_accountid = cte0_2.c2)
  • Rows Removed by Join Filter: 11,788
19. 0.010 0.010 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_meeting_current cte0_2 (cost=0.00..1.00 rows=50 width=68) (actual time=0.001..0.010 rows=34 loops=1)

20. 0.786 37.672 ↓ 15.1 348 34

Materialize (cost=0.00..4,935.43 rows=23 width=8) (actual time=0.023..1.108 rows=348 loops=34)

21. 36.886 36.886 ↓ 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.776..36.886 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
22.          

CTE account_activity_count_cte_email_sent_current

23. 1.671 1,554.931 ↑ 1.5 34 1

GroupAggregate (cost=402,467.42..402,482.34 rows=50 width=76) (actual time=1,553.029..1,554.931 rows=34 loops=1)

  • Group Key: cte0_3.c2, cte0_3.c3, cte0_3.c4
24. 2.198 1,553.260 ↓ 3.5 4,091 1

Sort (cost=402,467.42..402,470.31 rows=1,153 width=72) (actual time=1,552.997..1,553.260 rows=4,091 loops=1)

  • Sort Key: cte0_3.c2, cte0_3.c3, cte0_3.c4
  • Sort Method: quicksort Memory: 417kB
25. 5.484 1,551.062 ↓ 3.5 4,091 1

Hash Right Join (cost=1,001.62..402,408.79 rows=1,153 width=72) (actual time=50.613..1,551.062 rows=4,091 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact.c8021_accountid = cte0_3.c2)
26. 47.299 1,545.557 ↓ 1.8 53,217 1

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

  • Workers Planned: 2
  • Workers Launched: 2
27. 1,498.258 1,498.258 ↓ 1.4 17,739 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact (cost=0.00..398,326.08 rows=12,328 width=8) (actual time=0.259..1,498.258 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
28. 0.011 0.021 ↑ 1.5 34 1

Hash (cost=1.00..1.00 rows=50 width=68) (actual time=0.021..0.021 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
29. 0.010 0.010 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_meeting_current cte0_3 (cost=0.00..1.00 rows=50 width=68) (actual time=0.002..0.010 rows=34 loops=1)

30.          

CTE account_activity_count_cte_email_received_current

31. 1.027 2,410.425 ↑ 1.5 34 1

GroupAggregate (cost=401,853.22..401,865.32 rows=50 width=76) (actual time=2,409.256..2,410.425 rows=34 loops=1)

  • Group Key: cte0_4.c2, cte0_4.c3, cte0_4.c4
32. 1.470 2,409.398 ↓ 3.2 2,958 1

Sort (cost=401,853.22..401,855.54 rows=928 width=72) (actual time=2,409.226..2,409.398 rows=2,958 loops=1)

  • Sort Key: cte0_4.c2, cte0_4.c3, cte0_4.c4
  • Sort Method: quicksort Memory: 328kB
33. 3.552 2,407.928 ↓ 3.2 2,958 1

Hash Right Join (cost=1,001.62..401,807.48 rows=928 width=72) (actual time=208.895..2,407.928 rows=2,958 loops=1)

  • Hash Cond: (t1816_emailactivityaccountfact_1.c8021_accountid = cte0_4.c2)
34. 601.719 2,239.649 ↓ 1.1 25,870 1

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

  • Workers Planned: 2
  • Workers Launched: 2
35. 1,637.930 1,637.930 ↑ 1.2 8,623 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..398,326.08 rows=9,922 width=8) (actual time=1.264..1,637.930 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
36. 0.017 164.727 ↑ 1.5 34 1

Hash (cost=1.00..1.00 rows=50 width=68) (actual time=164.726..164.727 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
37. 164.710 164.710 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_meeting_current cte0_4 (cost=0.00..1.00 rows=50 width=68) (actual time=164.693..164.710 rows=34 loops=1)

38.          

CTE aggregated_domains_cte

39. 0.067 6.525 ↑ 1.9 32 1

GroupAggregate (cost=556.22..557.46 rows=62 width=36) (actual time=6.473..6.525 rows=32 loops=1)

  • Group Key: t1402_accountdomaindistinct.c7150_accountid
40. 0.051 6.458 ↓ 1.5 95 1

Sort (cost=556.22..556.37 rows=62 width=17) (actual time=6.450..6.458 rows=95 loops=1)

  • Sort Key: t1402_accountdomaindistinct.c7150_accountid
  • Sort Method: quicksort Memory: 30kB
41. 2.374 6.407 ↓ 1.5 95 1

Hash Semi Join (cost=1.62..554.37 rows=62 width=17) (actual time=0.081..6.407 rows=95 loops=1)

  • Hash Cond: (t1402_accountdomaindistinct.c7150_accountid = cte0_5.c2)
42. 4.014 4.014 ↑ 1.0 17,601 1

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

  • Filter: ((NOT deleted) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 1,402
43. 0.009 0.019 ↑ 1.5 34 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
44. 0.010 0.010 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_meeting_current cte0_5 (cost=0.00..1.00 rows=50 width=4) (actual time=0.002..0.010 rows=34 loops=1)

45.          

CTE accounts_with_open_opportunities

46. 0.010 344.547 ↑ 8.7 21 1

Group (cost=2,678.95..2,679.86 rows=183 width=4) (actual time=344.536..344.547 rows=21 loops=1)

  • Group Key: t1323_oppfact.c6938_opp_accountid
47. 0.060 344.537 ↑ 6.1 30 1

Sort (cost=2,678.95..2,679.40 rows=183 width=4) (actual time=344.533..344.537 rows=30 loops=1)

  • Sort Key: t1323_oppfact.c6938_opp_accountid
  • Sort Method: quicksort Memory: 26kB
48. 0.478 344.477 ↑ 6.1 30 1

Nested Loop (cost=3.92..2,672.07 rows=183 width=4) (actual time=1.110..344.477 rows=30 loops=1)

49. 0.421 67.151 ↑ 1.4 242 1

Nested Loop (cost=3.50..1,387.01 rows=328 width=8) (actual time=0.041..67.151 rows=242 loops=1)

50. 0.083 0.090 ↑ 4.2 34 1

HashAggregate (cost=3.22..4.65 rows=143 width=4) (actual time=0.022..0.090 rows=34 loops=1)

  • Group Key: cte0_6.c1
51. 0.007 0.007 ↑ 4.2 34 1

CTE Scan on filtered_accounts_cte cte0_6 (cost=0.00..2.86 rows=143 width=4) (actual time=0.001..0.007 rows=34 loops=1)

52. 66.640 66.640 ↓ 3.5 7 34

Index Scan using t1323_oppfact_249_ptimestamp on t1323_oppfact (cost=0.28..9.65 rows=2 width=8) (actual time=0.690..1.960 rows=7 loops=34)

  • Index Cond: (c6938_opp_accountid = cte0_6.c1)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
53. 276.848 276.848 ↓ 0.0 0 242

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=1.144..1.144 rows=0 loops=242)

  • Index Cond: ((sid = t1323_oppfact.sid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (NOT c6749_isclosed))
  • Rows Removed by Filter: 1
54. 0.038 4,355.778 ↑ 1.5 34 1

Hash Left Join (cost=8.52..16.11 rows=50 width=133) (actual time=4,355.679..4,355.778 rows=34 loops=1)

  • Hash Cond: (cte0.c2 = cte4.c1)
55. 0.028 4,349.176 ↑ 1.5 34 1

Hash Join (cost=6.50..13.41 rows=50 width=104) (actual time=4,349.090..4,349.176 rows=34 loops=1)

  • Hash Cond: (cte0.c2 = cte3.c2)
56. 0.031 1,938.686 ↑ 1.5 34 1

Hash Join (cost=4.88..11.10 rows=50 width=104) (actual time=1,938.618..1,938.686 rows=34 loops=1)

  • Hash Cond: (cte0.c2 = cte2.c2)
57. 0.029 383.685 ↑ 1.5 34 1

Hash Join (cost=3.25..8.78 rows=50 width=92) (actual time=383.633..383.685 rows=34 loops=1)

  • Hash Cond: (cte0.c2 = cte1.c2)
58. 0.031 344.608 ↑ 1.5 34 1

Hash Right Join (cost=1.62..6.47 rows=50 width=80) (actual time=344.572..344.608 rows=34 loops=1)

  • Hash Cond: (cte5.c1 = cte0.c2)
59. 344.557 344.557 ↑ 8.7 21 1

CTE Scan on accounts_with_open_opportunities cte5 (cost=0.00..3.66 rows=183 width=4) (actual time=344.538..344.557 rows=21 loops=1)

60. 0.012 0.020 ↑ 1.5 34 1

Hash (cost=1.00..1.00 rows=50 width=76) (actual time=0.020..0.020 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
61. 0.008 0.008 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_meeting_current cte0 (cost=0.00..1.00 rows=50 width=76) (actual time=0.002..0.008 rows=34 loops=1)

62. 0.016 39.048 ↑ 1.5 34 1

Hash (cost=1.00..1.00 rows=50 width=12) (actual time=39.048..39.048 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
63. 39.032 39.032 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_meeting_upcoming cte1 (cost=0.00..1.00 rows=50 width=12) (actual time=38.960..39.032 rows=34 loops=1)

64. 0.014 1,554.970 ↑ 1.5 34 1

Hash (cost=1.00..1.00 rows=50 width=12) (actual time=1,554.970..1,554.970 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
65. 1,554.956 1,554.956 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_email_sent_current cte2 (cost=0.00..1.00 rows=50 width=12) (actual time=1,553.032..1,554.956 rows=34 loops=1)

66. 0.013 2,410.462 ↑ 1.5 34 1

Hash (cost=1.00..1.00 rows=50 width=12) (actual time=2,410.462..2,410.462 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
67. 2,410.449 2,410.449 ↑ 1.5 34 1

CTE Scan on account_activity_count_cte_email_received_current cte3 (cost=0.00..1.00 rows=50 width=12) (actual time=2,409.259..2,410.449 rows=34 loops=1)

68. 0.016 6.564 ↑ 1.9 32 1

Hash (cost=1.24..1.24 rows=62 width=36) (actual time=6.564..6.564 rows=32 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
69. 6.548 6.548 ↑ 1.9 32 1

CTE Scan on aggregated_domains_cte cte4 (cost=0.00..1.24 rows=62 width=36) (actual time=6.476..6.548 rows=32 loops=1)

Planning time : 12.071 ms
Execution time : 4,356.845 ms