explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IZvS

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 12,206.020 ↑ 1.0 20 1

Limit (cost=22,107.99..22,108.04 rows=20 width=110) (actual time=12,206.014..12,206.020 rows=20 loops=1)

2. 134.574 12,206.014 ↑ 15,058.3 20 1

Sort (cost=22,107.99..22,860.90 rows=301,166 width=110) (actual time=12,206.013..12,206.014 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. 87.620 12,071.440 ↓ 1.3 392,000 1

Hash Join (cost=455.70..14,094.07 rows=301,166 width=110) (actual time=3.031..12,071.440 rows=392,000 loops=1)

  • Hash Cond: (staffs_staff.user_id = users_user.id)
4. 623.553 11,983.792 ↓ 1.3 392,000 1

Hash Join (cost=452.57..13,283.63 rows=301,166 width=97) (actual time=2.989..11,983.792 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. 119.222 157.418 ↑ 1.0 400,000 1

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

  • Hash Cond: (staff_events_staffsession.staff_id = staffs_staff.id)
6. 38.175 38.175 ↑ 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..38.175 rows=400,000 loops=1)

7. 0.012 0.021 ↑ 24.0 50 1

Hash (cost=22.00..22.00 rows=1,200 width=12) (actual time=0.021..0.021 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.192 2.791 ↓ 1.0 8,000 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 439kB
10. 1.599 1.599 ↓ 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.599 rows=8,000 loops=1)

11.          

SubPlan (for Hash Join)

12. 0.009 0.030 ↓ 0.0 0 1

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

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

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

14. 0.001 0.021 ↓ 0.0 0 1

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

15. 0.009 0.009 ↑ 1.0 1 1

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

  • Filter: club_see_all
  • Rows Removed by Filter: 49
16. 0.011 0.011 ↓ 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.011..0.011 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. 4,000.000 11,200.000 ↓ 25.0 25 400,000

Sort (cost=19.23..19.24 rows=1 width=12) (actual time=0.027..0.028 rows=25 loops=400,000)

  • Sort Key: u0_1.tree_id, u0_1.lft
  • Sort Method: quicksort Memory: 27kB
19. 1,200.000 7,200.000 ↓ 49.0 49 400,000

Nested Loop (cost=13.88..19.22 rows=1 width=12) (actual time=0.002..0.018 rows=49 loops=400,000)

20. 1,599.991 2,800.000 ↑ 1.0 1 400,000

Hash Join (cost=13.74..17.37 rows=1 width=12) (actual time=0.001..0.007 rows=1 loops=400,000)

  • Hash Cond: (ev_shn_me.staff_id = ev_staff.id)
21. 1,200.000 1,200.000 ↑ 1.0 50 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.003 rows=50 loops=400,000)

22. 0.004 0.009 ↑ 6.0 1 1

Hash (cost=13.67..13.67 rows=6 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.002 0.005 ↑ 6.0 1 1

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

  • Recheck Cond: (user_id = 2)
  • Heap Blocks: exact=1
24. 0.003 0.003 ↑ 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.003..0.003 rows=1 loops=1)

  • Index Cond: (user_id = 2)
25. 3,200.000 3,200.000 ↓ 8.2 49 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.008 rows=49 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: 1
26. 0.013 0.028 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
27. 0.015 0.015 ↑ 1.0 50 1

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

Planning time : 1.221 ms