explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p0E2

Settings
# exclusive inclusive rows x rows loops node
1. 6.051 5,881.233 ↓ 1.3 16 1

GroupAggregate (cost=163,634.57..163,635.38 rows=12 width=128) (actual time=5,875.226..5,881.233 rows=16 loops=1)

  • Group Key: assignable_executive.username, pm_owner.username, level_0_exec.username, level_1_exec.username, level_2_exec.username
2. 3.989 5,875.182 ↓ 144.1 1,729 1

Sort (cost=163,634.57..163,634.60 rows=12 width=112) (actual time=5,875.088..5,875.182 rows=1,729 loops=1)

  • Sort Key: assignable_executive.username, pm_owner.username, level_0_exec.username, level_1_exec.username, level_2_exec.username
  • Sort Method: quicksort Memory: 305kB
3. 1.901 5,871.193 ↓ 144.1 1,729 1

Nested Loop Left Join (cost=1,001.81..163,634.36 rows=12 width=112) (actual time=63.312..5,871.193 rows=1,729 loops=1)

4. 3,009.704 5,869.292 ↓ 144.1 1,729 1

Nested Loop Left Join (cost=1,001.39..163,625.77 rows=12 width=100) (actual time=63.286..5,869.292 rows=1,729 loops=1)

  • Join Filter: (level_1_exec.username = assignable_executive.level_2_executive_id)
  • Rows Removed by Join Filter: 60,613,553
5. 0.000 8.467 ↓ 144.1 1,729 1

Gather (cost=1,001.39..83,264.53 rows=12 width=95) (actual time=0.868..8.467 rows=1,729 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.416 127.092 ↓ 115.2 576 3 / 3

Nested Loop Left Join (cost=1.40..82,263.33 rows=5 width=95) (actual time=0.278..127.092 rows=576 loops=3)

7. 0.289 126.676 ↓ 115.2 576 3 / 3

Nested Loop Left Join (cost=0.98..82,221.28 rows=5 width=91) (actual time=0.274..126.676 rows=576 loops=3)

8. 0.306 121.200 ↓ 115.2 576 3 / 3

Nested Loop (cost=0.41..82,164.44 rows=5 width=83) (actual time=0.195..121.200 rows=576 loops=3)

9. 116.534 116.534 ↑ 1.2 872 3 / 3

Parallel Seq Scan on pom_app_owner assignable_executive_org (cost=0.00..73,977.84 rows=1,029 width=40) (actual time=0.133..116.534 rows=872 loops=3)

  • Filter: ((title ~~* 'SVP%'::text) OR (title ~~* 'VP%'::text) OR (title ~~* 'VICE PRESIDENT%'::text) OR (title ~~* '%DIR%'::text))
  • Rows Removed by Filter: 10,814
10. 4.360 4.360 ↑ 1.0 1 2,616 / 3

Index Scan using pc_owner_username_69d07652e9cc556c_like on pom_app_owner assignable_executive (cost=0.41..7.96 rows=1 width=51) (actual time=0.005..0.005 rows=1 loops=2,616)

  • Index Cond: (username = assignable_executive_org.level_1_executive_id)
  • Filter: ((username <> 'no-owner'::text) AND (array_length(path_from_root, 1) = 1))
  • Rows Removed by Filter: 0
11. 0.576 5.187 ↑ 1.0 1 1,729 / 3

Nested Loop Left Join (cost=0.56..11.36 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1,729)

12. 3.458 3.458 ↑ 1.0 1 1,729 / 3

Index Scan using pc_user_assigned_owner_owner_id_335563e83743d505_uniq on user_assigned_owner uao (cost=0.28..3.06 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=1,729)

  • Index Cond: ((assignable_executive.username = owner_id) AND (application_id = 2))
  • Filter: (owner_role = 3)
13. 1.153 1.153 ↑ 1.0 1 1,729 / 3

Index Scan using auth_user_pkey on auth_user pm_owner (cost=0.28..8.30 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=1,729)

  • Index Cond: (uao.user_id = id)
14. 0.000 0.000 ↓ 0.0 0 1,729 / 3

Index Scan using pc_owner_username_69d07652e9cc556c_like on pom_app_owner level_1_exec (cost=0.41..8.41 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1,729)

  • Index Cond: (username = assignable_executive.level_1_executive_id)
15. 2,795.654 2,851.121 ↓ 1.0 35,057 1,729

Materialize (cost=0.00..74,208.91 rows=34,661 width=12) (actual time=0.000..1.649 rows=35,057 loops=1,729)

16. 55.467 55.467 ↓ 1.0 35,057 1

Seq Scan on pom_app_owner level_2_exec (cost=0.00..74,035.61 rows=34,661 width=12) (actual time=0.002..55.467 rows=35,057 loops=1)

17. 0.000 0.000 ↑ 1.0 1 1,729

Materialize (cost=0.41..8.44 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=1,729)

18. 0.022 0.022 ↑ 1.0 1 1

Index Scan using pc_owner_username_69d07652e9cc556c_like on pom_app_owner level_0_exec (cost=0.41..8.43 rows=1 width=12) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (username = 'crobbins'::text)
Planning time : 1.879 ms
Execution time : 5,882.477 ms