explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GdvP : Optimization for: Optimization for: plan #Ahfm; plan #ZVKl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.079 84.703 ↓ 46.0 46 1

Nested Loop (cost=572.48..6,865.54 rows=1 width=40) (actual time=80.936..84.703 rows=46 loops=1)

2. 0.391 83.678 ↓ 473.0 473 1

Nested Loop (cost=572.21..6,865.22 rows=1 width=44) (actual time=80.588..83.678 rows=473 loops=1)

  • Join Filter: (c.id = cm.channel_id)
3. 0.186 82.835 ↓ 226.0 226 1

Nested Loop (cost=571.92..6,864.73 rows=1 width=44) (actual time=80.581..82.835 rows=226 loops=1)

4. 0.434 81.747 ↓ 112.8 451 1

Nested Loop (cost=571.64..6,863.46 rows=4 width=48) (actual time=80.566..81.747 rows=451 loops=1)

5. 36.902 80.638 ↓ 225.0 225 1

Hash Right Join (cost=571.35..6,860.20 rows=1 width=16) (actual time=80.551..80.638 rows=225 loops=1)

  • Hash Cond: (m.channel_id = c.id)
  • Filter: (m.inserted_at IS NULL)
  • Rows Removed by Filter: 94487
6. 42.281 42.281 ↑ 1.0 117,805 1

Seq Scan on messages m (cost=0.00..5,700.37 rows=118,537 width=24) (actual time=0.006..42.281 rows=117,805 loops=1)

7. 0.440 1.455 ↑ 1.0 1,362 1

Hash (cost=554.15..554.15 rows=1,376 width=16) (actual time=1.455..1.455 rows=1,362 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 80kB
8. 0.862 1.015 ↑ 1.0 1,362 1

Bitmap Heap Scan on channels c (cost=34.95..554.15 rows=1,376 width=16) (actual time=0.196..1.015 rows=1,362 loops=1)

  • Recheck Cond: (type = 'psychologist'::channel_type)
  • Heap Blocks: exact=398
9. 0.153 0.153 ↓ 1.0 1,421 1

Bitmap Index Scan on channels_type_index (cost=0.00..34.60 rows=1,376 width=0) (actual time=0.153..0.153 rows=1,421 loops=1)

  • Index Cond: (type = 'psychologist'::channel_type)
10. 0.675 0.675 ↑ 2.0 2 225

Index Scan using channel_members_channel_id_index on channel_members cm_1 (cost=0.29..3.22 rows=4 width=32) (actual time=0.003..0.003 rows=2 loops=225)

  • Index Cond: (channel_id = c.id)
11. 0.902 0.902 ↑ 1.0 1 451

Index Scan using users_pkey on users u_1 (cost=0.28..0.31 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=451)

  • Index Cond: (id = cm_1.user_id)
  • Filter: ((role)::text = 'psychologist'::text)
  • Rows Removed by Filter: 0
12. 0.452 0.452 ↑ 2.0 2 226

Index Scan using channel_members_channel_id_index on channel_members cm (cost=0.29..0.44 rows=4 width=32) (actual time=0.001..0.002 rows=2 loops=226)

  • Index Cond: (channel_id = cm_1.channel_id)
13. 0.946 0.946 ↓ 0.0 0 473

Index Scan using users_pkey on users u (cost=0.28..0.31 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=473)

  • Index Cond: (id = cm.user_id)
  • Filter: ((NOT dummy) AND onboarded AND active AND ((role)::text = 'member'::text))
  • Rows Removed by Filter: 1