explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j0nK

Settings
# exclusive inclusive rows x rows loops node
1. 1.811 24,538.591 ↑ 1.0 1 1

Aggregate (cost=1,611,156.08..1,611,156.09 rows=1 width=8) (actual time=24,538.591..24,538.591 rows=1 loops=1)

  • Buffers: shared hit=3,364 read=278,496 dirtied=23,595 written=23,514
  • I/O Timings: read=13,573.357 write=537.075
2. 21,285.147 24,536.780 ↑ 6,723.2 1,173 1

Seq Scan on public.members (cost=4,428.61..1,591,440.26 rows=7,886,329 width=0) (actual time=3,360.813..24,536.780 rows=1,173 loops=1)

  • Filter: (((members.invite_token IS NOT NULL) AND (members.created_at >= '2020-10-16 00:00:00'::timestamp without time zone) AND (members.created_at <= '2020-10-16 23:59:59.999999'::timestamp without time zone) AND ((members.source_type)::text = 'Namespace'::text) AND (hashed SubPlan 2)) OR (((members.source_type)::text = 'Project'::text) AND (hashed SubPlan 3)))
  • Rows Removed by Filter: 19,159,196
  • Buffers: shared hit=3,364 read=278,496 dirtied=23,595 written=23,514
  • I/O Timings: read=13,573.357 write=537.075
3.          

SubPlan (for Seq Scan)

4. 0.000 0.000 ↓ 0.0 0 0

CTE Scan on base_and_descendants namespaces_2 (cost=1,419.04..1,422.26 rows=161 width=4) (actual time=0.000..0.000 rows=0 loops=0)

5.          

CTE base_and_descendants

6. 0.000 0.000 ↓ 0.0 0 0

Recursive Union (cost=0.43..1,419.04 rows=161 width=346) (actual time=0.000..0.000 rows=0 loops=0)

7. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces (cost=0.43..3.45 rows=1 width=346) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (((namespaces.type)::text = 'Group'::text) AND (namespaces.id = 9,970))
8. 0.000 0.000 ↓ 0.0 0 0

Nested Loop (cost=0.56..141.24 rows=16 width=346) (actual time=0.000..0.000 rows=0 loops=0)

9. 0.000 0.000 ↓ 0.0 0 0

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=0)

10. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..14.08 rows=2 width=346) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
11. 4.431 3,251.633 ↑ 1.3 1,111 1

Nested Loop (cost=1.00..3,002.20 rows=1,497 width=4) (actual time=19.010..3,251.633 rows=1,111 loops=1)

  • Buffers: shared hit=3,364 read=2,400 dirtied=68
  • I/O Timings: read=3,183.156
12. 1,780.682 1,780.682 ↑ 1.4 1,111 1

Index Scan using index_routes_on_path_text_pattern_ops on public.routes rs (cost=0.56..3.59 rows=1,545 width=4) (actual time=14.385..1,780.682 rows=1,111 loops=1)

  • Index Cond: (((rs.path)::text ~>=~ 'gitlab-org/'::text) AND ((rs.path)::text ~<~ 'gitlab-org0'::text))
  • Filter: (((rs.path)::text ~~ 'gitlab-org/%'::text) AND ((rs.source_type)::text = 'Project'::text))
  • Rows Removed by Filter: 187
  • Buffers: shared hit=95 read=1,200 dirtied=8
  • I/O Timings: read=1,765.103
13. 1,466.520 1,466.520 ↑ 1.0 1 1,111

Index Only Scan using projects_pkey on public.projects (cost=0.43..1.94 rows=1 width=4) (actual time=1.320..1.320 rows=1 loops=1,111)

  • Index Cond: (projects.id = rs.source_id)
  • Heap Fetches: 137
  • Buffers: shared hit=3,269 read=1,200 dirtied=60
  • I/O Timings: read=1,418.054