explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 14yv

Settings
# exclusive inclusive rows x rows loops node
1. 0.701 3.352 ↓ 1.3 861 1

HashAggregate (cost=1,531.94..1,538.77 rows=683 width=8) (actual time=3.017..3.352 rows=861 loops=1)

  • Group Key: userthreads.thread_id
  • Buffers: shared hit=1196
2. 0.360 2.651 ↓ 1.4 990 1

Append (cost=21.72..1,530.23 rows=683 width=8) (actual time=0.137..2.651 rows=990 loops=1)

  • Buffers: shared hit=1196
3. 0.746 0.834 ↓ 1.1 667 1

Bitmap Heap Scan on userthreads (cost=21.72..1,187.50 rows=611 width=8) (actual time=0.136..0.834 rows=667 loops=1)

  • Recheck Cond: (user_id = 9)
  • Filter: tmember
  • Rows Removed by Filter: 61
  • Heap Blocks: exact=415
  • Buffers: shared hit=421
4. 0.088 0.088 ↓ 1.1 776 1

Bitmap Index Scan on ix_userthreads_user_id (cost=0.00..21.56 rows=686 width=0) (actual time=0.088..0.088 rows=776 loops=1)

  • Index Cond: (user_id = 9)
  • Buffers: shared hit=6
5. 0.037 0.512 ↑ 1.3 30 1

HashAggregate (cost=42.36..42.76 rows=40 width=8) (actual time=0.488..0.512 rows=30 loops=1)

  • Group Key: groupthreads.thread_id
  • Buffers: shared hit=16
6. 0.246 0.475 ↑ 1.0 40 1

Hash Semi Join (cost=24.72..42.26 rows=40 width=8) (actual time=0.059..0.475 rows=40 loops=1)

  • Hash Cond: (groupthreads.group_id = usergroups.group_id)
  • Buffers: shared hit=16
7. 0.196 0.196 ↓ 1.0 866 1

Seq Scan on groupthreads (cost=0.00..14.43 rows=843 width=16) (actual time=0.009..0.196 rows=866 loops=1)

  • Buffers: shared hit=6
8. 0.009 0.033 ↑ 1.0 10 1

Hash (cost=24.60..24.60 rows=10 width=8) (actual time=0.033..0.033 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=10
9. 0.016 0.024 ↑ 1.0 10 1

Bitmap Heap Scan on usergroups (cost=4.36..24.60 rows=10 width=8) (actual time=0.012..0.024 rows=10 loops=1)

  • Recheck Cond: (user_id = 9)
  • Heap Blocks: exact=8
  • Buffers: shared hit=10
10. 0.008 0.008 ↑ 1.0 10 1

Bitmap Index Scan on ix_usergroups_user_id (cost=0.00..4.36 rows=10 width=0) (actual time=0.008..0.008 rows=10 loops=1)

  • Index Cond: (user_id = 9)
  • Buffers: shared hit=2
11. 0.130 0.945 ↓ 9.2 293 1

Nested Loop (cost=4.74..293.15 rows=32 width=8) (actual time=0.023..0.945 rows=293 loops=1)

  • Buffers: shared hit=759
12. 0.011 0.019 ↑ 1.2 4 1

Bitmap Heap Scan on userorg uo (cost=4.32..18.28 rows=5 width=8) (actual time=0.013..0.019 rows=4 loops=1)

  • Recheck Cond: (user_id = 9)
  • Filter: (is_member AND is_member)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=4
  • Buffers: shared hit=6
13. 0.008 0.008 ↑ 1.0 6 1

Bitmap Index Scan on ix_userorg_user_id (cost=0.00..4.32 rows=6 width=0) (actual time=0.008..0.008 rows=6 loops=1)

  • Index Cond: (user_id = 9)
  • Buffers: shared hit=2
14. 0.796 0.796 ↓ 73.0 73 4

Index Scan using ix_threads_organization_id on threads t (cost=0.41..54.96 rows=1 width=16) (actual time=0.009..0.199 rows=73 loops=4)

  • Index Cond: (organization_id = uo.organization_id)
  • Filter: org_visible
  • Rows Removed by Filter: 202
  • Buffers: shared hit=753