explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VbVC

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 154,456.948 ↑ 1.0 20 1

Limit (cost=49,824,852.40..49,824,852.45 rows=20 width=614) (actual time=154,456.931..154,456.948 rows=20 loops=1)

  • Buffers: shared dirtied=29,957 hit=3,235 read=360,238
2. 2,674.540 154,456.941 ↑ 297,678.8 20 1

Sort (cost=49,824,852.40..49,839,736.34 rows=5,953,577 width=614) (actual time=154,456.929..154,456.941 rows=20 loops=1)

  • Sort Key: (COALESCE(GREATEST(date_part('epoch'::text, projects.last_activity_at), date_part('epoch'::text, projects.last_repository_updated_at), date_part('epoch'::text, projects.updated_at)), '0'::double precision))
  • Sort Method: top-N heapsort Memory: 35kB
  • Buffers: shared dirtied=29,957 hit=3,235 read=360,238
3. 150,727.536 151,782.401 ↑ 4.1 1,441,423 1

Seq Scan on public.projects (cost=0.00..49,666,429.85 rows=5,953,577 width=614) (actual time=1,062.629..151,782.401 rows=1,441,423 loops=1)

  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
  • Rows Removed by Filter: 9,035,875
  • Buffers: shared dirtied=29,957 hit=3,232 read=360,238
4.          

SubPlan (for Seq Scan)

5. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.56..4.58 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations.user_id = 660,180) AND (project_authorizations.project_id = projects.id))
  • Heap Fetches: 0
6. 1,054.865 1,054.865 ↓ 33.5 3,412 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_1 (cost=0.56..45.13 rows=102 width=4) (actual time=8.685..1,054.865 rows=3,412 loops=1)

  • Index Cond: (project_authorizations_1.user_id = 660,180)
  • Heap Fetches: 983
  • Buffers: shared dirtied=289 hit=794 read=831