explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QUD5

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 6,631.045 ↑ 1.0 1 1

Aggregate (cost=50,374.52..50,374.53 rows=1 width=8) (actual time=6,631.045..6,631.045 rows=1 loops=1)

2. 0.018 6,631.028 ↑ 1.6 231 1

Limit (cost=50,368.96..50,369.89 rows=371 width=12) (actual time=6,630.985..6,631.028 rows=231 loops=1)

3. 0.340 6,631.010 ↑ 1.6 231 1

Sort (cost=50,368.96..50,369.89 rows=371 width=12) (actual time=6,630.984..6,631.010 rows=231 loops=1)

  • Sort Key: issues.updated_at DESC
  • Sort Method: quicksort Memory: 35kB
4. 3.350 6,630.670 ↑ 1.6 231 1

Nested Loop Left Join (cost=3,802.78..50,353.13 rows=371 width=12) (actual time=53.425..6,630.670 rows=231 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)))
  • Rows Removed by Filter: 11
5. 2.616 6,622.026 ↑ 3.1 242 1

Nested Loop (cost=3,802.35..44,608.14 rows=744 width=16) (actual time=53.344..6,622.026 rows=242 loops=1)

6. 6,476.185 6,561.522 ↑ 1.1 2,412 1

Bitmap Heap Scan on issues (cost=3,801.91..17,210.96 rows=2,594 width=12) (actual time=53.179..6,561.522 rows=2,412 loops=1)

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

BitmapOr (cost=774.08..774.08 rows=2,637 width=0) (actual time=49.316..49.316 rows=0 loops=1)

8. 13.595 13.595 ↑ 2.1 702 1

Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..404.32 rows=1,509 width=0) (actual time=13.595..13.595 rows=702 loops=1)

  • Index Cond: ((title)::text ~~* '%amex%'::text)
9. 35.719 35.719 ↓ 9.3 10,535 1

Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..368.46 rows=1,128 width=0) (actual time=35.719..35.719 rows=10,535 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 = 64248))
  • Heap Fetches: 0
12. 8.221 8.221 ↓ 8.5 465 1

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..60.52 rows=55 width=4) (actual time=0.085..8.221 rows=465 loops=1)

  • Index Cond: (user_id = 64248)
13. 0.067 27.800 ↓ 1.2 1,467 1

Nested Loop (cost=1.00..3,024.72 rows=1,247 width=4) (actual time=0.116..27.800 rows=1,467 loops=1)

14. 8.662 8.662 ↓ 1.2 1,467 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..548.98 rows=1,247 width=4) (actual time=0.060..8.662 rows=1,467 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 20))
  • Heap Fetches: 539
15. 19.071 19.071 ↑ 1.0 1 1,467

Index Only Scan using projects_pkey on projects projects_1 (cost=0.43..1.98 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1,467)

  • Index Cond: (id = project_authorizations.project_id)
  • Heap Fetches: 128
16. 41.660 57.888 ↓ 0.0 0 2,412

Index Scan using projects_pkey on projects (cost=0.43..10.55 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=2,412)

  • Index Cond: (id = issues.project_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))))
  • Rows Removed by Filter: 1
17.          

SubPlan (for Index Scan)

18. 14.472 14.472 ↓ 0.0 0 2,412

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.006..0.006 rows=0 loops=2,412)

  • Index Cond: ((user_id = 64248) AND (project_id = projects.id))
  • Heap Fetches: 1
19. 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..559.46 rows=1,279 width=4) (never executed)

  • Index Cond: (user_id = 64248)
  • Heap Fetches: 0
20. 0.005 0.005 ↑ 1.0 1 1

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) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((user_id = 64248) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
21. 1.751 1.751 ↓ 1.1 1,467 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_6 (cost=0.56..562.66 rows=1,279 width=4) (actual time=0.031..1.751 rows=1,467 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 539
22. 3.630 3.630 ↑ 1.0 1 242

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

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

SubPlan (for Nested Loop Left Join)

24. 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 = 64248) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
25. 1.664 1.664 ↓ 1.1 1,467 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_4 (cost=0.56..562.66 rows=1,279 width=4) (actual time=0.023..1.664 rows=1,467 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 539
Planning time : 26.818 ms
Execution time : 6,631.679 ms