explain.depesz.com

PostgreSQL's explain analyze made readable

Result: txzC

Settings
# exclusive inclusive rows x rows loops node
1. 1.947 16.377 ↑ 2.9 28 1

GroupAggregate (cost=378.36..380.18 rows=81 width=36) (actual time=14.311..16.377 rows=28 loops=1)

  • Group Key: categories.id, categories.project_id
2. 0.847 14.430 ↓ 19.6 1,589 1

Sort (cost=378.36..378.56 rows=81 width=36) (actual time=14.133..14.430 rows=1,589 loops=1)

  • Sort Key: categories.id, categories.project_id
  • Sort Method: quicksort Memory: 173kB
3. 1.200 13.583 ↓ 19.6 1,589 1

Hash Left Join (cost=15.06..375.79 rows=81 width=36) (actual time=0.117..13.583 rows=1,589 loops=1)

  • Hash Cond: (projects.id = project_members.project_id)
  • Filter: ((subcategory_members.userid IS NOT NULL) OR ((NOT subcategories.private) AND ((category_members.userid IS NOT NULL) OR ((NOT categories.private) AND ((project_members.userid IS NOT NULL) OR ((team_members.role <> 4) AND (NOT projects.private)))))))
  • Rows Removed by Filter: 252
4. 1.643 12.349 ↓ 22.7 1,841 1

Nested Loop Left Join (cost=3.88..363.38 rows=81 width=59) (actual time=0.074..12.349 rows=1,841 loops=1)

5. 0.549 8.865 ↓ 22.7 1,841 1

Nested Loop (cost=3.60..338.33 rows=81 width=63) (actual time=0.068..8.865 rows=1,841 loops=1)

6. 0.000 6.624 ↓ 5.2 188 1

Nested Loop Left Join (cost=3.31..322.89 rows=36 width=62) (actual time=0.063..6.624 rows=188 loops=1)

7. 0.030 6.256 ↓ 5.2 188 1

Nested Loop (cost=3.04..311.85 rows=36 width=58) (actual time=0.057..6.256 rows=188 loops=1)

8. 0.013 0.013 ↑ 1.0 1 1

Index Scan using team_members_pkey on team_members (cost=0.28..2.50 rows=1 width=12) (actual time=0.011..0.013 rows=1 loops=1)

  • Index Cond: ((team_id = 333) AND (userid = 53577))
9. 0.107 6.213 ↓ 5.2 188 1

Nested Loop (cost=2.75..308.98 rows=36 width=62) (actual time=0.045..6.213 rows=188 loops=1)

10. 0.007 5.722 ↓ 5.2 192 1

Nested Loop (cost=2.48..297.01 rows=37 width=62) (actual time=0.039..5.722 rows=192 loops=1)

11. 4.547 5.099 ↓ 2.3 308 1

Nested Loop (cost=2.20..239.83 rows=135 width=34) (actual time=0.023..5.099 rows=308 loops=1)

12. 0.099 0.111 ↑ 1.1 63 1

Bitmap Heap Scan on projects (cost=1.91..68.15 rows=68 width=17) (actual time=0.018..0.111 rows=63 loops=1)

  • Recheck Cond: (team = 333)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=37
13. 0.012 0.012 ↓ 1.0 70 1

Bitmap Index Scan on projects_team_idx (cost=0.00..1.90 rows=69 width=0) (actual time=0.012..0.012 rows=70 loops=1)

  • Index Cond: (team = 333)
14. 0.441 0.441 ↓ 2.5 5 63

Index Scan using categories_project_idx on categories (cost=0.28..2.50 rows=2 width=17) (actual time=0.003..0.007 rows=5 loops=63)

  • Index Cond: (project_id = projects.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
15. 0.616 0.616 ↑ 4.0 1 308

Index Scan using field_parents_parent_parent_type on field_parents (cost=0.28..0.38 rows=4 width=28) (actual time=0.001..0.002 rows=1 loops=308)

  • Index Cond: ((parent_id = categories.id) AND (parent_type = 5))
16. 0.384 0.384 ↑ 1.0 1 192

Index Only Scan using fields_pkey on fields (cost=0.28..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=192)

  • Index Cond: (id = field_parents.field_id)
  • Heap Fetches: 18
17. 0.376 0.376 ↓ 0.0 0 188

Index Only Scan using category_members_userid_category on category_members (cost=0.28..0.30 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=188)

  • Index Cond: ((userid = 53577) AND (category = categories.id))
  • Heap Fetches: 21
18. 1.692 1.692 ↓ 5.0 10 188

Index Scan using subcategories_cat_idx on subcategories (cost=0.29..0.41 rows=2 width=17) (actual time=0.002..0.009 rows=10 loops=188)

  • Index Cond: (category = categories.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
19. 1.841 1.841 ↓ 0.0 0 1,841

Index Only 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=1,841)

  • Index Cond: ((userid = 53577) AND (subcategory = subcategories.id))
  • Heap Fetches: 1
20. 0.008 0.034 ↓ 1.3 13 1

Hash (cost=11.06..11.06 rows=10 width=12) (actual time=0.034..0.034 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.018 0.026 ↓ 1.3 13 1

Bitmap Heap Scan on project_members (cost=1.46..11.06 rows=10 width=12) (actual time=0.011..0.026 rows=13 loops=1)

  • Recheck Cond: (userid = 53577)
  • Heap Blocks: exact=10
22. 0.008 0.008 ↓ 1.3 13 1

Bitmap Index Scan on project_members_userid (cost=0.00..1.46 rows=10 width=0) (actual time=0.008..0.008 rows=13 loops=1)

  • Index Cond: (userid = 53577)