explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ah3d

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 6,531.533 ↓ 3.0 3 1

Limit (cost=21,366.07..21,366.07 rows=1 width=762) (actual time=6,531.530..6,531.533 rows=3 loops=1)

2. 0.016 6,531.529 ↓ 3.0 3 1

Sort (cost=21,366.07..21,366.07 rows=1 width=762) (actual time=6,531.528..6,531.529 rows=3 loops=1)

  • Sort Key: issues.updated_at DESC
  • Sort Method: quicksort Memory: 30kB
3. 0.012 6,531.513 ↓ 3.0 3 1

Nested Loop Left Join (cost=7,827.34..21,366.06 rows=1 width=762) (actual time=1,135.741..6,531.513 rows=3 loops=1)

  • Filter: (((projects.visibility_level > 0) AND ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level >= 20) OR ((project_features.issues_access_level = 10) AND (alternatives: SubPlan 6 or hashed SubPlan 7)))) OR ((projects.visibility_level = 0) AND ((project_features.issues_access_level IS NULL) OR (project_features.issues_access_level >= 10)) AND (alternatives: SubPlan 8 or hashed SubPlan 9)))
4. 0.019 6,531.462 ↓ 1.5 3 1

Nested Loop (cost=7,826.91..21,350.62 rows=2 width=770) (actual time=1,135.725..6,531.462 rows=3 loops=1)

  • Join Filter: (issues.project_id = projects.id)
5. 1.480 6,531.302 ↑ 2.7 3 1

Hash Semi Join (cost=7,826.47..21,288.71 rows=8 width=770) (actual time=1,135.687..6,531.302 rows=3 loops=1)

  • Hash Cond: (issues.project_id = rs.source_id)
6. 6,481.510 6,524.145 ↑ 1.1 2,418 1

Bitmap Heap Scan on issues (cost=1,630.60..15,085.45 rows=2,603 width=762) (actual time=37.002..6,524.145 rows=2,418 loops=1)

  • Recheck Cond: (((title)::text ~~* '%amex%'::text) OR (description ~~* '%amex%'::text))
  • Rows Removed by Index Recheck: 8333
  • Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 955795) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (hashed SubPlan 3))))
  • Rows Removed by Filter: 25
  • Heap Blocks: exact=10139
7. 0.001 33.983 ↓ 0.0 0 1

BitmapOr (cost=1,473.15..1,473.15 rows=2,646 width=0) (actual time=33.983..33.983 rows=0 loops=1)

8. 8.928 8.928 ↑ 2.1 704 1

Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..723.85 rows=1,513 width=0) (actual time=8.927..8.928 rows=704 loops=1)

  • Index Cond: ((title)::text ~~* '%amex%'::text)
9. 25.054 25.054 ↓ 9.2 10,480 1

Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..747.99 rows=1,133 width=0) (actual time=25.054..25.054 rows=10,480 loops=1)

  • Index Cond: (description ~~* '%amex%'::text)
10.          

SubPlan (for Bitmap Heap Scan)

11. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_issue_assignees_on_issue_id_and_user_id on issue_assignees (cost=0.43..3.45 rows=1 width=0) (never executed)

  • Index Cond: ((issue_id = issues.id) AND (user_id = 955795))
  • Heap Fetches: 0
12. 0.284 0.284 ↓ 2.1 117 1

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..60.47 rows=55 width=4) (actual time=0.020..0.284 rows=117 loops=1)

  • Index Cond: (user_id = 955795)
13. 0.474 8.368 ↓ 20.8 1,310 1

Nested Loop (cost=1.00..157.30 rows=63 width=4) (actual time=0.052..8.368 rows=1,310 loops=1)

14. 1.344 1.344 ↓ 20.8 1,310 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..29.16 rows=63 width=4) (actual time=0.026..1.344 rows=1,310 loops=1)

  • Index Cond: ((user_id = 955795) AND (access_level >= 20))
  • Heap Fetches: 449
15. 6.550 6.550 ↑ 1.0 1 1,310

Index Only Scan using projects_pkey on projects projects_2 (cost=0.43..2.02 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1,310)

  • Index Cond: (id = project_authorizations.project_id)
  • Heap Fetches: 274
16. 0.085 5.677 ↑ 1.2 401 1

Hash (cost=6,189.93..6,189.93 rows=475 width=8) (actual time=5.677..5.677 rows=401 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
17. 0.175 5.592 ↑ 1.2 401 1

Nested Loop (cost=1.00..6,189.93 rows=475 width=8) (actual time=0.065..5.592 rows=401 loops=1)

18. 1.006 1.006 ↑ 2.2 401 1

Index Scan using index_routes_on_path_text_pattern_ops on routes rs (cost=0.56..3.59 rows=881 width=4) (actual time=0.026..1.006 rows=401 loops=1)

  • Index Cond: (((path)::text ~>=~ 'gitlab-org/'::text) AND ((path)::text ~<~ 'gitlab-org0'::text))
  • Filter: (((path)::text ~~ 'gitlab-org/%'::text) AND ((source_type)::text = 'Project'::text))
  • Rows Removed by Filter: 38
19. 3.208 4.411 ↑ 1.0 1 401

Index Scan using projects_pkey on projects projects_1 (cost=0.43..7.01 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=401)

  • Index Cond: (id = rs.source_id)
  • Filter: ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (visibility_level = 20))
20.          

SubPlan (for Index Scan)

21. 1.203 1.203 ↑ 1.0 1 401

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_7 (cost=0.56..3.58 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=401)

  • Index Cond: ((user_id = 955795) AND (project_id = projects_1.id))
  • Heap Fetches: 87
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_8 (cost=0.56..29.01 rows=64 width=4) (never executed)

  • Index Cond: (user_id = 955795)
  • Heap Fetches: 0
23. 0.093 0.141 ↑ 1.0 1 3

Index Scan using projects_pkey on projects (cost=0.43..7.73 rows=1 width=8) (actual time=0.035..0.047 rows=1 loops=3)

  • Index Cond: (id = projects_1.id)
  • Filter: (((alternatives: SubPlan 4 or hashed SubPlan 5) OR (visibility_level = 20)) AND ((visibility_level > 0) OR ((visibility_level = 0) AND (alternatives: SubPlan 8 or hashed SubPlan 9))))
24.          

SubPlan (for Index Scan)

25. 0.048 0.048 ↑ 1.0 1 3

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..3.58 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=3)

  • Index Cond: ((user_id = 955795) AND (project_id = projects.id))
  • Heap Fetches: 0
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.56..29.01 rows=64 width=4) (never executed)

  • Index Cond: (user_id = 955795)
  • Heap Fetches: 0
27. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.56..3.58 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 955795) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_6 (cost=0.56..29.17 rows=64 width=4) (never executed)

  • Index Cond: ((user_id = 955795) AND (access_level >= 10))
  • Heap Fetches: 0
29. 0.039 0.039 ↑ 1.0 1 3

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.53 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=3)

  • Index Cond: (projects.id = project_id)
30.          

SubPlan (for Nested Loop Left Join)

31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_3 (cost=0.56..3.58 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 955795) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.56..29.17 rows=64 width=4) (never executed)

  • Index Cond: ((user_id = 955795) AND (access_level >= 10))
  • Heap Fetches: 0
Planning time : 29.404 ms
Execution time : 6,531.798 ms