explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bo1V

Settings
# exclusive inclusive rows x rows loops node
1. 4.565 4,851.118 ↑ 1.0 2 1

Aggregate (cost=39,665.41..39,669.22 rows=2 width=10) (actual time=4,846.148..4,851.118 rows=2 loops=1)

  • Group Key: issues.state_id
  • Buffers: shared hit=3,378,520 read=65,827
  • I/O Timings: read=1,397.564
2. 17.242 4,846.553 ↓ 47.1 23,808 1

Sort (cost=39,665.41..39,666.67 rows=506 width=2) (actual time=4,844.251..4,846.553 rows=23,808 loops=1)

  • Sort Key: issues.state_id
  • Sort Method: quicksort Memory: 1,885kB
  • Buffers: shared hit=3,378,520 read=65,827
  • I/O Timings: read=1,397.564
3. 22.049 4,829.311 ↓ 47.1 23,808 1

Nested Loop Left Join (cost=1,420.63..39,642.68 rows=506 width=2) (actual time=30.071..4,829.311 rows=23,808 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=3,378,520 read=65,827
  • I/O Timings: read=1,397.564
4. 5.172 4,734.258 ↓ 45.3 23,893 1

Nested Loop (cost=1,420.20..37,478.60 rows=528 width=6) (actual time=29.724..4,734.258 rows=23,893 loops=1)

  • Buffers: shared hit=3,281,722 read=65,806
  • I/O Timings: read=1,395.883
5. 246.085 3,060.672 ↓ 112.9 556,138 1

Nested Loop (cost=1,419.76..35,206.59 rows=4,924 width=10) (actual time=1.340..3,060.672 rows=556,138 loops=1)

  • Buffers: shared hit=1,056,775 read=65,746
  • I/O Timings: read=1,390.360
6. 44.465 244.987 ↓ 32.5 160,600 1

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

  • Buffers: shared hit=116,627 read=958
  • I/O Timings: read=83.973
7. 0.388 8.638 ↑ 1.7 979 1

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

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

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

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

  • Buffers: shared hit=627
10.          

CTE base_and_descendants

11. 0.252 1.074 ↑ 1.3 125 1

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

  • Buffers: shared hit=627
12. 0.017 0.017 ↑ 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.016..0.017 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.959 6.875 ↑ 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.055 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. 191.884 191.884 ↑ 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.196 rows=164 loops=979)

  • Index Cond: (issues.project_id = projects.id)
  • Heap Fetches: 4,810
  • Buffers: shared hit=110,260 read=958
  • I/O Timings: read=83.973
21. 2,569.600 2,569.600 ↓ 1.5 3 160,600

Index Scan using index_label_links_on_target_id_and_target_type on public.label_links (cost=0.56..0.63 rows=2 width=8) (actual time=0.010..0.016 rows=3 loops=160,600)

  • Index Cond: ((label_links.target_id = issues.id) AND ((label_links.target_type)::text = 'Issue'::text))
  • Buffers: shared hit=940,148 read=64,788
  • I/O Timings: read=1,306.387
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=4) (actual time=0.003..0.003 rows=0 loops=556,138)

  • Index Cond: (labels.id = label_links.label_id)
  • Filter: ((labels.title)::text = 'bug'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,224,947 read=60
  • I/O Timings: read=5.523
23. 71.679 71.679 ↑ 1.0 1 23,893

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

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=95,551 read=21
  • I/O Timings: read=1.681
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.325 1.325 ↓ 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.017..1.325 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