explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GMIA : Optimization for: plan #IZvS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 4,053.737 ↑ 1.0 20 1

Limit (cost=21,906.57..21,906.62 rows=20 width=110) (actual time=4,053.731..4,053.737 rows=20 loops=1)

2. 135.519 4,053.731 ↑ 15,058.3 20 1

Sort (cost=21,906.57..22,659.49 rows=301,166 width=110) (actual time=4,053.730..4,053.731 rows=20 loops=1)

  • Sort Key: event_sessions_session.start, users_user.first_name, users_user.last_name
  • Sort Method: top-N heapsort Memory: 30kB
3. 78.772 3,918.212 ↓ 1.3 392,000 1

Hash Join (cost=455.70..13,892.65 rows=301,166 width=110) (actual time=2.734..3,918.212 rows=392,000 loops=1)

  • Hash Cond: (staffs_staff.user_id = users_user.id)
4. 899.744 3,839.413 ↓ 1.3 392,000 1

Hash Join (cost=452.57..13,082.21 rows=301,166 width=97) (actual time=2.696..3,839.413 rows=392,000 loops=1)

  • Hash Cond: (staff_events_staffsession.session_id = event_sessions_session.id)
  • Join Filter: ((hashed SubPlan 1) OR (SubPlan 2))
  • Rows Removed by Join Filter: 8000
5. 102.916 137.069 ↑ 1.0 400,000 1

Hash Join (cost=37.00..11,082.35 rows=401,554 width=93) (actual time=0.038..137.069 rows=400,000 loops=1)

  • Hash Cond: (staff_events_staffsession.staff_id = staffs_staff.id)
6. 34.131 34.131 ↑ 1.0 400,000 1

Seq Scan on staff_events_staffsession (cost=0.00..9,987.54 rows=401,554 width=85) (actual time=0.005..34.131 rows=400,000 loops=1)

7. 0.013 0.022 ↑ 24.0 50 1

Hash (cost=22.00..22.00 rows=1,200 width=12) (actual time=0.022..0.022 rows=50 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 19kB
8. 0.009 0.009 ↑ 24.0 50 1

Seq Scan on staffs_staff (cost=0.00..22.00 rows=1,200 width=12) (actual time=0.003..0.009 rows=50 loops=1)

9. 1.074 2.569 ↓ 1.0 8,000 1

Hash (cost=300.98..300.98 rows=7,998 width=16) (actual time=2.569..2.569 rows=8,000 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 439kB
10. 1.495 1.495 ↓ 1.0 8,000 1

Seq Scan on event_sessions_session (cost=0.00..300.98 rows=7,998 width=16) (actual time=0.003..1.495 rows=8,000 loops=1)

11.          

SubPlan (forHash Join)

12. 0.014 0.031 ↓ 0.0 0 1

Sort (cost=14.61..14.61 rows=1 width=21) (actual time=0.031..0.031 rows=0 loops=1)

  • Sort Key: u2.first_name, u2.last_name
  • Sort Method: quicksort Memory: 25kB
13. 0.000 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.15..14.60 rows=1 width=21) (actual time=0.017..0.017 rows=0 loops=1)

14. 0.002 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.15..11.96 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1)

15. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on staffs_staffpermission u1 (cost=0.00..1.50 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: club_see_all
  • Rows Removed by Filter: 49
16. 0.008 0.008 ↓ 0.0 0 1

Index Scan using staffs_staff_staff_permission_id_key on staffs_staff u0 (cost=0.15..8.17 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (staff_permission_id = u1.id)
  • Filter: (user_id = 2)
  • Rows Removed by Filter: 1
17. 0.000 0.000 ↓ 0.0 0

Seq Scan on users_user u2 (cost=0.00..2.62 rows=1 width=21) (never executed)

  • Filter: (id = 2)
18. 400.000 2,800.000 ↓ 25.0 25 400,000

Nested Loop (cost=4.34..23.53 rows=1 width=4) (actual time=0.002..0.007 rows=25 loops=400,000)

19. 400.000 400.000 ↑ 1.0 1 400,000

Nested Loop (cost=4.20..21.68 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=400,000)

  • Join Filter: (ev_shn_me.staff_id = ev_staff.id)
  • Rows Removed by Join Filter: 2
20. 0.000 0.000 ↑ 16.7 3 400,000

Seq Scan on staff_hierarchy_staffhierarchynode ev_shn_me (cost=0.00..3.50 rows=50 width=16) (actual time=0.000..0.000 rows=3 loops=400,000)

21. 0.000 0.000 ↑ 6.0 1 1,184,000

Materialize (cost=4.20..13.70 rows=6 width=4) (actual time=0.000..0.000 rows=1 loops=1,184,000)

22. 0.002 0.004 ↑ 6.0 1 1

Bitmap Heap Scan on staffs_staff ev_staff (cost=4.20..13.67 rows=6 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Recheck Cond: (user_id = 2)
  • Heap Blocks: exact=1
23. 0.002 0.002 ↑ 6.0 1 1

Bitmap Index Scan on staffs_staff_user_id_41ce53bd (cost=0.00..4.20 rows=6 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (user_id = 2)
24. 2,000.000 2,000.000 ↓ 4.2 25 400,000

Index Scan using staff_hierarchy_staffhierarchynode_tree_id_31831735 on staff_hierarchy_staffhierarchynode u0_1 (cost=0.14..1.79 rows=6 width=16) (actual time=0.001..0.005 rows=25 loops=400,000)

  • Index Cond: (tree_id = ev_shn_me.tree_id)
  • Filter: ((staff_hierarchy_id = event_sessions_session.staff_hierarchy_v2_id) AND (lft >= ev_shn_me.lft) AND (rght <= ev_shn_me.rght))
  • Rows Removed by Filter: 0
25. 0.013 0.027 ↑ 1.0 50 1

Hash (cost=2.50..2.50 rows=50 width=21) (actual time=0.027..0.027 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.014 0.014 ↑ 1.0 50 1

Seq Scan on users_user (cost=0.00..2.50 rows=50 width=21) (actual time=0.006..0.014 rows=50 loops=1)

Planning time : 1.042 ms