explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hBcJ : Optimization for: Optimization for: plan #A7dh; plan #e2E8

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 326,130.965 613,029.536 ↑ 1.0 1 1

Aggregate (cost=2,402,194.12..2,402,194.13 rows=1 width=8) (actual time=613,029.536..613,029.536 rows=1 loops=1)

  • Buffers: shared hit=17,369,998 read=7,829,785
  • I/O Timings: read=195,788.413
2. 10,614.665 286,898.571 ↓ 1.0 18,615,692 1

Merge Join (cost=28.19..2,355,774.67 rows=18,567,779 width=1,282) (actual time=0.155..286,898.571 rows=18,615,692 loops=1)

  • Merge Cond: (users.id = members.user_id)
  • Buffers: shared hit=17,369,983 read=7,829,777
  • I/O Timings: read=195,786.181
3. 142,106.687 142,106.687 ↓ 1.0 6,988,706 1

Index Scan using users_pkey on public.users (cost=0.43..1,013,956.45 rows=6,845,176 width=1,286) (actual time=0.128..142,106.687 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=2,447,342 read=4,538,198
  • I/O Timings: read=104,010.515
4. 134,177.219 134,177.219 ↑ 1.0 18,753,475 1

Index Scan using index_members_on_user_id on public.members (cost=0.44..1,104,098.02 rows=18,924,119 width=4) (actual time=0.022..134,177.219 rows=18,753,475 loops=1)

  • Filter: (members.access_level > 10)
  • Rows Removed by Filter: 238,529
  • Buffers: shared hit=14,922,641 read=3,291,579
  • I/O Timings: read=91,775.666