explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1GI : Rep View Grid - Account Engagement with account owned Count. Sort by accounts count.

Settings
# exclusive inclusive rows x rows loops node
1. 0.166 8,363.776 ↓ 18.1 362 1

Sort (cost=1,018,338.28..1,018,338.33 rows=20 width=108) (actual time=8,363.749..8,363.776 rows=362 loops=1)

  • Sort Key: cte0.c5 NULLS FIRST, cte0.c3 NULLS FIRST
  • Sort Method: quicksort Memory: 53kB
2.          

CTE filtered_users_cte

3. 51.277 51.277 ↑ 1.0 100,006 1

Seq Scan on t_userdim (cost=0.00..3,541.11 rows=100,006 width=16) (actual time=0.020..51.277 rows=100,006 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 1
4.          

CTE totalaccountsownedcte

5. 2,141.826 7,417.885 ↓ 5.0 5 1

GroupAggregate (cost=607,477.76..627,727.77 rows=1 width=12) (actual time=4,544.372..7,417.885 rows=5 loops=1)

  • Group Key: t_accountdim.c_ownerid
6. 2,100.817 5,276.059 ↓ 2.0 5,399,999 1

Sort (cost=607,477.76..614,227.76 rows=2,700,000 width=8) (actual time=4,544.357..5,276.059 rows=5,399,999 loops=1)

  • Sort Key: t_accountdim.c_ownerid
  • Sort Method: external merge Disk: 95,024kB
7. 1,579.372 3,175.242 ↓ 2.0 5,399,999 1

Hash Join (cost=2,254.64..245,227.64 rows=2,700,000 width=8) (actual time=163.330..3,175.242 rows=5,399,999 loops=1)

  • Hash Cond: (t_accountdim.c_ownerid = cte0_1.c1)
8. 1,432.569 1,432.569 ↑ 1.0 5,400,000 1

Seq Scan on t_accountdim (cost=0.00..168,723.00 rows=5,400,000 width=8) (actual time=0.013..1,432.569 rows=5,400,000 loops=1)

  • Filter: (NOT deleted)
9. 17.348 163.301 ↓ 500.0 100,006 1

Hash (cost=2,252.14..2,252.14 rows=200 width=4) (actual time=163.301..163.301 rows=100,006 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,073kB
10. 54.263 145.953 ↓ 500.0 100,006 1

HashAggregate (cost=2,250.14..2,252.14 rows=200 width=4) (actual time=122.908..145.953 rows=100,006 loops=1)

  • Group Key: cte0_1.c1
11. 91.690 91.690 ↑ 1.0 100,006 1

CTE Scan on filtered_users_cte cte0_1 (cost=0.00..2,000.12 rows=100,006 width=4) (actual time=0.001..91.690 rows=100,006 loops=1)

12.          

CTE users_with_totalaccountsowned_cte

13. 0.006 7,483.009 ↑ 1.0 20 1

Limit (cost=5,041.30..5,041.35 rows=20 width=76) (actual time=7,483.002..7,483.009 rows=20 loops=1)

14. 25.276 7,483.003 ↑ 5,000.3 20 1

Sort (cost=5,041.30..5,291.31 rows=100,006 width=76) (actual time=7,483.001..7,483.003 rows=20 loops=1)

  • Sort Key: cte0_2.c2 NULLS FIRST, cte1_1.c3 NULLS FIRST
  • Sort Method: top-N heapsort Memory: 25kB
15. 23.259 7,457.727 ↑ 1.0 100,006 1

Hash Left Join (cost=0.03..2,380.18 rows=100,006 width=76) (actual time=7,417.933..7,457.727 rows=100,006 loops=1)

  • Hash Cond: (cte1_1.c1 = cte0_2.c1)
16. 16.567 16.567 ↑ 1.0 100,006 1

CTE Scan on filtered_users_cte cte1_1 (cost=0.00..2,000.12 rows=100,006 width=68) (actual time=0.021..16.567 rows=100,006 loops=1)

17. 0.009 7,417.901 ↓ 5.0 5 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=7,417.901..7,417.901 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 7,417.892 7,417.892 ↓ 5.0 5 1

CTE Scan on totalaccountsownedcte cte0_2 (cost=0.00..0.02 rows=1 width=12) (actual time=4,544.374..7,417.892 rows=5 loops=1)

19.          

CTE primarysortedusercte_meeting_upcoming_with_percentage

20. 0.021 7,996.232 ↑ 1.0 20 1

Hash Left Join (cost=104,758.33..104,758.92 rows=20 width=92) (actual time=7,996.223..7,996.232 rows=20 loops=1)

  • Hash Cond: (cte1_3.c1 = cte0_3.c2)
21.          

CTE innerprimarysortedusercte

22. 0.002 513.206 ↓ 0.0 0 1

GroupAggregate (cost=1.26..104,758.30 rows=1 width=12) (actual time=513.205..513.206 rows=0 loops=1)

  • Group Key: t_meetingaccountfact.c_activityowneridsf
23. 76.385 513.204 ↓ 0.0 0 1

Merge Semi Join (cost=1.26..99,145.70 rows=1,122,518 width=8) (actual time=513.204..513.204 rows=0 loops=1)

  • Merge Cond: (t_meetingaccountfact.c_activityowneridsf = cte1_2.c1)
24. 436.796 436.796 ↑ 1.0 1,122,517 1

Index Scan using activiowner on t_meetingaccountfact (cost=0.43..79,500.81 rows=1,122,518 width=8) (actual time=0.076..436.796 rows=1,122,517 loops=1)

  • Filter: (c_isshared AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 2
25. 0.011 0.023 ↑ 10.0 2 1

Sort (cost=0.83..0.88 rows=20 width=4) (actual time=0.022..0.023 rows=2 loops=1)

  • Sort Key: cte1_2.c1
  • Sort Method: quicksort Memory: 25kB
26. 0.012 0.012 ↑ 1.0 20 1

CTE Scan on users_with_totalaccountsowned_cte cte1_2 (cost=0.00..0.40 rows=20 width=4) (actual time=0.001..0.012 rows=20 loops=1)

27. 7,483.004 7,483.004 ↑ 1.0 20 1

CTE Scan on users_with_totalaccountsowned_cte cte1_3 (cost=0.00..0.40 rows=20 width=76) (actual time=7,483.004..7,483.004 rows=20 loops=1)

28. 0.001 513.207 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=513.207..513.207 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
29. 513.206 513.206 ↓ 0.0 0 1

CTE Scan on innerprimarysortedusercte cte0_3 (cost=0.00..0.02 rows=1 width=12) (actual time=513.206..513.206 rows=0 loops=1)

30.          

CTE user_activity_count_cte_meeting_current_with_percentage

31. 0.055 367.247 ↑ 1.0 20 1

GroupAggregate (cost=257,623.01..277,267.38 rows=20 width=92) (actual time=367.196..367.247 rows=20 loops=1)

  • Group Key: cte0_4.c1, cte0_4.c2, cte0_4.c3, cte0_4.c4
32. 0.044 367.192 ↑ 56,125.9 20 1

Sort (cost=257,623.01..260,429.31 rows=1,122,518 width=80) (actual time=367.175..367.192 rows=20 loops=1)

  • Sort Key: cte0_4.c1, cte0_4.c2, cte0_4.c3, cte0_4.c4
  • Sort Method: quicksort Memory: 26kB
33. 92.628 367.148 ↑ 56,125.9 20 1

Hash Right Join (cost=0.65..45,062.45 rows=1,122,518 width=80) (actual time=367.139..367.148 rows=20 loops=1)

  • Hash Cond: (t_meetingaccountfact_1.c_activityowneridsf = cte0_4.c2)
34. 274.496 274.496 ↑ 1.0 1,122,517 1

Seq Scan on t_meetingaccountfact t_meetingaccountfact_1 (cost=0.00..29,627.18 rows=1,122,518 width=8) (actual time=0.008..274.496 rows=1,122,517 loops=1)

  • Filter: ((NOT deleted) AND c_isshared)
  • Rows Removed by Filter: 2
35. 0.004 0.024 ↑ 1.0 20 1

Hash (cost=0.40..0.40 rows=20 width=76) (actual time=0.024..0.024 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.020 0.020 ↑ 1.0 20 1

CTE Scan on primarysortedusercte_meeting_upcoming_with_percentage cte0_4 (cost=0.00..0.40 rows=20 width=76) (actual time=0.000..0.020 rows=20 loops=1)

37. 0.112 8,363.610 ↓ 18.1 362 1

Hash Join (cost=0.65..1.33 rows=20 width=108) (actual time=8,363.519..8,363.610 rows=362 loops=1)

  • Hash Cond: (cte0.c1 = cte1.c1)
38. 7,996.230 7,996.230 ↑ 1.0 20 1

CTE Scan on primarysortedusercte_meeting_upcoming_with_percentage cte0 (cost=0.00..0.40 rows=20 width=92) (actual time=7,996.226..7,996.230 rows=20 loops=1)

39. 0.005 367.268 ↑ 1.0 20 1

Hash (cost=0.40..0.40 rows=20 width=48) (actual time=367.268..367.268 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 367.263 367.263 ↑ 1.0 20 1

CTE Scan on user_activity_count_cte_meeting_current_with_percentage cte1 (cost=0.00..0.40 rows=20 width=48) (actual time=367.199..367.263 rows=20 loops=1)

Planning time : 1.639 ms
Execution time : 8,382.045 ms