explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3LM6

Settings
# exclusive inclusive rows x rows loops node
1. 0.764 4.061 ↓ 1.0 1,108 1

HashAggregate (cost=1,015.29..1,025.96 rows=1,067 width=8) (actual time=3.787..4.061 rows=1,108 loops=1)

  • Group Key: userthreads.thread_id
  • Buffers: shared hit=1287
2. 0.507 3.297 ↓ 1.3 1,342 1

Append (cost=14.39..1,012.62 rows=1,067 width=8) (actual time=0.152..3.297 rows=1,342 loops=1)

  • Buffers: shared hit=1287
3. 0.885 0.985 ↑ 1.0 911 1

Bitmap Heap Scan on userthreads (cost=14.39..818.56 rows=920 width=8) (actual time=0.152..0.985 rows=911 loops=1)

  • Recheck Cond: (user_id = 23)
  • Filter: tmember
  • Rows Removed by Filter: 86
  • Heap Blocks: exact=446
  • Buffers: shared hit=451
4. 0.100 0.100 ↓ 1.0 1,048 1

Bitmap Index Scan on ix_userthreads_user_id (cost=0.00..14.16 rows=1,033 width=0) (actual time=0.100..0.100 rows=1,048 loops=1)

  • Index Cond: (user_id = 23)
  • Buffers: shared hit=5
5. 0.092 0.650 ↓ 1.2 119 1

HashAggregate (cost=39.44..40.40 rows=96 width=8) (actual time=0.624..0.650 rows=119 loops=1)

  • Group Key: groupthreads.thread_id
  • Buffers: shared hit=22
6. 0.290 0.558 ↓ 2.3 223 1

Hash Join (cost=20.94..39.20 rows=96 width=8) (actual time=0.085..0.558 rows=223 loops=1)

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

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

  • Buffers: shared hit=6
8. 0.010 0.053 ↑ 1.0 26 1

Hash (cost=20.61..20.61 rows=26 width=8) (actual time=0.053..0.053 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=16
9. 0.032 0.043 ↑ 1.0 26 1

Bitmap Heap Scan on usergroups (cost=1.68..20.61 rows=26 width=8) (actual time=0.015..0.043 rows=26 loops=1)

  • Recheck Cond: (user_id = 23)
  • Heap Blocks: exact=14
  • Buffers: shared hit=16
10. 0.011 0.011 ↑ 1.0 26 1

Bitmap Index Scan on ix_usergroups_user_id (cost=0.00..1.68 rows=26 width=0) (actual time=0.011..0.011 rows=26 loops=1)

  • Index Cond: (user_id = 23)
  • Buffers: shared hit=2
11. 0.120 1.155 ↓ 6.1 312 1

Nested Loop (cost=0.69..142.99 rows=51 width=8) (actual time=0.015..1.155 rows=312 loops=1)

  • Buffers: shared hit=814
12. 0.019 0.019 ↑ 1.0 8 1

Index Scan using ix_userorg_user_id on userorg uo (cost=0.28..9.86 rows=8 width=8) (actual time=0.007..0.019 rows=8 loops=1)

  • Index Cond: (user_id = 23)
  • Filter: (is_member AND is_member)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=11
13. 1.016 1.016 ↓ 39.0 39 8

Index Scan using ix_threads_organization_id on threads t (cost=0.41..16.63 rows=1 width=16) (actual time=0.007..0.127 rows=39 loops=8)

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