explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cdGm

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 22.144 ↓ 10.0 10 1

Limit (cost=2,023.01..2,023.02 rows=1 width=1,258) (actual time=22.141..22.144 rows=10 loops=1)

  • Buffers: shared hit=15,643
2. 0.061 22.141 ↓ 10.0 10 1

Sort (cost=2,023.01..2,023.02 rows=1 width=1,258) (actual time=22.140..22.141 rows=10 loops=1)

  • Sort Key: ((milestones.due_date IS NULL)), ((milestones.id IS NULL)), milestones.due_date DESC, issues.id DESC
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=15,643
3. 0.233 22.080 ↓ 10.0 10 1

Nested Loop Semi Join (cost=1,471.99..2,023.00 rows=1 width=1,258) (actual time=8.192..22.080 rows=10 loops=1)

  • Buffers: shared hit=15,643
4. 0.384 20.317 ↓ 255.0 255 1

Nested Loop Semi Join (cost=1,471.42..2,022.12 rows=1 width=1,268) (actual time=4.621..20.317 rows=255 loops=1)

  • Buffers: shared hit=14,103
5. 0.263 19.423 ↓ 255.0 255 1

Nested Loop Left Join (cost=58.46..602.31 rows=1 width=1,272) (actual time=4.595..19.423 rows=255 loops=1)

  • Buffers: shared hit=14,077
6. 0.254 18.650 ↓ 255.0 255 1

Nested Loop Left Join (cost=58.03..601.86 rows=1 width=1,264) (actual time=4.590..18.650 rows=255 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=13,357
7. 0.249 17.631 ↓ 255.0 255 1

Nested Loop (cost=57.60..597.77 rows=1 width=1,268) (actual time=4.575..17.631 rows=255 loops=1)

  • Buffers: shared hit=12,337
8. 0.149 15.597 ↓ 255.0 255 1

Nested Loop Semi Join (cost=57.16..593.68 rows=1 width=1,260) (actual time=4.544..15.597 rows=255 loops=1)

  • Buffers: shared hit=10,167
9. 0.075 13.530 ↓ 3.7 274 1

Nested Loop (cost=56.60..548.55 rows=75 width=1,256) (actual time=4.527..13.530 rows=274 loops=1)

  • Buffers: shared hit=8,847
10. 0.921 4.824 ↓ 9.0 1,233 1

HashAggregate (cost=56.03..57.40 rows=137 width=4) (actual time=4.408..4.824 rows=1,233 loops=1)

  • Group Key: label_links_1.target_id
  • Buffers: shared hit=2,679
11. 3.903 3.903 ↓ 9.0 1,233 1

Index Scan using index_label_links_on_label_id on public.label_links label_links_1 (cost=0.56..55.69 rows=137 width=4) (actual time=0.030..3.903 rows=1,233 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,481
  • Buffers: shared hit=2,679
12. 8.631 8.631 ↓ 0.0 0 1,233

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

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

Index Only Scan using index_on_label_links_all_columns on public.label_links label_links_2 (cost=0.56..0.60 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=274)

  • 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: 0
  • Buffers: shared hit=1,320
14. 1.020 1.785 ↑ 1.0 1 255

Index Scan using projects_pkey on public.projects (cost=0.43..4.09 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=255)

  • 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,170
15.          

SubPlan (for Index Scan)

16. 0.765 0.765 ↑ 1.0 1 255

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.003..0.003 rows=1 loops=255)

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10))
  • Heap Fetches: 0
  • Buffers: shared hit=1,150
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..272.02 rows=1,222 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.765 0.765 ↑ 1.0 1 255

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=255)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=1,020
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..272.02 rows=1,222 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.510 0.510 ↑ 1.0 1 255

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=255)

  • Index Cond: (issues.milestone_id = milestones.id)
  • Buffers: shared hit=720
23. 0.510 0.510 ↑ 32.2 5 255

CTE Scan on base_and_descendants namespaces (cost=1,412.97..1,416.19 rows=161 width=4) (actual time=0.000..0.002 rows=5 loops=255)

  • Buffers: shared hit=26
24.          

CTE base_and_descendants

25. 0.117 0.198 ↑ 8.5 19 1

Recursive Union (cost=0.43..1,412.97 rows=161 width=329) (actual time=0.020..0.198 rows=19 loops=1)

  • Buffers: shared hit=26
26. 0.012 0.012 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=329) (actual time=0.012..0.012 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.013 0.069 ↓ 1.1 18 1

Nested Loop (cost=0.56..140.63 rows=16 width=329) (actual time=0.021..0.069 rows=18 loops=1)

  • Buffers: shared hit=22
28. 0.003 0.003 ↑ 10.0 1 1

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

29. 0.053 0.053 ↓ 9.0 18 1

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..14.02 rows=2 width=329) (actual time=0.015..0.053 rows=18 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=22
30. 1.530 1.530 ↓ 0.0 0 255

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.73 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=255)

  • Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
  • Heap Fetches: 6
  • 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,540