explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 254,564.547 911,927.147 ↑ 1.0 1 1

Aggregate (cost=1,750,843.49..1,750,843.50 rows=1 width=8) (actual time=911,927.146..911,927.147 rows=1 loops=1)

  • Buffers: shared hit=12,330,771 read=2,960,783
  • I/O Timings: read=585,641.487
2. 9,439.320 657,362.600 ↓ 1.0 18,615,692 1

Merge Join (cost=12.63..1,704,747.64 rows=18,438,343 width=1,282) (actual time=0.313..657,362.600 rows=18,615,692 loops=1)

  • Merge Cond: (users.id = members.user_id)
  • Buffers: shared hit=12,330,771 read=2,960,783
  • I/O Timings: read=585,641.487
3. 590,516.078 590,516.078 ↓ 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.025..590,516.078 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,182,608 read=2,802,912
  • I/O Timings: read=543,420.954
4. 57,407.202 57,407.202 ↑ 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=0.284..57,407.202 rows=18,753,475 loops=1)

  • Heap Fetches: 2,560,083
  • Buffers: shared hit=8,148,163 read=157,871
  • I/O Timings: read=42,220.534