explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SQl

Settings
# exclusive inclusive rows x rows loops node
1. 0.199 2,612.172 ↑ 1.0 1 1

Aggregate (cost=387,441.76..387,441.77 rows=1 width=8) (actual time=2,612.172..2,612.172 rows=1 loops=1)

2. 0.466 2,611.973 ↓ 3,503.0 3,503 1

Unique (cost=387,441.74..387,441.75 rows=1 width=4) (actual time=2,611.266..2,611.973 rows=3,503 loops=1)

3. 1.558 2,611.507 ↓ 3,505.0 3,505 1

Sort (cost=387,441.74..387,441.75 rows=1 width=4) (actual time=2,611.265..2,611.507 rows=3,505 loops=1)

  • Sort Key: clients_client.id
  • Sort Method: quicksort Memory: 261kB
4. 23.024 2,609.949 ↓ 3,505.0 3,505 1

Nested Loop Semi Join (cost=19,718.99..387,441.73 rows=1 width=4) (actual time=348.949..2,609.949 rows=3,505 loops=1)

5. 133.120 573.661 ↓ 1.6 91,512 1

Hash Join (cost=19,718.29..32,298.99 rows=56,955 width=8) (actual time=118.124..573.661 rows=91,512 loops=1)

  • Hash Cond: (clients_groupassignment.client_id = clients_client.id)
6. 322.554 322.554 ↓ 1.0 468,087 1

Seq Scan on clients_groupassignment (cost=0.00..11,352.15 rows=468,015 width=8) (actual time=0.019..322.554 rows=468,087 loops=1)

7. 117.987 117.987 ↓ 1.0 91,523 1

Hash (cost=18,620.04..18,620.04 rows=87,860 width=4) (actual time=117.987..117.987 rows=91,523 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4242kB
  • -> Index Scan using clients_client_brokerage_id on clients_client (cost=0.42..18620.04 rows=87860 width=4) (actual time=0.029..100.669 rows=91523 loo$s=1)
  • Index Cond: (brokerage_id = 18855)
  • Filter: (status <> ALL ('{50,70}'::integer[]))
  • Rows Removed by Filter: 7184
8. 716.871 2,013.264 ↓ 0.0 0 91,512

Nested Loop (cost=0.70..6.23 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=91,512)

  • -> Index Scan using clients_groupassignment_pkey on clients_groupassignment v0 (cost=0.42..5.93 rows=1 width=8) (actual time=0.022..0.022 rows=0 loops=9151$)
  • Index Cond: (id = clients_groupassignment.id)
  • Filter: ((status = ANY ('{10,15}'::integer[])) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 1
9. 3.507 3.507 ↑ 1.0 1 3,507

Index Scan using accounts_employeegroup_pkey on accounts_employeegroup v1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=3,507)

  • Index Cond: (id = v0.group_id)
  • Filter: (company_id = 18855)
  • Rows Removed by Filter: 0
10.          

SubPlan (for Nested Loop)

11. 0.000 1,292.886 ↑ 1.0 1 20,853

Nested Loop (cost=1.00..5.46 rows=1 width=0) (actual time=0.062..0.062 rows=1 loops=20,853)

12. 23.749 938.385 ↑ 1.0 1 20,853

Nested Loop (cost=0.71..5.15 rows=1 width=8) (actual time=0.044..0.045 rows=1 loops=20,853)

13. 896.679 896.679 ↓ 0.0 1 20,853

Index Only Scan using clients_client_agents_client_id_37afacd828f1e2f6_uniq on clients_client_agents u3 (cost=0.42..2.64 rows=1 widt$=4) (cost=0..0 rows=0 width=0) (actual time=0.043..0.043 rows=1 loops=20,853)

  • Index Cond: (client_id = v0.client_id)
  • Heap Fetches: 15566
14. 17.957 17.957 ↑ 1.0 1 17,957

Index Only Scan using auth_user_pkey on auth_user u0 (cost=0.29..2.51 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=17,957)

  • Index Cond: (id = u3.user_id)
  • Heap Fetches: 17389
15. 359.140 359.140 ↓ 0.0 1 17,957

Index Only Scan using accounts_employeegroup_employees_employeegroup_id_user_id_key on accounts_employeegroup_employees u1 (cost=0.29..0.3$ rows=1 width=4) (cost=0..0 rows=0 width=0) (actual time=0.020..0.020 rows=1 loops=17,957)

  • Index Cond: ((employeegroup_id = v0.group_id) AND (user_id = u0.id))
  • Heap Fetches: 7364
16. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,962.11..19,872.79 rows=571,494 width=8) (never executed)

  • Hash Cond: (u3_1.user_id = u0_1.id)
17. 0.000 0.000 ↓ 0.0 0

Seq Scan on clients_client_agents u3_1 (cost=0.00..9,820.90 rows=633,290 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,642.93..1,642.93 rows=25,534 width=12) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1,167.56..1,642.93 rows=25,534 width=12) (never executed)

  • Hash Cond: (u1_1.user_id = u0_1.id)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on accounts_employeegroup_employees u1_1 (cost=0.00..408.34 rows=25,534 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Hash (cost=813.87..813.87 rows=28,295 width=4) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using auth_user_pkey on auth_user u0_1 (cost=0.29..813.87 rows=28,295 width=4) (never executed)

Planning time : 515.892 ms
Execution time : 2,612.289 ms