explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GDvs

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,442.526 ↓ 10.0 10 1

Limit (cost=33,704.41..33,704.42 rows=1 width=1,262) (actual time=1,442.523..1,442.526 rows=10 loops=1)

  • Buffers: shared hit=1,311,538
2. 0.059 1,442.522 ↓ 10.0 10 1

Sort (cost=33,704.41..33,704.42 rows=1 width=1,262) (actual time=1,442.521..1,442.522 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=1,311,538
3. 16.790 1,442.463 ↓ 10.0 10 1

Aggregate (cost=33,698.15..33,704.40 rows=1 width=1,262) (actual time=1,427.623..1,442.463 rows=10 loops=1)

  • Group Key: issues.id, milestones.id
  • Filter: (count(DISTINCT labels.title) = 3)
  • Rows Removed by Filter: 7,599
  • Buffers: shared hit=1,311,538
4. 16.228 1,425.673 ↓ 28.5 7,935 1

Sort (cost=33,698.15..33,698.84 rows=278 width=1,269) (actual time=1,423.482..1,425.673 rows=7,935 loops=1)

  • Sort Key: issues.id DESC, milestones.id
  • Sort Method: quicksort Memory: 13,477kB
  • Buffers: shared hit=1,311,538
5. 6.847 1,409.445 ↓ 28.5 7,935 1

Nested Loop Left Join (cost=1,421.06..33,686.86 rows=278 width=1,269) (actual time=38.234..1,409.445 rows=7,935 loops=1)

  • Buffers: shared hit=1,311,538
6. 8.917 1,386.728 ↓ 28.5 7,935 1

Nested Loop Left Join (cost=1,420.63..33,563.29 rows=278 width=1,261) (actual time=38.227..1,386.728 rows=7,935 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=1,293,686
7. 69.722 1,352.735 ↓ 27.4 7,935 1

Nested Loop (cost=1,420.20..32,374.68 rows=290 width=1,265) (actual time=38.203..1,352.735 rows=7,935 loops=1)

  • Buffers: shared hit=1,260,699
8. 146.189 568.104 ↓ 88.5 238,303 1

Nested Loop (cost=1,419.76..31,129.23 rows=2,692 width=1,260) (actual time=1.473..568.104 rows=238,303 loops=1)

  • Buffers: shared hit=307,312
9. 24.095 150.751 ↓ 16.8 45,194 1

Nested Loop (cost=1,419.20..29,379.51 rows=2,697 width=1,256) (actual time=1.426..150.751 rows=45,194 loops=1)

  • Buffers: shared hit=56,611
10. 0.350 9.176 ↑ 1.7 979 1

Nested Loop (cost=1,418.63..16,399.75 rows=1,633 width=4) (actual time=1.395..9.176 rows=979 loops=1)

  • Buffers: shared hit=6,367
11. 0.164 1.451 ↑ 1.3 125 1

HashAggregate (cost=1,418.20..1,419.81 rows=161 width=4) (actual time=1.360..1.451 rows=125 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=627
12. 1.287 1.287 ↑ 1.3 125 1

CTE Scan on base_and_descendants namespaces (cost=1,412.97..1,416.19 rows=161 width=4) (actual time=0.026..1.287 rows=125 loops=1)

  • Buffers: shared hit=627
13.          

CTE base_and_descendants

14. 0.271 1.139 ↑ 1.3 125 1

Recursive Union (cost=0.43..1,412.97 rows=161 width=329) (actual time=0.024..1.139 rows=125 loops=1)

  • Buffers: shared hit=627
15. 0.018 0.018 ↑ 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.018..0.018 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
16. 0.085 0.850 ↓ 1.6 25 5

Nested Loop (cost=0.56..140.63 rows=16 width=329) (actual time=0.014..0.170 rows=25 loops=5)

  • Buffers: shared hit=623
17. 0.015 0.015 ↓ 2.5 25 5

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

18. 0.750 0.750 ↑ 2.0 1 125

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.004..0.006 rows=1 loops=125)

  • 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=623
19. 3.459 7.375 ↑ 1.2 8 125

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.94 rows=10 width=8) (actual time=0.011..0.059 rows=8 loops=125)

  • Index Cond: (projects.namespace_id = namespaces.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=5,740
20.          

SubPlan (for Index Scan)

21. 3.916 3.916 ↑ 1.0 1 979

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

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10))
  • Heap Fetches: 113
  • Buffers: shared hit=4,383
22. 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
23. 117.480 117.480 ↑ 3.8 46 979

Index Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on public.issues (cost=0.56..6.18 rows=177 width=1,252) (actual time=0.011..0.120 rows=46 loops=979)

  • Index Cond: ((issues.project_id = projects.id) AND (issues.state_id = 1))
  • Buffers: shared hit=50,244
24. 271.164 271.164 ↓ 2.5 5 45,194

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.63 rows=2 width=8) (actual time=0.005..0.006 rows=5 loops=45,194)

  • Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
  • Heap Fetches: 1,847
  • Buffers: shared hit=250,701
25. 714.909 714.909 ↓ 0.0 0 238,303

Index Scan using labels_pkey on public.labels (cost=0.43..0.46 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=238,303)

  • Index Cond: (labels.id = label_links.label_id)
  • Filter: ((labels.title)::text = ANY ('{QA,Quality,bug}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=953,387
26. 23.805 23.805 ↑ 1.0 1 7,935

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=7,935)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=31,740
27.          

SubPlan (for Nested Loop Left Join)

28. 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
29. 1.271 1.271 ↓ 2.6 3,160 1

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.021..1.271 rows=3,160 loops=1)

  • Index Cond: ((project_authorizations_3.user_id = 4,156,052) AND (project_authorizations_3.access_level >= 10))
  • Heap Fetches: 458
  • Buffers: shared hit=1,247
30. 15.870 15.870 ↑ 1.0 1 7,935

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=7,935)

  • Index Cond: (issues.milestone_id = milestones.id)
  • Buffers: shared hit=17,852