explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WLB2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 39.165 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,640,125.57..2,697,838.87 rows=1 width=131) (actual time=39.165..39.165 rows=0 loops=1)

2.          

CTE task_activity

3. 0.004 38.565 ↑ 769.6 7 1

Unique (cost=2,640,090.66..2,640,117.65 rows=5,387 width=6) (actual time=38.561..38.565 rows=7 loops=1)

4. 0.000 38.561 ↑ 415.2 13 1

Sort (cost=2,640,090.66..2,640,104.15 rows=5,398 width=6) (actual time=38.560..38.561 rows=13 loops=1)

  • Sort Key: task_activity_1.task_id
  • Sort Method: quicksort Memory: 25kB
5. 23.018 56.795 ↑ 415.2 13 1

Gather (cost=1,000.70..2,639,756.03 rows=5,398 width=6) (actual time=2.341..56.795 rows=13 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 33.777 33.777 ↑ 562.2 4 3 / 3

Parallel Index Scan using task_activity_pkey on task_activity task_activity_1 (cost=0.70..2,638,216.23 rows=2,249 width=6) (actual time=21.749..33.777 rows=4 loops=3)

  • Index Cond: (userid = ANY ('{49,71841,74670,86640,94261,96709,773243,1083782,1400571,3903722,4306127,4312733,4322042,4327441,4354049,4363560,4364515,4371056,4406677,4418824,4428690,4445864,4445865,4445866,4445867,4445868,4445869,4445870,4445871,4445872,4445873,4445874,4445875,4445876,4445877,4445878,4445879,4445880,4445881,4445882,4445883,4445884,4445885,4445886,4445887,4445888,4445889,4445890,4445891,4445892,4445893,4445894,4445895,4445896,4445897,4445898,4445899,4445900,4445901,4445902,4445903,4445904,4445905,4445906,4445907,4445908,4445909,4445910,4445911,4445912,4445913,4445914,4445915,4445916,4445917,4445918,4445919,4445920,4445921,4445922,4445923,4445924,4445925,4445926,4445927,4445928,4445929,4445930,4445931,4445932,4445933,4445934,4445935,4445936,4445937,4445938,4445939,4445943,4445944,4445945,4445946,4445947,4445948,4445949,4445950,4445951,4445952,4445953,4445954,4445955,4445956,4445957,4445958,4445959,4445960,4445961,4445962,4445963,4445964,4445965,4445966,4470687,4470690,6317336,6328968,6328969,6328970,6328971,6328972,6328973,6328974,6328975,6328976,6328977,6328978,6328979,6328980,6328981,6328982,6328983,6357395,6369251,6374737,10201615,10201618,10561748,10601129,10624108,10624110,10651393,10681123}'::integer[]))
  • Filter: (((end_time IS NULL) AND (start_time < '1602738000000'::bigint)) OR ((end_time IS NOT NULL) AND (((start_time >= '1602651600000'::bigint) AND (start_time <= '1602738000000'::bigint)) OR ((end_time >= '1602651600000'::bigint) AND (end_time <= '1602738000000'::bigint)) OR ((start_time < '1602651600000'::bigint) AND (end_time > '1602738000000'::bigint)))))
  • Rows Removed by Filter: 8,808
7. 0.000 39.165 ↓ 0.0 0 1

Nested Loop Left Join (cost=7.50..57,720.37 rows=1 width=142) (actual time=39.165..39.165 rows=0 loops=1)

8. 0.001 39.165 ↓ 0.0 0 1

Nested Loop Left Join (cost=7.07..57,719.59 rows=1 width=146) (actual time=39.165..39.165 rows=0 loops=1)

9. 0.000 39.164 ↓ 0.0 0 1

Nested Loop Left Join (cost=6.64..57,718.83 rows=1 width=142) (actual time=39.164..39.164 rows=0 loops=1)

10. 0.004 39.164 ↓ 0.0 0 1

Nested Loop (cost=6.22..57,718.07 rows=1 width=144) (actual time=39.164..39.164 rows=0 loops=1)

  • Join Filter: (projects.team = team_members.team_id)
  • Rows Removed by Join Filter: 7
11. 0.007 39.139 ↑ 589.9 7 1

Nested Loop Left Join (cost=5.79..57,590.70 rows=4,129 width=140) (actual time=38.747..39.139 rows=7 loops=1)

12. 0.009 39.118 ↑ 589.9 7 1

Nested Loop (cost=5.79..52,044.86 rows=4,129 width=139) (actual time=38.738..39.118 rows=7 loops=1)

13. 0.005 38.990 ↑ 589.9 7 1

Nested Loop (cost=2.10..36,705.62 rows=4,129 width=151) (actual time=38.701..38.990 rows=7 loops=1)

14. 0.007 38.866 ↑ 590.3 7 1

Nested Loop (cost=1.42..31,214.10 rows=4,132 width=164) (actual time=38.671..38.866 rows=7 loops=1)

15. 0.005 38.824 ↑ 620.3 7 1

Nested Loop (cost=0.99..26,654.93 rows=4,342 width=155) (actual time=38.653..38.824 rows=7 loops=1)

16. 0.006 38.770 ↑ 678.6 7 1

Nested Loop (cost=0.56..21,654.45 rows=4,750 width=146) (actual time=38.631..38.770 rows=7 loops=1)

17. 0.006 38.715 ↑ 769.6 7 1

Nested Loop (cost=0.00..15,273.51 rows=5,387 width=124) (actual time=38.614..38.715 rows=7 loops=1)

18. 38.569 38.569 ↑ 769.6 7 1

CTE Scan on task_activity (cost=0.00..107.74 rows=5,387 width=32) (actual time=38.563..38.569 rows=7 loops=1)

19. 0.014 0.140 ↑ 2.0 1 7

Append (cost=0.00..2.80 rows=2 width=92) (actual time=0.019..0.020 rows=1 loops=7)

20. 0.007 0.007 ↓ 0.0 0 7

Seq Scan on items (cost=0.00..0.00 rows=1 width=415) (actual time=0.001..0.001 rows=0 loops=7)

  • Filter: ((NOT deleted) AND (NOT template) AND (task_activity.task_id = id))
21. 0.119 0.119 ↑ 1.0 1 7

Index Scan using tasks_id_idx on tasks (cost=0.57..2.79 rows=1 width=92) (actual time=0.017..0.017 rows=1 loops=7)

  • Index Cond: (id = task_activity.task_id)
  • Filter: ((NOT deleted) AND (NOT template))
22. 0.049 0.049 ↑ 1.0 1 7

Index Scan using subcategories_pkey on subcategories (cost=0.56..1.18 rows=1 width=30) (actual time=0.007..0.007 rows=1 loops=7)

  • Index Cond: (id = items.subcategory)
  • Filter: (NOT deleted)
23. 0.049 0.049 ↑ 1.0 1 7

Index Scan using categories_pkey on categories (cost=0.43..1.05 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=7)

  • Index Cond: (id = subcategories.category)
  • Filter: (NOT deleted)
24. 0.035 0.035 ↑ 1.0 1 7

Index Scan using projects_pkey on projects (cost=0.43..1.05 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=7)

  • Index Cond: (id = categories.project_id)
  • Filter: (NOT deleted)
25. 0.119 0.119 ↑ 1.0 1 7

Index Scan using statuses_pkey on statuses (cost=0.69..1.33 rows=1 width=49) (actual time=0.017..0.017 rows=1 loops=7)

  • Index Cond: (id = items.status_id)
26. 0.007 0.119 ↑ 1.0 1 7

Aggregate (cost=3.68..3.70 rows=1 width=1) (actual time=0.017..0.017 rows=1 loops=7)

27. 0.112 0.112 ↑ 4.2 11 7

Index Only Scan using status_group_idx2 on statuses statuses_1 (cost=0.56..3.57 rows=46 width=0) (actual time=0.015..0.016 rows=11 loops=7)

  • Index Cond: (status_group = subcategories.status_group)
  • Heap Fetches: 0
28. 0.007 0.014 ↓ 0.0 0 7

Append (cost=0.00..1.32 rows=2 width=7) (actual time=0.002..0.002 rows=0 loops=7)

29. 0.000 0.000 ↓ 0.0 0 7

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

  • Filter: (items.parent = id)
30. 0.007 0.007 ↓ 0.0 0 7

Index Scan using tasks_id_idx on tasks parent_1 (cost=0.57..1.31 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=7)

  • Index Cond: (items.parent = id)
31. 0.004 0.021 ↑ 2.0 1 7

Materialize (cost=0.43..3.51 rows=2 width=12) (actual time=0.003..0.003 rows=1 loops=7)

32. 0.017 0.017 ↑ 2.0 1 1

Index Scan using team_member_user_idx on team_members (cost=0.43..3.50 rows=2 width=12) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (userid = 183)
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using task_members_userid_task_id on task_members (cost=0.42..0.74 rows=1 width=10) (never executed)

  • Index Cond: ((userid = 86,640) AND (task_id = COALESCE(items.parent, items.id)))
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using subcategory_members_userid_subcategory on subcategory_members (cost=0.43..0.75 rows=1 width=12) (never executed)

  • Index Cond: ((userid = 86,640) AND (subcategory = subcategories.id))
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Index Only Scan using category_members_userid_category on category_members (cost=0.42..0.74 rows=1 width=12) (never executed)

  • Index Cond: ((userid = 86,640) AND (category = categories.id))
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using project_members_pkey on project_members (cost=0.43..0.75 rows=1 width=12) (never executed)

  • Index Cond: ((project_id = projects.id) AND (userid = 86,640))
  • Heap Fetches: 0
Planning time : 5.348 ms
Execution time : 57.741 ms