explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 591N

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 162,533.725 ↑ 4.3 231 1

Limit (cost=576,007,884.67..576,007,887.17 rows=1,000 width=1,256) (actual time=162,533.596..162,533.725 rows=231 loops=1)

2.          

CTE issues

3. 1,541.233 140,471.282 ↑ 7.4 1,101,491 1

Merge Join (cost=10,784.11..575,798,141.15 rows=8,106,420 width=759) (actual time=3.893..140,471.282 rows=1,101,491 loops=1)

  • Merge Cond: (projects_1.id = issues_1.project_id)
4.          

CTE project_authorizations

5. 1.235 1.235 ↓ 1.1 1,460 1

Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.56..355.11 rows=1,368 width=12) (actual time=0.027..1.235 rows=1,460 loops=1)

  • Index Cond: (user_id = 64248)
  • Heap Fetches: 320
6. 1,264.595 32,691.973 ↑ 4.5 1,037,737 1

Nested Loop Left Join (cost=0.87..519,996,519.37 rows=4,705,472 width=4) (actual time=2.419..32,691.973 rows=1,037,737 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: 64343
7. 23,710.705 23,712.523 ↑ 4.5 1,102,080 1

Index Scan using projects_pkey on projects projects_1 (cost=0.43..331,386,864.02 rows=4,946,924 width=4) (actual time=2.404..23,712.523 rows=1,102,080 loops=1)

  • Filter: ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (visibility_level = 20))
  • Rows Removed by Filter: 7689321
8.          

SubPlan (forIndex Scan)

9. 0.000 0.000 ↓ 0.0 0

CTE Scan on project_authorizations project_authorizations_2 (cost=0.00..37.62 rows=1 width=0) (never executed)

  • Filter: ((access_level >= 10) AND (user_id = 64248) AND (project_id = projects_1.id))
10. 1.818 1.818 ↓ 730.0 1,460 1

CTE Scan on project_authorizations project_authorizations_3 (cost=0.00..34.20 rows=2 width=4) (actual time=0.029..1.818 rows=1,460 loops=1)

  • Filter: ((access_level >= 10) AND (user_id = 64248))
11. 7,714.560 7,714.560 ↑ 1.0 1 1,102,080

Index Scan using index_project_features_on_project_id on project_features (cost=0.43..0.49 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1,102,080)

  • Index Cond: (projects_1.id = project_id)
12.          

SubPlan (forNested Loop Left Join)

13. 0.000 0.000 ↓ 0.0 0

CTE Scan on project_authorizations project_authorizations_4 (cost=0.00..37.62 rows=1 width=0) (never executed)

  • Filter: ((access_level >= 10) AND (user_id = 64248) AND (project_id = projects_1.id))
14. 0.295 0.295 ↓ 730.0 1,460 1

CTE Scan on project_authorizations project_authorizations_5 (cost=0.00..34.20 rows=2 width=4) (actual time=0.005..0.295 rows=1,460 loops=1)

  • Filter: ((access_level >= 10) AND (user_id = 64248))
15. 106,227.618 106,238.076 ↓ 1.0 15,241,692 1

Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=40.07..55,815,454.87 rows=15,145,746 width=759) (actual time=0.014..106,238.076 rows=15,241,692 loops=1)

  • Filter: ((confidential IS NOT TRUE) OR (confidential AND ((author_id = 64248) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (hashed SubPlan 4))))
  • Rows Removed by Filter: 244774
16.          

SubPlan (forIndex Scan)

17. 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_1.id) AND (user_id = 64248))
  • Heap Fetches: 0
18. 1.231 1.231 ↓ 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.018..1.231 rows=465 loops=1)

  • Index Cond: (user_id = 64248)
19. 0.033 9.227 ↓ 730.0 1,460 1

Nested Loop (cost=0.43..39.63 rows=2 width=4) (actual time=0.020..9.227 rows=1,460 loops=1)

20. 0.434 0.434 ↓ 730.0 1,460 1

CTE Scan on project_authorizations project_authorizations_1 (cost=0.00..34.20 rows=2 width=4) (actual time=0.004..0.434 rows=1,460 loops=1)

  • Filter: ((access_level >= 20) AND (user_id = 64248))
21. 8.760 8.760 ↑ 1.0 1 1,460

Index Only Scan using projects_pkey on projects (cost=0.43..2.70 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1,460)

  • Index Cond: (id = project_authorizations_1.project_id)
  • Heap Fetches: 316
22. 0.349 162,533.704 ↑ 559.2 231 1

Sort (cost=209,743.52..210,066.48 rows=129,184 width=1,256) (actual time=162,533.596..162,533.704 rows=231 loops=1)

  • Sort Key: issues.id DESC
  • Sort Method: quicksort Memory: 358kB
23. 162,533.355 162,533.355 ↑ 559.2 231 1

CTE Scan on issues (cost=0.00..202,660.50 rows=129,184 width=1,256) (actual time=213.191..162,533.355 rows=231 loops=1)

  • Filter: (((title)::text ~~* '%amex%'::text) OR (description ~~* '%amex%'::text))
  • Rows Removed by Filter: 1101260
Planning time : 1.908 ms
Execution time : 162,680.532 ms