explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bp94

Settings
# exclusive inclusive rows x rows loops node
1. 13.869 107.752 ↑ 101.2 28 1

GroupAggregate (cost=642.66..706.40 rows=2,833 width=36) (actual time=94.391..107.752 rows=28 loops=1)

  • Group Key: categories.id, categories.project_id
2. 14.302 93.883 ↓ 16.5 46,809 1

Sort (cost=642.66..649.74 rows=2,833 width=36) (actual time=91.209..93.883 rows=46,809 loops=1)

  • Sort Key: categories.id, categories.project_id
  • Sort Method: quicksort Memory: 5193kB
3. 24.202 79.581 ↓ 16.5 46,809 1

Hash Right Join (cost=469.96..480.21 rows=2,833 width=36) (actual time=55.377..79.581 rows=46,809 loops=1)

  • Hash Cond: (project_members.project_id = projects.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: 17626
4. 0.017 0.026 ↓ 1.3 13 1

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

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

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

  • Index Cond: (userid = 53577)
6. 20.276 55.353 ↓ 22.7 64,435 1

Hash (cost=433.09..433.09 rows=2,833 width=59) (actual time=55.353..55.353 rows=64,435 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6807kB
7. 10.363 35.077 ↓ 22.7 64,435 1

Nested Loop (cost=5.25..433.09 rows=2,833 width=59) (actual time=0.081..35.077 rows=64,435 loops=1)

8. 1.000 9.986 ↓ 22.7 1,841 1

Nested Loop Left Join (cost=3.59..360.52 rows=81 width=63) (actual time=0.067..9.986 rows=1,841 loops=1)

9. 0.526 7.145 ↓ 22.7 1,841 1

Nested Loop (cost=3.32..335.47 rows=81 width=67) (actual time=0.062..7.145 rows=1,841 loops=1)

10. 0.122 2.107 ↓ 5.2 188 1

Nested Loop Left Join (cost=3.03..320.03 rows=36 width=66) (actual time=0.057..2.107 rows=188 loops=1)

11. 0.123 1.797 ↓ 5.2 188 1

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

12. 0.334 1.290 ↓ 5.2 192 1

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

13. 0.093 0.648 ↓ 2.3 308 1

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

14. 0.100 0.114 ↑ 1.1 63 1

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

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

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

  • Index Cond: (team = 333)
16. 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
17. 0.308 0.308 ↑ 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.001 rows=1 loops=308)

  • Index Cond: ((parent_id = categories.id) AND (parent_type = 5))
18. 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: 17
19. 0.188 0.188 ↓ 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.001..0.001 rows=0 loops=188)

  • Index Cond: ((userid = 53577) AND (category = categories.id))
  • Heap Fetches: 21
20. 4.512 4.512 ↓ 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.024 rows=10 loops=188)

  • Index Cond: (category = categories.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
21. 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
22. 14.648 14.728 ↑ 1.0 35 1,841

Materialize (cost=1.65..37.23 rows=35 width=12) (actual time=0.000..0.008 rows=35 loops=1,841)

23. 0.072 0.080 ↑ 1.0 35 1

Bitmap Heap Scan on team_members (cost=1.65..37.05 rows=35 width=12) (actual time=0.013..0.080 rows=35 loops=1)

  • Recheck Cond: (team_id = 333)
  • Heap Blocks: exact=28
24. 0.008 0.008 ↑ 1.0 35 1

Bitmap Index Scan on team_members_pkey (cost=0.00..1.65 rows=35 width=0) (actual time=0.008..0.008 rows=35 loops=1)

  • Index Cond: (team_id = 333)