explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pa2T

Settings
# exclusive inclusive rows x rows loops node
1. 0.201 150.867 ↑ 1.0 1 1

Aggregate (cost=180,683.53..180,683.54 rows=1 width=8) (actual time=150.867..150.867 rows=1 loops=1)

  • Buffers: shared hit=46,711
2. 0.298 150.666 ↓ 2.2 112 1

Nested Loop (cost=1,114.47..180,683.27 rows=51 width=16) (actual time=39.422..150.666 rows=112 loops=1)

  • Buffers: shared hit=46,711
3. 4.439 149.098 ↓ 1.6 254 1

Nested Loop (cost=1,110.87..180,125.02 rows=154 width=12) (actual time=9.575..149.098 rows=254 loops=1)

  • Buffers: shared hit=45,573
4. 2.079 97.112 ↓ 34.3 5,283 1

Nested Loop (cost=1,107.28..179,567.15 rows=154 width=4) (actual time=3.935..97.112 rows=5,283 loops=1)

  • Buffers: shared hit=24,154
5. 2.636 47.089 ↑ 1.6 922 1

Nested Loop Left Join (cost=1,106.71..21,436.60 rows=1,470 width=4) (actual time=3.904..47.089 rows=922 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 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 97
  • Buffers: shared hit=12,865
6. 0.378 23.259 ↑ 1.5 1,019 1

Nested Loop (cost=1,106.28..15,149.12 rows=1,534 width=4) (actual time=3.870..23.259 rows=1,019 loops=1)

  • Buffers: shared hit=7,069
7. 0.189 3.909 ↓ 1.0 153 1

HashAggregate (cost=1,105.84..1,107.35 rows=151 width=4) (actual time=3.810..3.909 rows=153 loops=1)

  • Group Key: namespaces.id
  • Buffers: shared hit=761
8. 3.720 3.720 ↓ 1.0 153 1

CTE Scan on base_and_descendants namespaces (cost=1,100.93..1,103.95 rows=151 width=4) (actual time=0.040..3.720 rows=153 loops=1)

  • Buffers: shared hit=761
9.          

CTE base_and_descendants

10. 0.521 3.407 ↓ 1.0 153 1

Recursive Union (cost=0.43..1,100.93 rows=151 width=357) (actual time=0.032..3.407 rows=153 loops=1)

  • Buffers: shared hit=761
11. 0.026 0.026 ↑ 1.0 1 1

Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=357) (actual time=0.025..0.026 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.234 2.860 ↓ 2.0 30 5

Nested Loop (cost=0.56..109.45 rows=15 width=357) (actual time=0.048..0.572 rows=30 loops=5)

  • Buffers: shared hit=757
13. 0.025 0.025 ↓ 3.1 31 5

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

14. 2.601 2.601 ↑ 2.0 1 153

Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..10.90 rows=2 width=357) (actual time=0.012..0.017 rows=1 loops=153)

  • 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=757
15. 13.877 18.972 ↑ 1.4 7 153

Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..92.89 rows=10 width=8) (actual time=0.025..0.124 rows=7 loops=153)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (projects.visibility_level = ANY ('{10,20}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=6,308
16.          

SubPlan (for Index Scan)

17. 5.095 5.095 ↑ 1.0 1 1,019

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.005..0.005 rows=1 loops=1,019)

  • 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: 361
  • Buffers: shared hit=4,826
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_3 (cost=0.57..127.57 rows=1,500 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
19. 17.323 17.323 ↑ 1.0 1 1,019

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.017..0.017 rows=1 loops=1,019)

  • Index Cond: (projects.id = project_features.project_id)
  • Buffers: shared hit=4,077
20.          

SubPlan (for Nested Loop Left Join)

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_4 (cost=0.57..3.59 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((project_authorizations_4.user_id = 4,156,052) AND (project_authorizations_4.project_id = projects.id) AND (project_authorizations_4.access_level >= 10))
  • Heap Fetches: 0
22. 3.871 3.871 ↓ 2.2 3,311 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on public.project_authorizations project_authorizations_5 (cost=0.57..127.57 rows=1,500 width=4) (actual time=0.029..3.871 rows=3,311 loops=1)

  • Index Cond: ((project_authorizations_5.user_id = 4,156,052) AND (project_authorizations_5.access_level >= 10))
  • Heap Fetches: 862
  • Buffers: shared hit=1,719
23. 43.927 47.944 ↑ 2.5 6 922

Index Scan using idx_issues_on_project_id_and_created_at_and_id_and_state_id on public.issues (cost=0.56..107.42 rows=15 width=8) (actual time=0.014..0.052 rows=6 loops=922)

  • Index Cond: ((issues.project_id = projects.id) AND (issues.created_at <= '2020-07-30 23:59:59.999999+00'::timestamp with time zone) AND (issues.created_at >= '2020-07-01 00:00:00+00'::timestamp with time zone))
  • Filter: ((issues.confidential IS NOT TRUE) OR (issues.confidential AND ((issues.author_id = 4,156,052) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=11,289
24.          

SubPlan (for Index Scan)

25. 2.781 2.781 ↓ 0.0 0 309

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on public.issue_assignees (cost=0.43..3.45 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=309)

  • Index Cond: ((issue_assignees.issue_id = issues.id) AND (issue_assignees.user_id = 4,156,052))
  • Heap Fetches: 0
  • Buffers: shared hit=927
26. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_issue_assignees_on_user_id on public.issue_assignees issue_assignees_1 (cost=0.43..83.87 rows=84 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (issue_assignees_1.user_id = 4,156,052)
27. 1.236 1.236 ↑ 1.0 1 309

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

  • Index Cond: ((project_authorizations.user_id = 4,156,052) AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20))
  • Heap Fetches: 71
  • Buffers: shared hit=1,325
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_1 (cost=0.57..111.99 rows=1,248 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 >= 20))
  • Heap Fetches: 0
29. 0.000 47.547 ↓ 0.0 0 5,283

Limit (cost=3.60..3.60 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=5,283)

  • Buffers: shared hit=21,419
30. 5.283 47.547 ↓ 0.0 0 5,283

Sort (cost=3.60..3.60 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=5,283)

  • Sort Key: resource_label_events.created_at
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=21,419
31. 42.264 42.264 ↓ 0.0 0 5,283

Index Scan using resource_label_events_issue_id_label_id_action on public.resource_label_events (cost=0.56..3.59 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=5,283)

  • Index Cond: ((resource_label_events.issue_id = issues.id) AND (resource_label_events.label_id = 2,526,320) AND (resource_label_events.action = 1))
  • Buffers: shared hit=21,419
32. 0.000 1.270 ↓ 0.0 0 254

Limit (cost=3.60..3.60 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=254)

  • Buffers: shared hit=1,138
33. 0.508 1.270 ↓ 0.0 0 254

Sort (cost=3.60..3.60 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=254)

  • Sort Key: resource_label_events_1.created_at
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,138
34. 0.762 0.762 ↓ 0.0 0 254

Index Scan using resource_label_events_issue_id_label_id_action on public.resource_label_events resource_label_events_1 (cost=0.56..3.59 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=254)

  • Index Cond: ((resource_label_events_1.issue_id = issues.id) AND (resource_label_events_1.label_id = 2,526,326) AND (resource_label_events_1.action = 1))
  • Buffers: shared hit=1,138