explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kEG : with users_state_index

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 291,365.435 956,037.649 ↑ 1.0 1 1

Aggregate (cost=2,405,898.16..2,405,898.17 rows=1 width=8) (actual time=956,037.648..956,037.649 rows=1 loops=1)

  • Buffers: shared hit=17,348,189 read=7,894,128 dirtied=36,181
  • I/O Timings: read=559,138.855
2. 11,647.658 664,672.214 ↑ 1.0 18,621,159 1

Merge Join (cost=6.51..2,359,323.37 rows=18,629,918 width=1,279) (actual time=2.355..664,672.214 rows=18,621,159 loops=1)

  • Merge Cond: (users.id = members.user_id)
  • Buffers: shared hit=17,348,174 read=7,894,120 dirtied=36,181
  • I/O Timings: read=559,136.427
3. 211,903.117 211,903.117 ↑ 1.0 6,990,814 1

Index Scan using users_pkey on public.users (cost=0.43..1,016,071.08 rows=6,991,004 width=1,283) (actual time=0.263..211,903.117 rows=6,990,814 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,436
  • Buffers: shared hit=2,452,433 read=4,569,444 dirtied=33,503
  • I/O Timings: read=168,654.850
4. 441,121.439 441,121.439 ↑ 1.0 18,758,947 1

Index Scan using index_members_on_user_id on public.members (cost=0.44..1,103,746.28 rows=18,993,000 width=4) (actual time=2.086..441,121.439 rows=18,758,947 loops=1)

  • Filter: (members.access_level > 10)
  • Rows Removed by Filter: 238,610
  • Buffers: shared hit=14,895,741 read=3,324,676 dirtied=2,678
  • I/O Timings: read=390,481.578