explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QKCO

Settings
# exclusive inclusive rows x rows loops node
1. 4.954 1,301.874 ↑ 1.0 2 1

Aggregate (cost=35,558.78..35,558.82 rows=2 width=10) (actual time=1,296.290..1,301.874 rows=2 loops=1)

  • Group Key: issues.state_id
  • Buffers: shared hit=1,025,081
2. 11.469 1,296.920 ↓ 7,935.7 23,807 1

Sort (cost=35,558.78..35,558.79 rows=3 width=2) (actual time=1,294.408..1,296.920 rows=23,807 loops=1)

  • Sort Key: issues.state_id
  • Sort Method: quicksort Memory: 1,884kB
  • Buffers: shared hit=1,025,081
3. 34.547 1,285.451 ↓ 7,935.7 23,807 1

Nested Loop Left Join (cost=1,420.20..35,558.76 rows=3 width=2) (actual time=12.015..1,285.451 rows=23,807 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: 85
  • Buffers: shared hit=1,025,081
4. 85.299 1,202.400 ↓ 5,973.0 23,892 1

Nested Loop Semi Join (cost=1,419.76..35,542.36 rows=4 width=6) (actual time=11.992..1,202.400 rows=23,892 loops=1)

  • Buffers: shared hit=928,548
5. 46.017 153.501 ↓ 32.5 160,600 1

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

  • Buffers: shared hit=117,585
6. 0.424 9.584 ↑ 1.7 979 1

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

  • Buffers: shared hit=6,367
7. 0.154 1.535 ↑ 1.3 125 1

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

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

  • Buffers: shared hit=627
9.          

CTE base_and_descendants

10. 0.326 1.210 ↑ 1.3 125 1

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

  • Buffers: shared hit=627
11. 0.019 0.019 ↑ 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.019 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
12. 0.095 0.865 ↓ 1.6 25 5

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

  • Buffers: shared hit=623
13. 0.020 0.020 ↓ 2.5 25 5

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

14. 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
15. 3.709 7.625 ↑ 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.061 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
16.          

SubPlan (for Index Scan)

17. 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
18. 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
19. 97.900 97.900 ↑ 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.100 rows=164 loops=979)

  • Index Cond: (issues.project_id = projects.id)
  • Heap Fetches: 4,810
  • Buffers: shared hit=111,218
20. 963.600 963.600 ↓ 0.0 0 160,600

Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.56..0.72 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=160,600)

  • Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
  • Heap Fetches: 6,596
  • 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: 3
  • Buffers: shared hit=810,963
21. 47.784 47.784 ↑ 1.0 1 23,892

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=23,892)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=95,568
22.          

SubPlan (for Nested Loop Left Join)

23. 0.720 0.720 ↑ 1.0 1 240

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

  • 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: 1
  • Buffers: shared hit=965
24. 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