explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nTDs

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,856,264.858 ↓ 0.0 0 1

HashAggregate (cost=194,018.46..194,018.49 rows=2 width=350) (actual time=1,856,264.858..1,856,264.858 rows=0 loops=1)

  • Group Key: assignees.userid, users.username, users.email, users.color, users.profile_picture_key
2.          

CTE c0

3. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.70..2,940.93 rows=1 width=51) (never executed)

4. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..2,939.48 rows=1 width=73) (never executed)

5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..2,704.86 rows=174 width=21) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..22.96 rows=17 width=24) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Index Only Scan using category_fields_pkey on category_fields (cost=0.42..2.65 rows=2 width=24) (never executed)

  • Index Cond: (field_id = '9424865a-c82f-409b-96ae-4c8423d7c1e8'::uuid)
  • Heap Fetches: 0
8. 0.000 0.000 ↓ 0.0 0

Index Scan using subcategories_cat_idx on subcategories subcategories_1 (cost=0.43..10.07 rows=8 width=16) (never executed)

  • Index Cond: (category = category_fields.category_id)
9. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..154.57 rows=319 width=13) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on items items_1 (cost=0.00..0.00 rows=1 width=40) (never executed)

  • Filter: (subcategories_1.id = subcategory)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using tasks_subcategory_idx on tasks tasks_1 (cost=0.56..154.57 rows=318 width=13) (never executed)

  • Index Cond: (subcategory = subcategories_1.id)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using field_values_pkey on field_values (cost=0.43..1.34 rows=1 width=73) (never executed)

  • Index Cond: ((item_id = items_1.id) AND (field_id = '9424865a-c82f-409b-96ae-4c8423d7c1e8'::uuid))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using drop_down_options_pkey on drop_down_options (cost=0.43..1.45 rows=1 width=46) (never executed)

  • Index Cond: (id = ((field_values.value ->> 'value'::text))::uuid)
14.          

CTE c1

15. 31.325 472.008 ↓ 4,050.0 4,050 1

Nested Loop (cost=1.70..2,941.03 rows=1 width=51) (actual time=144.956..472.008 rows=4,050 loops=1)

16. 25.545 396.133 ↓ 4,050.0 4,050 1

Nested Loop (cost=1.28..2,939.48 rows=1 width=73) (actual time=144.445..396.133 rows=4,050 loops=1)

17. 9.153 198.466 ↓ 164.9 28,687 1

Nested Loop (cost=0.85..2,704.86 rows=174 width=21) (actual time=0.953..198.466 rows=28,687 loops=1)

18. 2.317 14.233 ↓ 343.3 5,836 1

Nested Loop (cost=0.85..22.96 rows=17 width=24) (actual time=0.536..14.233 rows=5,836 loops=1)

19. 1.641 1.641 ↓ 342.5 685 1

Index Only Scan using category_fields_pkey on category_fields category_fields_1 (cost=0.42..2.65 rows=2 width=24) (actual time=0.501..1.641 rows=685 loops=1)

  • Index Cond: (field_id = '8aed6701-b2b5-499a-afdb-594e254e1a01'::uuid)
  • Heap Fetches: 733
20. 10.275 10.275 ↓ 1.1 9 685

Index Scan using subcategories_cat_idx on subcategories subcategories_2 (cost=0.43..10.07 rows=8 width=16) (actual time=0.007..0.015 rows=9 loops=685)

  • Index Cond: (category = category_fields_1.category_id)
21. 5.836 175.080 ↑ 63.8 5 5,836

Append (cost=0.00..154.57 rows=319 width=13) (actual time=0.010..0.030 rows=5 loops=5,836)

22. 0.000 0.000 ↓ 0.0 0 5,836

Seq Scan on items items_2 (cost=0.00..0.00 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=5,836)

  • Filter: (subcategories_2.id = subcategory)
23. 169.244 169.244 ↑ 63.6 5 5,836

Index Scan using tasks_subcategory_idx on tasks tasks_2 (cost=0.56..154.57 rows=318 width=13) (actual time=0.009..0.029 rows=5 loops=5,836)

  • Index Cond: (subcategory = subcategories_2.id)
24. 172.122 172.122 ↓ 0.0 0 28,687

Index Scan using field_values_pkey on field_values field_values_1 (cost=0.43..1.34 rows=1 width=73) (actual time=0.006..0.006 rows=0 loops=28,687)

  • Index Cond: ((item_id = items_2.id) AND (field_id = '8aed6701-b2b5-499a-afdb-594e254e1a01'::uuid))
25. 44.550 44.550 ↑ 1.0 1 4,050

Index Scan using drop_down_options_pkey on drop_down_options drop_down_options_1 (cost=0.43..1.54 rows=1 width=46) (actual time=0.009..0.011 rows=1 loops=4,050)

  • Index Cond: (id = ((field_values_1.value ->> 'value'::text))::uuid)
26.          

CTE c2

27. 19.955 862.643 ↓ 10,604.0 10,604 1

Nested Loop (cost=1.70..2,940.95 rows=1 width=51) (actual time=0.935..862.643 rows=10,604 loops=1)

28. 11.083 757.856 ↓ 10,604.0 10,604 1

Nested Loop (cost=1.28..2,939.48 rows=1 width=73) (actual time=0.099..757.856 rows=10,604 loops=1)

29. 3.786 318.298 ↓ 164.2 28,565 1

Nested Loop (cost=0.85..2,704.86 rows=174 width=21) (actual time=0.064..318.298 rows=28,565 loops=1)

30. 1.561 12.652 ↓ 341.5 5,805 1

Nested Loop (cost=0.85..22.96 rows=17 width=24) (actual time=0.050..12.652 rows=5,805 loops=1)

31. 0.831 0.831 ↓ 342.0 684 1

Index Only Scan using category_fields_pkey on category_fields category_fields_2 (cost=0.42..2.65 rows=2 width=24) (actual time=0.043..0.831 rows=684 loops=1)

  • Index Cond: (field_id = 'bf1f56fb-cca7-47ff-ad68-060a6b9022ae'::uuid)
  • Heap Fetches: 733
32. 10.260 10.260 ↑ 1.0 8 684

Index Scan using subcategories_cat_idx on subcategories subcategories_3 (cost=0.43..10.07 rows=8 width=16) (actual time=0.006..0.015 rows=8 loops=684)

  • Index Cond: (category = category_fields_2.category_id)
33. 5.805 301.860 ↑ 63.8 5 5,805

Append (cost=0.00..154.57 rows=319 width=13) (actual time=0.033..0.052 rows=5 loops=5,805)

34. 0.000 0.000 ↓ 0.0 0 5,805

Seq Scan on items items_3 (cost=0.00..0.00 rows=1 width=40) (actual time=0.000..0.000 rows=0 loops=5,805)

  • Filter: (subcategories_3.id = subcategory)
35. 296.055 296.055 ↑ 63.6 5 5,805

Index Scan using tasks_subcategory_idx on tasks tasks_3 (cost=0.56..154.57 rows=318 width=13) (actual time=0.032..0.051 rows=5 loops=5,805)

  • Index Cond: (subcategory = subcategories_3.id)
36. 428.475 428.475 ↓ 0.0 0 28,565

Index Scan using field_values_pkey on field_values field_values_2 (cost=0.43..1.34 rows=1 width=73) (actual time=0.015..0.015 rows=0 loops=28,565)

  • Index Cond: ((item_id = items_3.id) AND (field_id = 'bf1f56fb-cca7-47ff-ad68-060a6b9022ae'::uuid))
37. 84.832 84.832 ↑ 1.0 1 10,604

Index Scan using drop_down_options_pkey on drop_down_options drop_down_options_2 (cost=0.43..1.47 rows=1 width=46) (actual time=0.008..0.008 rows=1 loops=10,604)

  • Index Cond: (id = ((field_values_2.value ->> 'value'::text))::uuid)
38. 0.000 1,856,264.856 ↓ 0.0 0 1

Nested Loop (cost=3.10..185,195.52 rows=2 width=350) (actual time=1,856,264.856..1,856,264.856 rows=0 loops=1)

  • Join Filter: (items.id = c0.item_id)
39. 0.000 1,856,264.856 ↓ 0.0 0 1

Nested Loop (cost=3.10..185,195.49 rows=1 width=383) (actual time=1,856,264.856..1,856,264.856 rows=0 loops=1)

40. 9.811 1,856,264.856 ↓ 0.0 0 1

Nested Loop (cost=2.68..185,193.28 rows=1 width=307) (actual time=1,856,264.856..1,856,264.856 rows=0 loops=1)

41. 6,135.732 1,856,190.245 ↓ 4,050.0 4,050 1

Nested Loop (cost=2.12..185,191.74 rows=1 width=298) (actual time=1,950.465..1,856,190.245 rows=4,050 loops=1)

  • Join Filter: (items.id = c2.item_id)
  • Rows Removed by Join Filter: 42942150
42. 19,932.858 1,845,068.963 ↓ 4,050.0 4,050 1

Nested Loop (cost=2.12..185,191.71 rows=1 width=230) (actual time=1,868.999..1,845,068.963 rows=4,050 loops=1)

  • Join Filter: (items.id = c1.item_id)
  • Rows Removed by Join Filter: 114736500
43. 485.555 485.555 ↓ 4,050.0 4,050 1

CTE Scan on c1 (cost=0.00..0.02 rows=1 width=68) (actual time=144.959..485.555 rows=4,050 loops=1)

44. 83,069.550 1,824,650.550 ↓ 2.6 28,331 4,050

Nested Loop Left Join (cost=2.12..185,054.53 rows=10,973 width=162) (actual time=0.180..450.531 rows=28,331 loops=4,050)

45. 84,354.574 1,282,618.800 ↓ 2.6 28,331 4,050

Nested Loop (cost=2.12..159,055.86 rows=10,973 width=127) (actual time=0.172..316.696 rows=28,331 loops=4,050)

  • Join Filter: ((alternatives: SubPlan 4 or hashed SubPlan 5) OR ((((NOT items.private) AND (items.parent IS NULL)) OR (NOT parent.private)) AND ((alternatives: SubPlan 6 or hashed SubPlan 7) OR ((NOT subcategories.private) AND ((alternatives: SubPlan 8 or hashed SubPlan 9) OR ((NOT categories.private) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR ((team_members.role <> 4) AND (NOT projects.private)))))))))
46. 52.650 52.650 ↑ 1.0 1 4,050

Index Scan using team_member_user_idx on team_members (cost=0.42..2.64 rows=1 width=12) (actual time=0.011..0.013 rows=1 loops=4,050)

  • Index Cond: (userid = 1440488)
  • Filter: (team_id = 1264689::bigint)
47. 8,322.750 1,198,002.150 ↓ 2.5 28,331 4,050

Nested Loop Left Join (cost=1.70..53,377.49 rows=11,159 width=140) (actual time=0.106..295.803 rows=28,331 loops=4,050)

48. 61,572.150 615,976.650 ↓ 2.5 28,331 4,050

Nested Loop (cost=1.70..26,938.12 rows=11,159 width=139) (actual time=0.091..152.093 rows=28,331 loops=4,050)

49. 31,448.250 273,852.900 ↓ 2.8 17,318 4,050

Nested Loop (cost=1.70..11,831.27 rows=6,139 width=95) (actual time=0.040..67.618 rows=17,318 loops=4,050)

50. 6,629.850 56,137.050 ↓ 5.9 5,749 4,050

Nested Loop (cost=1.27..5,368.08 rows=980 width=92) (actual time=0.028..13.861 rows=5,749 loops=4,050)

51. 1,024.650 11,064.600 ↓ 1.5 678 4,050

Nested Loop (cost=0.84..1,753.29 rows=439 width=57) (actual time=0.021..2.732 rows=678 loops=4,050)

52. 2,061.450 2,061.450 ↓ 1.1 197 4,050

Index Scan using projects_team_idx on projects (cost=0.42..188.87 rows=176 width=32) (actual time=0.013..0.509 rows=197 loops=4,050)

  • Index Cond: (team = 1264689::bigint)
  • Filter: ((NOT deleted) AND (NOT template))
  • Rows Removed by Filter: 5
53. 7,978.500 7,978.500 ↑ 2.0 3 797,850

Index Scan using categories_project_idx on categories (cost=0.42..8.83 rows=6 width=33) (actual time=0.007..0.010 rows=3 loops=797,850)

  • Index Cond: (project_id = projects.id)
  • Filter: ((NOT deleted) AND (NOT template) AND ((NOT importing) OR (importing IS NULL)))
  • Rows Removed by Filter: 0
54. 38,442.600 38,442.600 ↓ 1.1 8 2,745,900

Index Scan using subcategories_cat_idx on subcategories (cost=0.43..8.16 rows=7 width=43) (actual time=0.005..0.014 rows=8 loops=2,745,900)

  • Index Cond: (category = categories.id)
  • Filter: ((NOT deleted) AND (NOT template) AND ((NOT importing) OR (importing IS NULL)))
  • Rows Removed by Filter: 0
55. 186,267.600 186,267.600 ↑ 2.0 3 23,283,450

Index Scan using status_group_idx2 on statuses (cost=0.43..6.54 rows=6 width=27) (actual time=0.008..0.008 rows=3 loops=23,283,450)

  • Index Cond: (status_group = subcategories.status_group)
56. 70,137.900 280,551.600 ↑ 1.0 2 70,137,900

Append (cost=0.00..2.44 rows=2 width=220) (actual time=0.003..0.004 rows=2 loops=70,137,900)

57. 0.000 0.000 ↓ 0.0 0 70,137,900

Seq Scan on items (cost=0.00..0.00 rows=1 width=379) (actual time=0.000..0.000 rows=0 loops=70,137,900)

  • Filter: ((NOT deleted) AND (NOT template) AND ((NOT importing) OR (importing IS NULL)) AND (subcategories.id = subcategory) AND (statuses.status = (status)::text))
58. 210,413.700 210,413.700 ↓ 2.0 2 70,137,900

Index Scan using tasks_subcategory_status_idx on tasks (cost=0.56..2.44 rows=1 width=60) (actual time=0.002..0.003 rows=2 loops=70,137,900)

  • Index Cond: ((subcategory = subcategories.id) AND ((status)::text = statuses.status))
  • Filter: ((NOT deleted) AND (NOT template) AND ((NOT importing) OR (importing IS NULL)))
  • Rows Removed by Filter: 0
59. 114,740.550 573,702.750 ↑ 2.0 1 114,740,550

Append (cost=0.00..2.35 rows=2 width=20) (actual time=0.005..0.005 rows=1 loops=114,740,550)

60. 0.000 0.000 ↓ 0.0 0 114,740,550

Seq Scan on items parent (cost=0.00..0.00 rows=1 width=33) (actual time=0.000..0.000 rows=0 loops=114,740,550)

  • Filter: (items.parent = id)
61. 458,962.200 458,962.200 ↑ 1.0 1 114,740,550

Index Scan using tasks_id_idx on tasks parent_1 (cost=0.56..2.35 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=114,740,550)

  • Index Cond: (items.parent = id)
62.          

SubPlan (forNested Loop)

63. 0.000 0.000 ↓ 0.0 0

Index Only Scan using task_members_pkey on task_members (cost=0.42..2.64 rows=1 width=0) (never executed)

  • Index Cond: ((task_id = COALESCE(items.parent, items.id)) AND (userid = 1440488))
  • Heap Fetches: 0
64. 5.763 5.763 ↓ 0.0 0 1

Index Only Scan using task_members_pkey on task_members task_members_1 (cost=0.42..1,021.93 rows=7 width=5) (actual time=5.763..5.763 rows=0 loops=1)

  • Index Cond: (userid = 1440488)
  • Heap Fetches: 0
65. 0.000 0.000 ↓ 0.0 0

Index Only Scan using subcategory_members_pkey on subcategory_members (cost=0.42..2.64 rows=1 width=0) (never executed)

  • Index Cond: ((subcategory = subcategories.id) AND (userid = 1440488))
  • Heap Fetches: 0
66. 192.724 192.724 ↓ 0.0 0 1

Index Only Scan using subcategory_members_pkey on subcategory_members subcategory_members_1 (cost=0.42..2,183.13 rows=10 width=8) (actual time=192.724..192.724 rows=0 loops=1)

  • Index Cond: (userid = 1440488)
  • Heap Fetches: 0
67. 0.000 0.000 ↓ 0.0 0

Index Only Scan using category_members_pkey on category_members (cost=0.42..2.64 rows=1 width=0) (never executed)

  • Index Cond: ((category = categories.id) AND (userid = 1440488))
  • Heap Fetches: 0
68. 8.678 8.678 ↓ 10.0 60 1

Index Only Scan using category_members_pkey on category_members category_members_1 (cost=0.42..1,707.21 rows=6 width=8) (actual time=6.596..8.678 rows=60 loops=1)

  • Index Cond: (userid = 1440488)
  • Heap Fetches: 60
69. 0.000 0.000 ↓ 0.0 0

Index Only Scan using project_members_pkey on project_members (cost=0.42..1.54 rows=1 width=0) (never executed)

  • Index Cond: ((project_id = projects.id) AND (userid = 1440488))
  • Heap Fetches: 0
70. 2.261 2.261 ↓ 50.0 200 1

Index Scan using project_members_userid on project_members project_members_1 (cost=0.42..5.88 rows=4 width=8) (actual time=0.870..2.261 rows=200 loops=1)

  • Index Cond: (userid = 1440488)
71. 114,740.550 458,962.200 ↑ 2.0 1 114,740,550

Append (cost=0.00..2.35 rows=2 width=52) (actual time=0.004..0.004 rows=1 loops=114,740,550)

72. 0.000 0.000 ↓ 0.0 0 114,740,550

Seq Scan on items parents (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=114,740,550)

  • Filter: (id = items.parent)
73. 344,221.650 344,221.650 ↑ 1.0 1 114,740,550

Index Scan using tasks_id_idx on tasks parents_1 (cost=0.56..2.35 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=114,740,550)

  • Index Cond: (id = items.parent)
74. 4,985.550 4,985.550 ↓ 10,604.0 10,604 4,050

CTE Scan on c2 (cost=0.00..0.02 rows=1 width=68) (actual time=0.001..1.231 rows=10,604 loops=4,050)

75. 64.800 64.800 ↓ 0.0 0 4,050

Index Only Scan using assignees_pkey on assignees (cost=0.56..1.51 rows=2 width=9) (actual time=0.016..0.016 rows=0 loops=4,050)

  • Index Cond: (task_id = items.id)
  • Heap Fetches: 0
76. 0.000 0.000 ↓ 0.0 0

Index Scan using users_pkey on users (cost=0.42..2.20 rows=1 width=80) (never executed)

  • Index Cond: (id = assignees.userid)
77. 0.000 0.000 ↓ 0.0 0

CTE Scan on c0 (cost=0.00..0.02 rows=1 width=68) (never executed)