explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PWZ5

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 82.870 ↓ 11.0 11 1

Limit (cost=16,926.86..16,926.87 rows=1 width=1,254) (actual time=82.866..82.870 rows=11 loops=1)

  • Buffers: shared hit=41,323
2. 0.073 82.866 ↓ 11.0 11 1

Sort (cost=16,926.86..16,926.87 rows=1 width=1,254) (actual time=82.865..82.866 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=41,323
3. 0.028 82.793 ↓ 11.0 11 1

Nested Loop Left Join (cost=14,860.84..16,926.85 rows=1 width=1,254) (actual time=72.173..82.793 rows=11 loops=1)

  • Buffers: shared hit=41,323
4. 0.036 82.710 ↓ 11.0 11 1

Nested Loop Left Join (cost=14,860.42..16,926.41 rows=1 width=1,248) (actual time=72.160..82.710 rows=11 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=41,283
5. 0.030 82.608 ↓ 11.0 11 1

Hash Semi Join (cost=14,859.98..16,922.31 rows=1 width=1,252) (actual time=72.140..82.608 rows=11 loops=1)

  • Hash Cond: (projects.namespace_id = namespaces.id)
  • Buffers: shared hit=41,239
6. 0.035 80.577 ↑ 8.0 11 1

Nested Loop (cost=13,752.63..15,814.73 rows=88 width=1,256) (actual time=70.129..80.577 rows=11 loops=1)

  • Buffers: shared hit=40,478
7. 0.222 80.377 ↑ 14.1 11 1

Nested Loop (cost=13,752.20..15,183.76 rows=155 width=1,248) (actual time=70.093..80.377 rows=11 loops=1)

  • Buffers: shared hit=40,385
8. 22.271 76.991 ↓ 1.9 452 1

HashAggregate (cost=13,751.63..14,338.93 rows=235 width=4) (actual time=69.882..76.991 rows=452 loops=1)

  • Group Key: label_links.target_id
  • Filter: (count(1) = 3)
  • Rows Removed by Filter: 29,589
  • Buffers: shared hit=38,191
9. 54.720 54.720 ↑ 1.5 32,319 1

Index Scan using index_label_links_on_label_id on public.label_links (cost=0.57..13,516.60 rows=47,007 width=4) (actual time=0.027..54.720 rows=32,319 loops=1)

  • Index Cond: (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,3005495,2278656}'::integer[]))
  • Filter: ((label_links.target_type)::text = 'Issue'::text)
  • Rows Removed by Filter: 17,340
  • Buffers: shared hit=38,191
10. 3.164 3.164 ↓ 0.0 0 452

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

  • Index Cond: (issues.id = label_links.target_id)
  • Filter: (issues.state_id = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,194
11. 0.088 0.165 ↑ 1.0 1 11

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

  • 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=93
12.          

SubPlan (for Index Scan)

13. 0.077 0.077 ↑ 1.0 1 11

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.007..0.007 rows=1 loops=11)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10))
  • Heap Fetches: 2
  • Buffers: shared hit=49
14. 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
15. 0.049 2.001 ↓ 1.0 153 1

Hash (cost=1,105.46..1,105.46 rows=151 width=4) (actual time=2.001..2.001 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=761
16. 1.952 1.952 ↓ 1.0 153 1

CTE Scan on base_and_descendants namespaces (cost=1,100.93..1,103.95 rows=151 width=4) (actual time=0.051..1.952 rows=153 loops=1)

  • Buffers: shared hit=761
17.          

CTE base_and_descendants

18. 0.453 1.713 ↓ 1.0 153 1

Recursive Union (cost=0.43..1,100.93 rows=151 width=357) (actual time=0.048..1.713 rows=153 loops=1)

  • Buffers: shared hit=761
19. 0.020 0.020 ↑ 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.019..0.020 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
20. 0.144 1.240 ↓ 2.0 30 5

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.030..0.248 rows=30 loops=5)

  • Buffers: shared hit=757
21. 0.025 0.025 ↓ 3.1 31 5

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

22. 1.071 1.071 ↑ 2.0 1 153

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.005..0.007 rows=1 loops=153)

  • 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=757
23. 0.066 0.066 ↑ 1.0 1 11

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.006..0.006 rows=1 loops=11)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=44
24.          

SubPlan (for Nested Loop Left Join)

25. 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
26. 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
27. 0.055 0.055 ↑ 1.0 1 11

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

  • Index Cond: (issues.milestone_id = milestones.id)
  • Buffers: shared hit=40