explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hodZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.282 5,241.103 ↓ 53.0 53 1

Sort (cost=5,369.09..5,369.09 rows=1 width=132) (actual time=5,241.101..5,241.103 rows=53 loops=1)

  • Sort Key: q_assignee.assignee_count, q_assignee.assignee_concat, statuses.orderindex, items.priority, items.assignee_orderindex
  • Sort Method: quicksort Memory: 38kB
2. 0.200 5,240.821 ↓ 53.0 53 1

Nested Loop Left Join (cost=302.77..5,369.08 rows=1 width=132) (actual time=15.776..5,240.821 rows=53 loops=1)

3. 0.159 5,238.024 ↓ 53.0 53 1

Nested Loop (cost=302.08..5,364.98 rows=1 width=60) (actual time=15.713..5,238.024 rows=53 loops=1)

4. 0.195 5,237.123 ↓ 53.0 53 1

Nested Loop (cost=301.79..5,364.63 rows=1 width=76) (actual time=15.686..5,237.123 rows=53 loops=1)

  • Join Filter: ((project_members.userid IS NOT NULL) OR ((team_members.role <> 4) AND (NOT projects.private)))
5. 0.137 5,236.292 ↓ 53.0 53 1

Nested Loop Left Join (cost=301.51..5,362.11 rows=1 width=81) (actual time=15.673..5,236.292 rows=53 loops=1)

6. 159.278 5,235.837 ↓ 53.0 53 1

Nested Loop (cost=301.23..5,361.16 rows=1 width=85) (actual time=15.653..5,235.837 rows=53 loops=1)

  • Join Filter: (items.id = cf_daf46ad2_9aba_494d_b876_ff588d7bdedd.item_id)
  • Rows Removed by Join Filter: 1058781
7. 1.279 1.279 ↓ 53.0 53 1

Index Scan using field_values_pkey on field_values cf_daf46ad2_9aba_494d_b876_ff588d7bdedd (cost=0.29..248.52 rows=1 width=46) (actual time=0.099..1.279 rows=53 loops=1)

  • Index Cond: (field_id = 'daf46ad2-9aba-494d-b876-ff588d7bdedd'::uuid)
  • Filter: (((value ->> 'value'::text))::uuid = '6477c66f-2c62-4593-8fa6-93ee64a6ad27'::uuid)
8. 340.246 5,075.280 ↓ 4.8 19,978 53

Hash Left Join (cost=300.94..5,060.38 rows=4,181 width=85) (actual time=0.136..95.760 rows=19,978 loops=53)

  • Hash Cond: (COALESCE(items.parent, items.id) = task_members.task_id)
  • Filter: ((task_members.userid IS NOT NULL) OR ((NOT items.private) AND (items.parent IS NULL)) OR (NOT parent.private))
  • Rows Removed by Filter: 17
9. 523.693 4,735.020 ↓ 4.8 19,995 53

Nested Loop Left Join (cost=298.37..5,031.53 rows=4,181 width=91) (actual time=0.135..89.340 rows=19,995 loops=53)

  • Filter: ((NOT parent.archived) OR (parent.archived IS NULL))
  • Rows Removed by Filter: 15
10. 141.563 2,090.267 ↓ 4.8 20,010 53

Nested Loop (cost=298.37..2,011.37 rows=4,184 width=90) (actual time=0.133..39.439 rows=20,010 loops=53)

11. 11.194 16.695 ↓ 1.5 419 53

Hash Right Join (cost=298.37..309.65 rows=282 width=35) (actual time=0.126..0.315 rows=419 loops=53)

  • Hash Cond: (subcategory_members.subcategory = subcategories.id)
  • Filter: ((subcategory_members.userid IS NOT NULL) OR (NOT subcategories.private))
  • Rows Removed by Filter: 6
12. 3.922 3.922 ↑ 1.1 330 53

Index Only Scan using subcategory_members_userid_subcategory on subcategory_members (cost=0.28..10.11 rows=373 width=12) (actual time=0.015..0.074 rows=330 loops=53)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 9487
13. 0.157 1.579 ↓ 1.5 425 1

Hash (cost=294.57..294.57 rows=282 width=36) (actual time=1.579..1.579 rows=425 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
14. 0.099 1.422 ↓ 1.5 425 1

Nested Loop (cost=4.49..294.57 rows=282 width=36) (actual time=0.076..1.422 rows=425 loops=1)

15. 0.045 0.501 ↓ 1.1 137 1

Hash Left Join (cost=4.20..239.43 rows=129 width=25) (actual time=0.070..0.501 rows=137 loops=1)

  • Hash Cond: (categories.id = category_members.category)
  • Filter: ((category_members.userid IS NOT NULL) OR (NOT categories.private))
  • Rows Removed by Filter: 6
16. 0.041 0.430 ↓ 1.1 143 1

Nested Loop (cost=2.20..233.71 rows=129 width=26) (actual time=0.035..0.430 rows=143 loops=1)

17. 0.100 0.119 ↑ 2.2 30 1

Bitmap Heap Scan on projects (cost=1.91..68.15 rows=65 width=17) (actual time=0.027..0.119 rows=30 loops=1)

  • Recheck Cond: (team = 333)
  • Filter: ((NOT template) AND (NOT deleted) AND ((importing IS NULL) OR (NOT importing)) AND (NOT archived))
  • Rows Removed by Filter: 39
  • Heap Blocks: exact=39
18. 0.019 0.019 ↑ 1.0 69 1

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

  • Index Cond: (team = 333)
19. 0.270 0.270 ↓ 2.5 5 30

Index Scan using categories_project_idx on categories (cost=0.29..2.53 rows=2 width=17) (actual time=0.003..0.009 rows=5 loops=30)

  • Index Cond: (project_id = projects.id)
  • Filter: ((NOT template) AND (NOT deleted) AND ((importing IS NULL) OR (NOT importing)) AND (NOT archived))
  • Rows Removed by Filter: 3
20. 0.006 0.026 ↑ 1.1 20 1

Hash (cost=1.74..1.74 rows=21 width=12) (actual time=0.026..0.026 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.020 0.020 ↑ 1.1 20 1

Index Only Scan using category_members_userid_category on category_members (cost=0.28..1.74 rows=21 width=12) (actual time=0.009..0.020 rows=20 loops=1)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 20
22. 0.822 0.822 ↓ 1.5 3 137

Index Scan using subcategories_cat_idx on subcategories (cost=0.29..0.41 rows=2 width=27) (actual time=0.003..0.006 rows=3 loops=137)

  • Index Cond: (category = categories.id)
  • Filter: ((NOT template) AND (NOT deleted) AND ((importing IS NULL) OR (NOT importing)) AND (NOT archived))
  • Rows Removed by Filter: 2
23. 111.035 1,932.009 ↓ 1.1 48 22,207

Append (cost=0.00..5.58 rows=45 width=72) (actual time=0.006..0.087 rows=48 loops=22,207)

24. 0.000 0.000 ↓ 0.0 0 22,207

Seq Scan on items (cost=0.00..0.00 rows=1 width=464) (actual time=0.000..0.000 rows=0 loops=22,207)

  • Filter: ((NOT template) AND (NOT deleted) AND (NOT importing) AND (NOT archived) AND (subcategories.id = subcategory))
25. 1,820.974 1,820.974 ↓ 1.1 48 22,207

Index Scan using tasks_subategory_index on tasks (cost=0.42..5.58 rows=44 width=63) (actual time=0.005..0.082 rows=48 loops=22,207)

  • Index Cond: (subcategory = subcategories.id)
  • Filter: ((NOT template) AND (NOT deleted) AND (NOT importing) AND (NOT archived))
  • Rows Removed by Filter: 0
26. 1,060.530 2,121.060 ↓ 0.0 0 1,060,530

Append (cost=0.00..0.70 rows=2 width=20) (actual time=0.002..0.002 rows=0 loops=1,060,530)

27. 0.000 0.000 ↓ 0.0 0 1,060,530

Seq Scan on items parent (cost=0.00..0.00 rows=1 width=34) (actual time=0.000..0.000 rows=0 loops=1,060,530)

  • Filter: (items.parent = id)
28. 1,060.530 1,060.530 ↓ 0.0 0 1,060,530

Index Scan using tasks_id_idx on tasks parent_1 (cost=0.42..0.70 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=1,060,530)

  • Index Cond: (items.parent = id)
29. 0.001 0.014 ↓ 2.0 6 1

Hash (cost=2.53..2.53 rows=3 width=8) (actual time=0.014..0.014 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
30. 0.013 0.013 ↓ 2.0 6 1

Index Only Scan using task_members_userid_task_id on task_members (cost=0.28..2.53 rows=3 width=8) (actual time=0.008..0.013 rows=6 loops=1)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 5
31. 0.318 0.318 ↓ 0.0 0 53

Index Only Scan using project_members_pkey on project_members (cost=0.28..0.94 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=53)

  • Index Cond: ((project_id = projects.id) AND (userid = 53577))
  • Heap Fetches: 0
32. 0.636 0.636 ↑ 1.0 1 53

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

  • Index Cond: ((team_id = 333) AND (userid = 53577))
33. 0.742 0.742 ↑ 1.0 1 53

Index Scan using status_group_idx on statuses (cost=0.29..0.35 rows=1 width=44) (actual time=0.014..0.014 rows=1 loops=53)

  • Index Cond: ((status_group = subcategories.status_group) AND (status = (items.status)::text))
  • Filter: ((type <> 'closed'::text) AND (items.status_id = id))
34. 0.212 2.597 ↑ 1.0 1 53

Subquery Scan on q_assignee (cost=0.70..4.08 rows=1 width=76) (actual time=0.049..0.049 rows=1 loops=53)

  • Filter: (q_assignee.task_id = items.id)
35. 1.219 2.385 ↑ 1.0 1 53

GroupAggregate (cost=0.70..4.07 rows=1 width=45) (actual time=0.045..0.045 rows=1 loops=53)

  • Group Key: assignees.task_id
36. 0.237 1.166 ↑ 1.0 1 53

Nested Loop (cost=0.70..4.05 rows=1 width=45) (actual time=0.020..0.022 rows=1 loops=53)

37. 0.530 0.530 ↑ 1.0 1 53

Index Only Scan using assignees_pkey on assignees (cost=0.41..1.53 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=53)

  • Index Cond: (task_id = items.id)
  • Heap Fetches: 57
38. 0.399 0.399 ↑ 1.0 1 57

Index Scan using users_pkey on users (cost=0.28..2.50 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=57)

  • Index Cond: (id = assignees.userid)
Planning time : 6.407 ms
Execution time : 5,241.476 ms