explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5rZG

Settings
# exclusive inclusive rows x rows loops node
1. 40.297 3,012.663 ↓ 148.0 444 1

Aggregate (cost=39,645.71..39,657.16 rows=3 width=14) (actual time=2,977.061..3,012.663 rows=444 loops=1)

  • Group Key: issues.id
  • Filter: (count(DISTINCT labels.title) = 3)
  • Rows Removed by Filter: 28,235
  • Buffers: shared hit=3,315,759
2. 20.102 2,972.366 ↓ 60.6 30,850 1

Sort (cost=39,645.71..39,646.98 rows=509 width=15) (actual time=2,967.744..2,972.366 rows=30,850 loops=1)

  • Sort Key: issues.id
  • Sort Method: quicksort Memory: 2,215kB
  • Buffers: shared hit=3,315,759
3. 34.883 2,952.264 ↓ 60.6 30,850 1

Nested Loop Left Join (cost=1,420.63..39,622.82 rows=509 width=15) (actual time=20.987..2,952.264 rows=30,850 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: 87
  • Buffers: shared hit=3,315,759
4. 0.000 2,854.106 ↓ 58.2 30,937 1

Nested Loop (cost=1,420.20..37,442.34 rows=532 width=19) (actual time=20.962..2,854.106 rows=30,937 loops=1)

  • Buffers: shared hit=3,190,764
5. 240.326 1,189.618 ↓ 112.7 556,138 1

Nested Loop (cost=1,419.76..35,159.16 rows=4,935 width=14) (actual time=1.351..1,189.618 rows=556,138 loops=1)

  • Buffers: shared hit=965,757
6. 43.797 146.292 ↓ 32.5 160,600 1

Nested Loop (cost=1,419.20..31,982.61 rows=4,944 width=10) (actual time=1.325..146.292 rows=160,600 loops=1)

  • Buffers: shared hit=117,585
7. 0.406 8.511 ↑ 1.7 979 1

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

  • Buffers: shared hit=6,367
8. 0.136 1.355 ↑ 1.3 125 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=627
9. 1.219 1.219 ↑ 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.027..1.219 rows=125 loops=1)

  • Buffers: shared hit=627
10.          

CTE base_and_descendants

11. 0.256 1.079 ↑ 1.3 125 1

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

  • Buffers: shared hit=627
12. 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.017..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
13. 0.165 0.805 ↓ 1.6 25 5

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

  • Buffers: shared hit=623
14. 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)

15. 0.625 0.625 ↑ 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.005 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
16. 2.834 6.750 ↑ 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.010..0.054 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
17.          

SubPlan (for Index Scan)

18. 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
19. 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
20. 93.984 93.984 ↑ 2.0 164 979

Index Only Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on public.issues (cost=0.56..6.30 rows=324 width=10) (actual time=0.006..0.096 rows=164 loops=979)

  • Index Cond: (issues.project_id = projects.id)
  • Heap Fetches: 4,810
  • Buffers: shared hit=111,218
21. 803.000 803.000 ↓ 1.5 3 160,600

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.62 rows=2 width=8) (actual time=0.004..0.005 rows=3 loops=160,600)

  • Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
  • Heap Fetches: 8,353
  • Buffers: shared hit=848,172
22. 1,668.414 1,668.414 ↓ 0.0 0 556,138

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=556,138)

  • 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=2,225,007
23. 61.874 61.874 ↑ 1.0 1 30,937

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.002..0.002 rows=1 loops=30,937)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=123,748
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. 1.401 1.401 ↓ 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.016..1.401 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