explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hlM1

Settings
# exclusive inclusive rows x rows loops node
1. 0.395 12,569.883 ↓ 68.0 68 1

Sort (cost=5,823.64..5,823.65 rows=1 width=60) (actual time=12,569.875..12,569.883 rows=68 loops=1)

  • Sort Key: statuses.orderindex, items.priority, items.assignee_orderindex
  • Sort Method: quicksort Memory: 34kB
2. 0.216 12,569.488 ↓ 68.0 68 1

Nested Loop (cost=309.65..5,823.63 rows=1 width=60) (actual time=20.003..12,569.488 rows=68 loops=1)

3. 0.270 12,568.252 ↓ 68.0 68 1

Nested Loop (cost=309.36..5,823.28 rows=1 width=76) (actual time=19.985..12,568.252 rows=68 loops=1)

  • Join Filter: ((project_members.userid IS NOT NULL) OR ((team_members.role <> 4) AND (NOT projects.private)))
4. 0.202 12,567.234 ↓ 68.0 68 1

Nested Loop Left Join (cost=309.08..5,820.76 rows=1 width=81) (actual time=19.969..12,567.234 rows=68 loops=1)

5. 357.312 12,566.760 ↓ 68.0 68 1

Nested Loop (cost=308.80..5,819.83 rows=1 width=85) (actual time=19.955..12,566.760 rows=68 loops=1)

  • Join Filter: (items.id = cf_daf46ad2_9aba_494d_b876_ff588d7bdedd.item_id)
  • Rows Removed by Join Filter: 2249576
6. 1.000 1.000 ↓ 68.0 68 1

Index Only Scan using field_values_item_id_field_id_text_value on field_values cf_daf46ad2_9aba_494d_b876_ff588d7bdedd (cost=0.41..325.25 rows=1 width=4) (actual time=0.107..1.000 rows=68 loops=1)

  • Index Cond: ((field_id = 'daf46ad2-9aba-494d-b876-ff588d7bdedd'::uuid) AND (text_value = '6477c66f-2c62-4593-8fa6-93ee64a6ad27'::text))
  • Heap Fetches: 0
7. 822.858 12,208.448 ↓ 7.2 33,083 68

Hash Left Join (cost=308.39..5,437.48 rows=4,568 width=85) (actual time=0.161..179.536 rows=33,083 loops=68)

  • 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: 69
8. 0.000 11,385.580 ↓ 7.3 33,152 68

Nested Loop Left Join (cost=305.82..5,406.20 rows=4,568 width=91) (actual time=0.160..167.435 rows=33,152 loops=68)

9. 337.824 4,821.132 ↓ 7.3 33,152 68

Nested Loop (cost=305.82..2,125.71 rows=4,568 width=90) (actual time=0.159..70.899 rows=33,152 loops=68)

10. 36.681 44.948 ↓ 3.5 1,070 68

Hash Right Join (cost=305.82..317.10 rows=304 width=35) (actual time=0.152..0.661 rows=1,070 loops=68)

  • Hash Cond: (subcategory_members.subcategory = subcategories.id)
  • Filter: ((subcategory_members.userid IS NOT NULL) OR (NOT subcategories.private))
  • Rows Removed by Filter: 8
11. 5.984 5.984 ↑ 1.1 330 68

Index Only Scan using subcategory_members_userid_subcategory on subcategory_members (cost=0.28..10.11 rows=373 width=12) (actual time=0.017..0.088 rows=330 loops=68)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 12172
12. 0.268 2.283 ↓ 3.5 1,078 1

Hash (cost=301.74..301.74 rows=304 width=36) (actual time=2.283..2.283 rows=1,078 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 77kB
13. 0.191 2.015 ↓ 3.5 1,078 1

Nested Loop (cost=4.49..301.74 rows=304 width=36) (actual time=0.071..2.015 rows=1,078 loops=1)

14. 0.077 0.628 ↓ 2.2 299 1

Hash Left Join (cost=4.20..243.43 rows=137 width=25) (actual time=0.065..0.628 rows=299 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
15. 0.427 0.532 ↓ 2.2 305 1

Nested Loop (cost=2.20..237.48 rows=137 width=26) (actual time=0.035..0.532 rows=305 loops=1)

  • -> Index Scan using categories_project_idx on categories (cost=0.29..2.51 rows=2 width=17) (actual time=0.002..0.006 rows=5 loops=64) Index Cond: (project_id = projects.id)
16. 0.085 0.105 ↑ 1.0 64 1

Bitmap Heap Scan on projects (cost=1.91..68.15 rows=67 width=17) (actual time=0.029..0.105 rows=64 loops=1)

  • Recheck Cond: (team = 333)
  • Filter: ((NOT template) AND (NOT deleted))
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=39
  • Filter: ((NOT template) AND (NOT deleted))
  • Rows Removed by Filter: 0
17. 0.020 0.020 ↑ 1.0 69 1

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

  • Index Cond: (team = 333)
18. 0.004 0.019 ↑ 1.1 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.015 0.015 ↑ 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.007..0.015 rows=20 loops=1)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 20
20. 1.196 1.196 ↓ 2.0 4 299

Index Scan using subcategories_cat_idx on subcategories (cost=0.29..0.41 rows=2 width=27) (actual time=0.002..0.004 rows=4 loops=299)

  • Index Cond: (category = categories.id)
  • Filter: ((NOT template) AND (NOT deleted))
  • Rows Removed by Filter: 0
21. 363.800 4,438.360 ↑ 1.5 31 72,760

Append (cost=0.00..5.50 rows=45 width=72) (actual time=0.006..0.061 rows=31 loops=72,760)

22. 0.000 0.000 ↓ 0.0 0 72,760

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

  • Filter: ((NOT template) AND (NOT deleted) AND (subcategories.id = subcategory))
23. 4,074.560 4,074.560 ↑ 1.4 31 72,760

Index Scan using tasks_subategory_index on tasks (cost=0.42..5.50 rows=44 width=63) (actual time=0.005..0.056 rows=31 loops=72,760)

  • Index Cond: (subcategory = subcategories.id)
  • Filter: ((NOT template) AND (NOT deleted))
  • Rows Removed by Filter: 0
24. 2,254.336 6,763.008 ↓ 0.0 0 2,254,336

Append (cost=0.00..0.70 rows=2 width=19) (actual time=0.002..0.003 rows=0 loops=2,254,336)

25. 0.000 0.000 ↓ 0.0 0 2,254,336

Seq Scan on items parent (cost=0.00..0.00 rows=1 width=33) (actual time=0.000..0.000 rows=0 loops=2,254,336)

  • Filter: (items.parent = id)
26. 4,508.672 4,508.672 ↓ 0.0 0 2,254,336

Index Scan using tasks_id_idx on tasks parent_1 (cost=0.42..0.70 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=2,254,336)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
28. 0.009 0.009 ↓ 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.005..0.009 rows=6 loops=1)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 5
29. 0.272 0.272 ↓ 0.0 0 68

Index Only Scan using project_members_pkey on project_members (cost=0.28..0.92 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=68)

  • Index Cond: ((project_id = projects.id) AND (userid = 53577))
  • Heap Fetches: 0
30. 0.748 0.748 ↑ 1.0 1 68

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

  • Index Cond: ((team_id = 333) AND (userid = 53577))
31. 1.020 1.020 ↑ 1.0 1 68

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

  • Index Cond: ((status_group = subcategories.status_group) AND (status = (items.status)::text))
  • Filter: ((type <> 'closed'::text) AND (items.status_id = id))
Planning time : 5.227 ms
Execution time : 12,570.193 ms