explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kio

Settings
# exclusive inclusive rows x rows loops node
1. 0.631 6,373.609 ↑ 6.1 231 1

Sort (cost=47,525.21..47,528.71 rows=1,401 width=757) (actual time=6,373.532..6,373.609 rows=231 loops=1)

  • Sort Key: issues.updated_at DESC, issues.id DESC
  • Sort Method: quicksort Memory: 358kB
2. 1.136 6,372.978 ↑ 6.1 231 1

Nested Loop Left Join (cost=335.54..47,451.99 rows=1,401 width=757) (actual time=32.273..6,372.978 rows=231 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: 11
3. 1.830 6,369.282 ↑ 6.1 242 1

Nested Loop (cost=335.10..41,457.63 rows=1,465 width=761) (actual time=32.252..6,369.282 rows=242 loops=1)

4. 6,298.381 6,328.732 ↑ 1.1 2,420 1

Bitmap Heap Scan on issues (cost=334.67..23,294.43 rows=2,604 width=757) (actual time=32.212..6,328.732 rows=2,420 loops=1)

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

BitmapOr (cost=334.67..334.67 rows=2,649 width=0) (actual time=28.418..28.418 rows=0 loops=1)

6. 4.932 4.932 ↑ 2.1 715 1

Bitmap Index Scan on index_issues_on_title_trigram (cost=0.00..62.38 rows=1,517 width=0) (actual time=4.932..4.932 rows=715 loops=1)

  • Index Cond: ((title)::text ~~* '%amex%'::text)
7. 23.484 23.484 ↓ 9.3 10,490 1

Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..270.99 rows=1,132 width=0) (actual time=23.484..23.484 rows=10,490 loops=1)

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

SubPlan (forBitmap Heap Scan)

9. 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
10. 1.303 1.303 ↑ 1.3 466 1

Index Scan using index_issue_assignees_on_user_id on issue_assignees issue_assignees_1 (cost=0.43..641.09 rows=605 width=4) (actual time=0.020..1.303 rows=466 loops=1)

  • Index Cond: (user_id = 64248)
11. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((user_id = 64248) AND (project_id = issues.project_id) AND (access_level >= 20))
  • Heap Fetches: 0
12. 0.630 0.630 ↓ 1.0 1,476 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.56..41.74 rows=1,440 width=4) (actual time=0.021..0.630 rows=1,476 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 20))
  • Heap Fetches: 89
13. 26.620 38.720 ↓ 0.0 0 2,420

Index Scan using projects_pkey on projects (cost=0.43..6.97 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=2,420)

  • Index Cond: (id = issues.project_id)
  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (visibility_level = 20))
  • Rows Removed by Filter: 1
14.          

SubPlan (forIndex Scan)

15. 12.100 12.100 ↓ 0.0 0 2,420

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_2 (cost=0.56..3.58 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=2,420)

  • Index Cond: ((user_id = 64248) AND (project_id = projects.id) AND (access_level >= 10))
  • Heap Fetches: 0
16. 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..42.12 rows=1,478 width=4) (never executed)

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 0
17. 1.936 1.936 ↑ 1.0 1 242

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

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

SubPlan (forNested Loop Left Join)

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_4 (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
20. 0.624 0.624 ↑ 1.0 1,476 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_5 (cost=0.56..42.12 rows=1,478 width=4) (actual time=0.018..0.624 rows=1,476 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 89
Planning time : 11.900 ms
Execution time : 6,373.799 ms