explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IZux

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 18.740 ↓ 28.0 28 1

HashAggregate (cost=7,021.89..7,021.90 rows=1 width=170) (actual time=18.729..18.740 rows=28 loops=1)

  • Group Key: items.id, items.name, items.parent, items.encrypted, subcategories.id, categories.id, projects.orderindex, categories.orderindex, subcategories.orderindex, statuses.orderindex, items.orderindex, items.subtask_orderindex, items.date_updated, items.due_date, items.due_date_time, items.date_created, items.priority, items.start_date, items.start_date_time, items.time_estimate, items.date_closed, statuses.status, statuses.type, (statuses.status = 'Open'::text)
2. 0.121 18.684 ↓ 28.0 28 1

Nested Loop (cost=4.49..7,021.83 rows=1 width=170) (actual time=3.657..18.684 rows=28 loops=1)

3. 0.201 17.383 ↓ 236.0 236 1

Nested Loop Left Join (cost=3.94..7,019.36 rows=1 width=172) (actual time=0.164..17.383 rows=236 loops=1)

  • Filter: ((task_members.userid IS NOT NULL) OR ((((NOT items.private) AND (items.parent IS NULL)) OR (NOT parent.private)) AND ((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)))))))))
4. 0.084 16.946 ↓ 236.0 236 1

Nested Loop Left Join (cost=3.52..7,017.44 rows=1 width=201) (actual time=0.156..16.946 rows=236 loops=1)

5. 0.130 16.626 ↓ 236.0 236 1

Nested Loop (cost=3.10..7,016.04 rows=1 width=197) (actual time=0.150..16.626 rows=236 loops=1)

6. 0.290 10.931 ↓ 37.1 1,113 1

Nested Loop Left Join (cost=2.54..6,969.84 rows=30 width=197) (actual time=0.077..10.931 rows=1,113 loops=1)

  • Join Filter: (subcategories.id = subcategory_members.subcategory)
7. 0.327 10.641 ↓ 37.1 1,113 1

Nested Loop Left Join (cost=2.12..6,963.15 rows=30 width=193) (actual time=0.072..10.641 rows=1,113 loops=1)

  • Join Filter: (COALESCE(items.parent, items.id) = task_members.task_id)
8. 0.392 10.314 ↓ 37.1 1,113 1

Nested Loop Left Join (cost=1.70..6,955.84 rows=30 width=189) (actual time=0.065..10.314 rows=1,113 loops=1)

  • Filter: ((parent.template IS NULL) OR (NOT parent.template))
9. 0.157 7.696 ↓ 37.1 1,113 1

Nested Loop (cost=1.70..6,880.42 rows=30 width=188) (actual time=0.064..7.696 rows=1,113 loops=1)

10. 0.031 0.483 ↓ 6.5 98 1

Nested Loop (cost=1.70..95.89 rows=15 width=60) (actual time=0.043..0.483 rows=98 loops=1)

11. 0.007 0.130 ↓ 3.3 23 1

Nested Loop (cost=1.27..34.64 rows=7 width=34) (actual time=0.031..0.130 rows=23 loops=1)

12. 0.004 0.039 ↓ 2.3 7 1

Nested Loop (cost=0.84..7.25 rows=3 width=21) (actual time=0.021..0.039 rows=7 loops=1)

13. 0.012 0.012 ↑ 1.0 1 1

Index Scan using team_members_pkey on team_members (cost=0.42..2.64 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ((team_id = 1248481) AND (userid = 1026626))
14. 0.023 0.023 ↓ 2.3 7 1

Index Scan using projects_team_idx on projects (cost=0.42..4.58 rows=3 width=17) (actual time=0.009..0.023 rows=7 loops=1)

  • Index Cond: (team = 1248481)
  • Filter: ((NOT template) AND (NOT deleted) AND (NOT archived))
  • Rows Removed by Filter: 1
15. 0.084 0.084 ↑ 2.0 3 7

Index Scan using categories_project_idx on categories (cost=0.42..9.07 rows=6 width=21) (actual time=0.007..0.012 rows=3 loops=7)

  • Index Cond: (project_id = projects.id)
  • Filter: (((NOT importing) OR (importing IS NULL)) AND (NOT deleted) AND (NOT template) AND (NOT archived))
  • Rows Removed by Filter: 2
16. 0.322 0.322 ↑ 2.0 4 23

Index Scan using subcategories_cat_idx on subcategories (cost=0.43..8.67 rows=8 width=34) (actual time=0.006..0.014 rows=4 loops=23)

  • Index Cond: (category = categories.id)
  • Filter: ((NOT deleted) AND (NOT template) AND ((NOT importing) OR (importing IS NULL)) AND ((NOT archived) OR (archived IS NULL)))
  • Rows Removed by Filter: 4
17. 0.196 7.056 ↑ 8.0 11 98

Append (cost=0.00..451.42 rows=88 width=138) (actual time=0.012..0.072 rows=11 loops=98)

18. 0.000 0.000 ↓ 0.0 0 98

Seq Scan on items (cost=0.00..0.00 rows=1 width=294) (actual time=0.000..0.000 rows=0 loops=98)

  • Filter: ((NOT template) AND (NOT importing) AND (NOT archived) AND ((NOT deleted) OR (deleted IS NULL)) AND (due_date <= 1560268799999::bigint) AND (subcategories.id = subcategory))
19. 6.860 6.860 ↑ 7.9 11 98

Index Scan using tasks_subcategory_idx on tasks (cost=0.56..451.42 rows=87 width=136) (actual time=0.011..0.070 rows=11 loops=98)

  • Index Cond: (subcategory = subcategories.id)
  • Filter: ((NOT template) AND (NOT importing) AND (NOT archived) AND ((NOT deleted) OR (deleted IS NULL)) AND (due_date <= 1560268799999::bigint))
  • Rows Removed by Filter: 42
20. 1.113 2.226 ↓ 0.0 0 1,113

Append (cost=0.00..2.49 rows=2 width=20) (actual time=0.002..0.002 rows=0 loops=1,113)

21. 0.000 0.000 ↓ 0.0 0 1,113

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

  • Filter: (items.parent = id)
22. 1.113 1.113 ↓ 0.0 0 1,113

Index Scan using tasks_id_idx on tasks parent_1 (cost=0.56..2.49 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=1,113)

  • Index Cond: (items.parent = id)
23. 0.000 0.000 ↓ 0.0 0 1,113

Materialize (cost=0.42..2.84 rows=10 width=9) (actual time=0.000..0.000 rows=0 loops=1,113)

24. 0.006 0.006 ↓ 0.0 0 1

Index Only Scan using task_members_userid_task_id on task_members (cost=0.42..2.79 rows=10 width=9) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (userid = 1026626)
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0 1,113

Materialize (cost=0.42..1.77 rows=11 width=12) (actual time=0.000..0.000 rows=0 loops=1,113)

26. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using subcategory_members_userid_subcategory on subcategory_members (cost=0.42..1.71 rows=11 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (userid = 1026626)
  • Heap Fetches: 0
27. 5.565 5.565 ↓ 0.0 0 1,113

Index Only Scan using assignees_pkey on assignees (cost=0.56..1.53 rows=1 width=5) (actual time=0.005..0.005 rows=0 loops=1,113)

  • Index Cond: ((task_id = items.id) AND (userid IS NOT NULL) AND (userid = 1026626))
  • Heap Fetches: 172
28. 0.236 0.236 ↓ 0.0 0 236

Index Only Scan using category_members_userid_category on category_members (cost=0.42..1.38 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=236)

  • Index Cond: ((userid = 1026626) AND (category = categories.id))
  • Heap Fetches: 0
29. 0.236 0.236 ↓ 0.0 0 236

Index Only Scan using project_members_pkey on project_members (cost=0.42..1.91 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=236)

  • Index Cond: ((project_id = projects.id) AND (userid = 1026626))
  • Heap Fetches: 0
30. 1.180 1.180 ↓ 0.0 0 236

Index Scan using statuses_pkey on statuses (cost=0.56..2.46 rows=1 width=39) (actual time=0.005..0.005 rows=0 loops=236)

  • Index Cond: (id = items.status_id)
  • Filter: (type <> ALL ('{closed,done}'::text[]))
  • Rows Removed by Filter: 1
Planning time : 7.418 ms
Execution time : 18.926 ms