explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5pG

Settings
# exclusive inclusive rows x rows loops node
1. 0.357 51,516.636 ↓ 67.0 67 1

Sort (cost=5,746.87..5,746.88 rows=1 width=60) (actual time=51,516.631..51,516.636 rows=67 loops=1)

  • Sort Key: statuses.orderindex, items.priority, items.assignee_orderindex
  • Sort Method: quicksort Memory: 34kB
2. 0.257 51,516.279 ↓ 67.0 67 1

Nested Loop (cost=309.52..5,746.86 rows=1 width=60) (actual time=19.319..51,516.279 rows=67 loops=1)

3. 0.225 51,514.950 ↓ 67.0 67 1

Nested Loop (cost=309.23..5,746.51 rows=1 width=76) (actual time=19.300..51,514.950 rows=67 loops=1)

  • Join Filter: ((project_members.userid IS NOT NULL) OR ((team_members.role <> 4) AND (NOT projects.private)))
4. 0.203 51,513.787 ↓ 67.0 67 1

Nested Loop Left Join (cost=308.95..5,743.99 rows=1 width=81) (actual time=19.284..51,513.787 rows=67 loops=1)

5. 933.682 51,513.182 ↓ 67.0 67 1

Nested Loop (cost=308.67..5,743.06 rows=1 width=85) (actual time=19.267..51,513.182 rows=67 loops=1)

  • Join Filter: (items.id = cf_daf46ad2_9aba_494d_b876_ff588d7bdedd.item_id)
  • Rows Removed by Join Filter: 2216360
6. 1.334 1.334 ↓ 67.0 67 1

Index Scan using field_values_pkey on field_values cf_daf46ad2_9aba_494d_b876_ff588d7bdedd (cost=0.29..248.48 rows=1 width=4) (actual time=0.098..1.334 rows=67 loops=1)

  • Index Cond: (field_id = 'daf46ad2-9aba-494d-b876-ff588d7bdedd'::uuid)
  • Filter: (value = '{"value": "6477c66f-2c62-4593-8fa6-93ee64a6ad27"}'::jsonb)
7. 3,081.185 50,578.166 ↓ 7.2 33,081 67

Hash Left Join (cost=308.39..5,437.48 rows=4,568 width=85) (actual time=1.415..754.898 rows=33,081 loops=67)

  • 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. 4,133.699 47,496.970 ↓ 7.3 33,150 67

Nested Loop Left Join (cost=305.82..5,406.20 rows=4,568 width=91) (actual time=1.414..708.910 rows=33,150 loops=67)

9. 1,463.749 21,152.771 ↓ 7.3 33,150 67

Nested Loop (cost=305.82..2,125.71 rows=4,568 width=90) (actual time=1.412..315.713 rows=33,150 loops=67)

10. 228.765 261.032 ↓ 3.5 1,070 67

Hash Right Join (cost=305.82..317.10 rows=304 width=35) (actual time=1.164..3.896 rows=1,070 loops=67)

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

Index Only Scan using subcategory_members_userid_subcategory on subcategory_members (cost=0.28..10.11 rows=373 width=12) (actual time=0.018..0.446 rows=330 loops=67)

  • Index Cond: (userid = 53577)
  • Heap Fetches: 11993
12. 0.272 2.385 ↓ 3.5 1,078 1

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

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

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

14. 0.098 0.668 ↓ 2.2 299 1

Hash Left Join (cost=4.20..243.43 rows=137 width=25) (actual time=0.070..0.668 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.041 0.545 ↓ 2.2 305 1

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

16. 0.099 0.120 ↑ 1.0 64 1

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

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

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

  • Index Cond: (team = 333)
18. 0.384 0.384 ↓ 2.5 5 64

Index Scan using categories_project_idx on categories (cost=0.29..2.51 rows=2 width=17) (actual time=0.003..0.006 rows=5 loops=64)

  • Index Cond: (project_id = projects.id)
  • Filter: ((NOT template) AND (NOT deleted))
  • Rows Removed by Filter: 0
19. 0.005 0.025 ↑ 1.1 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
20. 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
21. 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
22. 1,218.730 19,427.990 ↑ 1.5 31 71,690

Append (cost=0.00..5.50 rows=45 width=72) (actual time=0.025..0.271 rows=31 loops=71,690)

23. 0.000 0.000 ↓ 0.0 0 71,690

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

  • Filter: ((NOT template) AND (NOT deleted) AND (subcategories.id = subcategory))
24. 18,209.260 18,209.260 ↑ 1.4 31 71,690

Index Scan using tasks_subategory_index on tasks (cost=0.42..5.50 rows=44 width=63) (actual time=0.023..0.254 rows=31 loops=71,690)

  • Index Cond: (subcategory = subcategories.id)
  • Filter: ((NOT template) AND (NOT deleted))
  • Rows Removed by Filter: 0
25. 6,663.150 22,210.500 ↓ 0.0 0 2,221,050

Append (cost=0.00..0.70 rows=2 width=19) (actual time=0.010..0.010 rows=0 loops=2,221,050)

26. 0.000 0.000 ↓ 0.0 0 2,221,050

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

  • Filter: (items.parent = id)
27. 15,547.350 15,547.350 ↓ 0.0 0 2,221,050

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

  • Index Cond: (items.parent = id)
28. 0.003 0.011 ↓ 2.0 6 1

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

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

  • Index Cond: (userid = 53577)
  • Heap Fetches: 5
30. 0.402 0.402 ↓ 0.0 0 67

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

  • Index Cond: ((project_id = projects.id) AND (userid = 53577))
  • Heap Fetches: 0
31. 0.938 0.938 ↑ 1.0 1 67

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

  • Index Cond: ((team_id = 333) AND (userid = 53577))
32. 1.072 1.072 ↑ 1.0 1 67

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

  • Index Cond: ((status_group = subcategories.status_group) AND (status = (items.status)::text))
  • Filter: ((type <> 'closed'::text) AND (items.status_id = id))