explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bXj2

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

Aggregate (cost=140.56..140.57 rows=1 width=0) (actual time=1,357.214..1,357.214 rows=1 loops=1)

2. 0.150 1,357.197 ↓ 13.0 13 1

Bitmap Heap Scan on team_members (cost=1.68..140.55 rows=1 width=0) (actual time=116.149..1,357.197 rows=13 loops=1)

  • Recheck Cond: (team_id = 333)
  • Filter: ((NOT deleted) AND (role = 4) AND (SubPlan 1))
  • Rows Removed by Filter: 27
  • Heap Blocks: exact=27
3. 0.016 0.016 ↑ 1.0 40 1

Bitmap Index Scan on team_members_pkey (cost=0.00..1.68 rows=40 width=0) (actual time=0.016..0.016 rows=40 loops=1)

  • Index Cond: (team_id = 333)
4.          

SubPlan (forBitmap Heap Scan)

5. 86.312 1,357.031 ↑ 4,575.0 1 13

Nested Loop Left Join (cost=1.68..3,645.95 rows=4,575 width=0) (actual time=104.387..104.387 rows=1 loops=13)

  • Filter: ((project_members.permission_level <> 1) OR (category_members.permission_level <> 1) OR (subcategory_members.permission_level <> 1) OR (task_members.permission_level <> 1))
  • Rows Removed by Filter: 23463
6. 0.685 14.079 ↓ 1.3 178 13

Nested Loop Left Join (cost=0.84..561.39 rows=137 width=16) (actual time=0.076..1.083 rows=178 loops=13)

7. 0.077 6.747 ↑ 2.2 30 13

Nested Loop Left Join (cost=0.28..351.28 rows=67 width=12) (actual time=0.066..0.519 rows=30 loops=13)

  • Join Filter: (project_members.project_id = projects.id)
  • Rows Removed by Join Filter: 50
8. 6.279 6.279 ↑ 2.2 30 13

Seq Scan on projects (cost=0.00..345.68 rows=67 width=8) (actual time=0.054..0.483 rows=30 loops=13)

  • Filter: ((NOT deleted) AND (NOT template) AND (team = 333))
  • Rows Removed by Filter: 1298
9. 0.313 0.391 ↑ 1.0 2 391

Materialize (cost=0.28..3.60 rows=2 width=12) (actual time=0.000..0.001 rows=2 loops=391)

10. 0.078 0.078 ↓ 1.5 3 13

Index Scan using project_members_userid on project_members (cost=0.28..3.59 rows=2 width=12) (actual time=0.005..0.006 rows=3 loops=13)

  • Index Cond: (userid = team_members.userid)
11. 0.853 6.647 ↓ 3.0 6 391

Nested Loop Left Join (cost=0.56..3.12 rows=2 width=20) (actual time=0.005..0.017 rows=6 loops=391)

12. 3.519 3.519 ↓ 3.0 6 391

Index Scan using categories_project_idx on categories (cost=0.29..2.50 rows=2 width=16) (actual time=0.004..0.009 rows=6 loops=391)

  • Index Cond: (project_id = projects.id)
  • Filter: ((NOT deleted) AND (NOT template))
  • Rows Removed by Filter: 0
13. 2.275 2.275 ↓ 0.0 0 2,275

Index Scan using category_members_userid_category on category_members (cost=0.28..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=2,275)

  • Index Cond: ((userid = team_members.userid) AND (category = categories.id))
14. 95.156 1,256.640 ↓ 4.4 132 2,310

Nested Loop Left Join (cost=0.84..21.91 rows=30 width=16) (actual time=0.011..0.544 rows=132 loops=2,310)

15. 36.567 552.090 ↓ 4.4 132 2,310

Nested Loop Left Join (cost=0.56..12.69 rows=30 width=16) (actual time=0.009..0.239 rows=132 loops=2,310)

16. 3.243 25.410 ↓ 2.0 4 2,310

Nested Loop Left Join (cost=0.56..1.02 rows=2 width=20) (actual time=0.004..0.011 rows=4 loops=2,310)

17. 13.860 13.860 ↓ 2.0 4 2,310

Index Scan using subcategories_cat_idx on subcategories (cost=0.29..0.40 rows=2 width=16) (actual time=0.003..0.006 rows=4 loops=2,310)

  • Index Cond: (category = categories.id)
  • Filter: ((NOT deleted) AND (NOT template))
  • Rows Removed by Filter: 0
18. 8.307 8.307 ↓ 0.0 0 8,307

Index Scan using subcategory_members_userid_subcategory on subcategory_members (cost=0.28..0.30 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=8,307)

  • Index Cond: ((userid = team_members.userid) AND (subcategory = subcategories.id))
19. 41.535 490.113 ↑ 1.2 36 8,307

Append (cost=0.00..5.38 rows=45 width=13) (actual time=0.005..0.059 rows=36 loops=8,307)

20. 0.000 0.000 ↓ 0.0 0 8,307

Seq Scan on items (cost=0.00..0.00 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=8,307)

  • Filter: ((NOT deleted) AND (NOT template) AND (subcategories.id = subcategory))
21. 448.578 448.578 ↑ 1.2 36 8,307

Index Scan using tasks_subategory_index on tasks (cost=0.42..5.38 rows=44 width=12) (actual time=0.004..0.054 rows=36 loops=8,307)

  • Index Cond: (subcategories.id = subcategory)
  • Filter: ((NOT deleted) AND (NOT template))
  • Rows Removed by Filter: 0
22. 609.394 609.394 ↓ 0.0 0 304,697

Index Scan using task_members_pkey on task_members (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=304,697)

  • Index Cond: ((task_id = items.id) AND (userid = team_members.userid))
Planning time : 2.694 ms
Execution time : 1,357.320 ms