explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7NTv

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 21.507 ↓ 11.0 11 1

Limit (cost=8,869.39..8,869.39 rows=1 width=1,254) (actual time=21.504..21.507 rows=11 loops=1)

  • Buffers: shared hit=16,618
2. 0.056 21.504 ↓ 11.0 11 1

Sort (cost=8,869.39..8,869.39 rows=1 width=1,254) (actual time=21.503..21.504 rows=11 loops=1)

  • Sort Key: ((milestones.due_date IS NULL)), ((milestones.id IS NULL)), milestones.due_date DESC, issues.id DESC
  • Sort Method: quicksort Memory: 39kB
  • Buffers: shared hit=16,618
3. 0.258 21.448 ↓ 11.0 11 1

Nested Loop Semi Join (cost=1,644.38..8,869.38 rows=1 width=1,254) (actual time=7.394..21.448 rows=11 loops=1)

  • Buffers: shared hit=16,618
4. 0.275 19.618 ↓ 262.0 262 1

Nested Loop Semi Join (cost=1,643.81..8,868.51 rows=1 width=1,264) (actual time=4.166..19.618 rows=262 loops=1)

  • Buffers: shared hit=14,901
5. 0.339 18.819 ↓ 262.0 262 1

Nested Loop Left Join (cost=542.88..7,761.16 rows=1 width=1,268) (actual time=4.133..18.819 rows=262 loops=1)

  • Buffers: shared hit=14,881
6. 0.348 17.956 ↓ 262.0 262 1

Nested Loop Left Join (cost=542.45..7,760.72 rows=1 width=1,260) (actual time=4.123..17.956 rows=262 loops=1)

  • Filter: ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level = ANY ('{20,30}'::integer[])) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=14,113
7. 0.180 16.822 ↓ 262.0 262 1

Nested Loop (cost=542.02..7,756.62 rows=1 width=1,264) (actual time=4.110..16.822 rows=262 loops=1)

  • Buffers: shared hit=13,065
8. 0.252 14.546 ↓ 262.0 262 1

Nested Loop Semi Join (cost=541.58..7,752.55 rows=1 width=1,256) (actual time=4.082..14.546 rows=262 loops=1)

  • Buffers: shared hit=10,775
9. 0.410 12.626 ↑ 4.3 278 1

Nested Loop (cost=541.01..7,034.80 rows=1,193 width=1,252) (actual time=4.068..12.626 rows=278 loops=1)

  • Buffers: shared hit=9,404
10. 0.737 4.284 ↑ 1.4 1,322 1

HashAggregate (cost=540.45..558.54 rows=1,809 width=4) (actual time=3.969..4.284 rows=1,322 loops=1)

  • Group Key: label_links_1.target_id
  • Buffers: shared hit=2,869
11. 3.547 3.547 ↑ 1.4 1,322 1

Index Scan using index_label_links_on_label_id on public.label_links label_links_1 (cost=0.57..535.93 rows=1,809 width=4) (actual time=0.023..3.547 rows=1,322 loops=1)

  • Index Cond: (label_links_1.label_id = 3,005,495)
  • Filter: ((label_links_1.target_type)::text = 'Issue'::text)
  • Rows Removed by Filter: 1,581
  • Buffers: shared hit=2,869
12. 7.932 7.932 ↓ 0.0 0 1,322

Index Scan using issues_pkey on public.issues (cost=0.56..3.58 rows=1 width=1,248) (actual time=0.006..0.006 rows=0 loops=1,322)

  • Index Cond: (issues.id = label_links_1.target_id)
  • Filter: (issues.state_id = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6,535
13. 1.668 1.668 ↑ 1.0 1 278

Index Only Scan using index_on_label_links_all_columns on public.label_links label_links_2 (cost=0.57..0.60 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=278)

  • Index Cond: ((label_links_2.target_id = issues.id) AND (label_links_2.label_id = 2,278,656) AND (label_links_2.target_type = 'Issue'::text))
  • Heap Fetches: 34
  • Buffers: shared hit=1,371
14. 1.048 2.096 ↑ 1.0 1 262

Index Scan using projects_pkey on public.projects (cost=0.43..4.07 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=262)

  • Index Cond: (projects.id = issues.project_id)
  • Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2,290
15.          

SubPlan (for Index Scan)

16. 1.048 1.048 ↑ 1.0 1 262

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations (cost=0.57..3.59 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=262)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10))
  • Heap Fetches: 64
  • Buffers: shared hit=1,242
17. 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 project_authorizations_1 (cost=0.57..127.57 rows=1,500 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_1.user_id = 4,156,052) AND (project_authorizations_1.access_level >= 10))
  • Heap Fetches: 0
18. 0.786 0.786 ↑ 1.0 1 262

Index Scan using index_project_features_on_project_id on public.project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=262)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=1,048
19.          

SubPlan (for Nested Loop Left Join)

20. 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 project_authorizations_2 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_2.user_id = 4,156,052) AND (project_authorizations_2.project_id = projects.id) AND (project_authorizations_2.access_level >= 10))
  • Heap Fetches: 0
21. 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 project_authorizations_3 (cost=0.57..127.57 rows=1,500 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_3.user_id = 4,156,052) AND (project_authorizations_3.access_level >= 10))
  • Heap Fetches: 0
22. 0.524 0.524 ↑ 1.0 1 262

Index Scan using milestones_pkey on public.milestones (cost=0.42..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=262)

  • Index Cond: (issues.milestone_id = milestones.id)
  • Buffers: shared hit=768
23. 0.524 0.524 ↑ 30.2 5 262

CTE Scan on base_and_descendants namespaces (cost=1,100.93..1,103.95 rows=151 width=4) (actual time=0.000..0.002 rows=5 loops=262)

  • Buffers: shared hit=20
24.          

CTE base_and_descendants

25. 0.047 0.119 ↑ 11.6 13 1

Recursive Union (cost=0.43..1,100.93 rows=151 width=357) (actual time=0.028..0.119 rows=13 loops=1)

  • Buffers: shared hit=20
26. 0.015 0.015 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=357) (actual time=0.013..0.015 rows=1 loops=1)

  • Index Cond: (namespaces_1.id = 9,970)
  • Filter: ((namespaces_1.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4
27. 0.009 0.057 ↑ 1.2 12 1

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.025..0.057 rows=12 loops=1)

  • Buffers: shared hit=16
28. 0.002 0.002 ↑ 10.0 1 1

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

29. 0.046 0.046 ↓ 6.0 12 1

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..10.90 rows=2 width=357) (actual time=0.020..0.046 rows=12 loops=1)

  • Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
  • Filter: ((namespaces_2.type)::text = 'Group'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=16
30. 1.572 1.572 ↓ 0.0 0 262

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.57..0.72 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=262)

  • Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
  • Heap Fetches: 215
  • Filter: (label_links.label_id = ANY ('{1924053,2191076,2229310,2251841,2278648,2379994,2414262,3519299,3791725,3892549,3892770,3899495,3969396,3999541,3999564,4007552,4007738,4049204,4057321,4063693,4117093,6190961,7142293,7157910,7256789,7717873,7841215,8761704,8827899,8828075,8828148,9084503,10778837,10778846,11602405,12073682,12932265,13682327,15339054}'::integer[]))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1,717