explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e2E8 : Optimization for: plan #A7dh

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4,161.541 1,076,726.521 ↑ 1.7 4,025,435 1

Unique (cost=5,209,465.54..5,348,723.88 rows=6,845,176 width=13) (actual time=1,067,967.209..1,076,726.521 rows=4,025,435 loops=1)

  • Buffers: shared hit=17,369,790 read=7,829,523
  • I/O Timings: read=958,530.484
2. 16,660.626 1,072,564.980 ↓ 1.0 18,615,692 1

Sort (cost=5,209,465.54..5,255,884.99 rows=18,567,779 width=13) (actual time=1,067,967.207..1,072,564.980 rows=18,615,692 loops=1)

  • Sort Key: users.id, users.user_type
  • Sort Method: external merge Disk: 382,560kB
  • Buffers: shared hit=17,369,790 read=7,829,523
  • I/O Timings: read=958,530.484
3. 13,952.951 1,055,904.354 ↓ 1.0 18,615,692 1

Merge Join (cost=28.19..2,355,774.67 rows=18,567,779 width=13) (actual time=0.109..1,055,904.354 rows=18,615,692 loops=1)

  • Merge Cond: (users.id = members.user_id)
  • Buffers: shared hit=17,369,790 read=7,829,523
  • I/O Timings: read=958,530.484
4. 601,888.951 601,888.951 ↓ 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=13) (actual time=0.094..601,888.951 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,441 read=4,537,652
  • I/O Timings: read=569,347.843
5. 440,062.452 440,062.452 ↑ 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.011..440,062.452 rows=18,753,475 loops=1)

  • Filter: (members.access_level > 10)
  • Rows Removed by Filter: 238,529
  • Buffers: shared hit=14,922,349 read=3,291,871
  • I/O Timings: read=389,182.641