explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QqCS

Settings
# exclusive inclusive rows x rows loops node
1. 0.176 6,220.011 ↑ 1.0 1 1

Aggregate (cost=31,219.72..31,219.73 rows=1 width=8) (actual time=6,220.011..6,220.011 rows=1 loops=1)

2. 0.088 6,219.835 ↑ 4.3 231 1

Limit (cost=4,182.28..31,207.21 rows=1,001 width=4) (actual time=35.213..6,219.835 rows=231 loops=1)

3. 1.160 6,219.747 ↑ 6.0 231 1

Nested Loop Left Join (cost=4,182.28..41,655.40 rows=1,388 width=4) (actual time=35.213..6,219.747 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 6 or hashed SubPlan 7)))
  • Rows Removed by Filter: 11
4. 3.157 6,214.892 ↑ 6.0 242 1

Nested Loop (cost=4,181.85..35,681.66 rows=1,460 width=4) (actual time=35.194..6,214.892 rows=242 loops=1)

5. 6,129.888 6,173.143 ↑ 1.1 2,412 1

Bitmap Heap Scan on issues (cost=4,181.41..17,590.46 rows=2,594 width=4) (actual time=35.154..6,173.143 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
6. 0.002 32.136 ↓ 0.0 0 1

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

7. 7.445 7.445 ↑ 2.1 702 1

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

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

Bitmap Index Scan on index_issues_on_description_trigram (cost=0.00..669.96 rows=1,128 width=0) (actual time=24.689..24.689 rows=10,535 loops=1)

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

SubPlan (forBitmap Heap Scan)

10. 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
11. 1.285 1.285 ↓ 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.024..1.285 rows=465 loops=1)

  • Index Cond: (user_id = 64248)
12. 0.769 9.834 ↓ 1.2 1,467 1

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

13. 1.730 1.730 ↓ 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.020..1.730 rows=1,467 loops=1)

  • Index Cond: ((user_id = 64248) AND (access_level >= 20))
  • Heap Fetches: 539
14. 7.335 7.335 ↑ 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.005..0.005 rows=1 loops=1,467)

  • Index Cond: (id = project_authorizations.project_id)
  • Heap Fetches: 144
15. 26.532 38.592 ↓ 0.0 0 2,412

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

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

SubPlan (forIndex Scan)

17. 12.060 12.060 ↓ 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.005..0.005 rows=0 loops=2,412)

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

  • Index Cond: ((user_id = 64248) AND (access_level >= 10))
  • Heap Fetches: 0
19. 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)
20.          

SubPlan (forNested Loop Left Join)

21. 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
22. 1.759 1.759 ↓ 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.018..1.759 rows=1,467 loops=1)

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