explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c3wg : long partial index

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 278,817.366 700,572.846 ↑ 1.0 1 1

Aggregate (cost=1,755,475.88..1,755,475.89 rows=1 width=8) (actual time=700,572.845..700,572.846 rows=1 loops=1)

  • Buffers: shared hit=12,352,517 read=2,939,037
  • I/O Timings: read=354,587.678
2. 8,905.519 421,755.480 ↓ 1.0 18,615,692 1

Merge Join (cost=12.63..1,709,380.03 rows=18,438,341 width=1,282) (actual time=6.131..421,755.480 rows=18,615,692 loops=1)

  • Merge Cond: (users.id = members.user_id)
  • Buffers: shared hit=12,352,517 read=2,939,037
  • I/O Timings: read=354,587.678
3. 346,770.166 346,770.166 ↑ 1.0 6,988,706 1

Index Scan using users_pkey on public.users (cost=0.43..1,018,224.70 rows=6,990,841 width=1,286) (actual time=0.024..346,770.166 rows=6,988,706 loops=1)

  • Filter: (((users.state)::text = 'active'::text) AND ((users.user_type IS NULL) OR (users.user_type = ANY ('{NULL,6,4}'::integer[]))) AND ((users.user_type IS NULL) OR (users.user_type <> ALL ('{2,6,1,3,7,8}'::integer[]))))
  • Rows Removed by Filter: 136,298
  • Buffers: shared hit=4,205,174 read=2,780,346
  • I/O Timings: read=303,211.982
4. 66,079.795 66,079.795 ↑ 1.0 18,753,475 1

Index Only Scan using tmp_index on public.members (cost=0.44..448,609.91 rows=18,792,199 width=4) (actual time=6.100..66,079.795 rows=18,753,475 loops=1)

  • Heap Fetches: 2,560,083
  • Buffers: shared hit=8,147,343 read=158,691
  • I/O Timings: read=51,375.696