explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ahfm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.093 169.347 ↓ 46.0 46 1

Nested Loop (cost=3,340.68..9,489.95 rows=1 width=544) (actual time=168.817..169.347 rows=46 loops=1)

2.          

CTE cu

3. 30.261 44.774 ↓ 1.0 48,305 1

Hash Join (cost=148.02..2,024.00 rows=48,294 width=46) (actual time=1.584..44.774 rows=48,305 loops=1)

  • Hash Cond: (cm_1.user_id = u_1.id)
4. 12.942 12.942 ↓ 1.0 48,305 1

Seq Scan on channel_members cm_1 (cost=0.00..1,211.94 rows=48,294 width=32) (actual time=0.006..12.942 rows=48,305 loops=1)

5. 0.672 1.571 ↑ 1.0 1,734 1

Hash (cost=126.34..126.34 rows=1,734 width=46) (actual time=1.571..1.571 rows=1,734 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 148kB
6. 0.899 0.899 ↑ 1.0 1,734 1

Seq Scan on users u_1 (cost=0.00..126.34 rows=1,734 width=46) (actual time=0.005..0.899 rows=1,734 loops=1)

7. 0.059 169.034 ↓ 27.5 110 1

Nested Loop (cost=1,316.40..7,464.68 rows=4 width=548) (actual time=168.801..169.034 rows=110 loops=1)

8. 34.256 168.805 ↓ 34.0 34 1

Hash Right Join (cost=1,316.12..7,461.42 rows=1 width=548) (actual time=168.785..168.805 rows=34 loops=1)

  • Hash Cond: (m.channel_id = c.id)
  • Filter: (m.inserted_at IS NULL)
  • Rows Removed by Filter: 67287
9. 42.038 42.038 ↑ 1.0 117,804 1

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

10. 0.323 92.511 ↓ 140.2 561 1

Hash (cost=1,316.07..1,316.07 rows=4 width=548) (actual time=92.511..92.511 rows=561 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
11. 2.165 92.188 ↓ 140.2 561 1

Nested Loop (cost=0.29..1,316.07 rows=4 width=548) (actual time=1.596..92.188 rows=561 loops=1)

12. 80.015 80.015 ↓ 166.8 5,004 1

CTE Scan on cu (cost=0.00..1,086.62 rows=30 width=532) (actual time=1.587..80.015 rows=5,004 loops=1)

  • Filter: ((NOT dummy) AND onboarded AND active AND ((role)::text = 'member'::text))
  • Rows Removed by Filter: 43301
13. 10.008 10.008 ↓ 0.0 0 5,004

Index Scan using channels_pkey on channels c (cost=0.29..7.64 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=5,004)

  • Index Cond: (id = cu.channel_id)
  • Filter: (type = 'psychologist'::channel_type)
  • Rows Removed by Filter: 1
14. 0.170 0.170 ↑ 1.3 3 34

Index Scan using channel_members_channel_id_index on channel_members cm (cost=0.29..3.22 rows=4 width=32) (actual time=0.003..0.005 rows=3 loops=34)

  • Index Cond: (channel_id = c.id)
15. 0.220 0.220 ↓ 0.0 0 110

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=110)

  • Index Cond: (id = cm.user_id)
  • Filter: ((role)::text = 'psychologist'::text)
  • Rows Removed by Filter: 1