explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gadq

Settings
# exclusive inclusive rows x rows loops node
1. 0.277 2,116.925 ↓ 112.0 112 1

Nested Loop Semi Join (cost=15,613.06..15,682.27 rows=1 width=524) (actual time=591.462..2,116.925 rows=112 loops=1)

  • Join Filter: (freelancer_taskis.project_id = collaborators_3.project_id)
2.          

CTE freelancer_taskis

3. 276.352 1,295.732 ↓ 80.2 44,272 1

Gather (cost=13,591.28..15,606.70 rows=552 width=1,633) (actual time=584.495..1,295.732 rows=44,272 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 74.825 1,019.380 ↓ 68.1 22,136 2

Nested Loop Left Join (cost=12,591.28..14,532.18 rows=325 width=1,492) (actual time=551.631..1,019.380 rows=22,136 loops=2)

5. 35.371 944.552 ↓ 68.1 22,136 2

Nested Loop Left Join (cost=12,590.99..13,172.63 rows=325 width=1,488) (actual time=551.574..944.552 rows=22,136 loops=2)

  • Filter: ((assignments.id IS NOT NULL) OR ((steps.id = stories.current_step_id) AND (selected_users.blocked_at IS NULL)))
  • Rows Removed by Filter: 3018
6. 31.057 909.180 ↓ 77.4 25,154 2

Nested Loop Left Join (cost=12,590.71..12,963.67 rows=325 width=1,492) (actual time=551.564..909.180 rows=25,154 loops=2)

7. 33.802 878.122 ↓ 77.4 25,154 2

Nested Loop Left Join (cost=12,590.42..12,863.77 rows=325 width=1,488) (actual time=551.558..878.122 rows=25,154 loops=2)

8. 257.044 844.319 ↓ 77.4 25,154 2

Nested Loop Left Join (cost=12,590.14..12,746.33 rows=325 width=1,488) (actual time=551.554..844.319 rows=25,154 loops=2)

  • Join Filter: ((assignments.profile_id IS NULL) AND (steps.profile_id IS NULL))
  • Rows Removed by Join Filter: 423455
9. 15.552 587.265 ↓ 64.1 20,838 2

Merge Left Join (cost=12,589.85..12,597.18 rows=325 width=1,492) (actual time=551.494..587.265 rows=20,838 loops=2)

  • Merge Cond: (steps.id = rated_steps.id)
10. 132.581 562.091 ↓ 64.1 20,838 2

Sort (cost=8,356.66..8,357.47 rows=325 width=1,488) (actual time=542.152..562.091 rows=20,838 loops=2)

  • Sort Key: steps.id
  • Sort Method: external merge Disk: 24336kB
11. 51.567 429.510 ↓ 64.1 20,838 2

Nested Loop Left Join (cost=2,143.99..8,343.10 rows=325 width=1,488) (actual time=40.796..429.510 rows=20,838 loops=2)

12. 48.403 377.941 ↓ 64.1 20,838 2

Nested Loop Left Join (cost=2,143.71..8,138.20 rows=325 width=868) (actual time=40.777..377.941 rows=20,838 loops=2)

13. 60.679 329.536 ↓ 64.1 20,838 2

Nested Loop Left Join (cost=2,143.43..8,039.98 rows=325 width=860) (actual time=40.752..329.536 rows=20,838 loops=2)

14. 50.255 268.855 ↓ 64.1 20,838 2

Nested Loop (cost=2,143.14..6,241.88 rows=325 width=836) (actual time=40.726..268.855 rows=20,838 loops=2)

15. 13.372 218.598 ↓ 64.1 20,838 2

Hash Join (cost=2,142.86..6,121.04 rows=325 width=216) (actual time=40.698..218.598 rows=20,838 loops=2)

  • Hash Cond: (steps.workflow_step_id = workflow_steps.id)
16. 10.945 187.611 ↓ 64.1 20,838 2

Hash Join (cost=1,221.14..5,198.47 rows=325 width=185) (actual time=22.758..187.611 rows=20,838 loops=2)

  • Hash Cond: (projects.account_id = accounts.id)
17. 10.775 176.158 ↓ 64.1 20,838 2

Hash Join (cost=1,195.16..5,171.64 rows=325 width=170) (actual time=22.230..176.158 rows=20,838 loops=2)

  • Hash Cond: (workflows.project_id = projects.id)
18. 45.460 164.596 ↓ 64.1 20,838 2

Nested Loop (cost=1,126.32..5,101.94 rows=325 width=136) (actual time=21.419..164.596 rows=20,838 loops=2)

19. 51.505 119.134 ↓ 64.1 20,838 2

Nested Loop (cost=1,126.04..4,979.52 rows=325 width=136) (actual time=21.393..119.134 rows=20,838 loops=2)

20. 14.099 67.627 ↓ 64.1 20,838 2

Hash Join (cost=1,125.76..4,875.66 rows=325 width=128) (actual time=21.356..67.627 rows=20,838 loops=2)

  • Hash Cond: (steps.story_id = stories.id)
21. 32.381 32.381 ↓ 63.0 20,966 2

Parallel Seq Scan on steps (cost=0.00..3,749.03 rows=333 width=28) (actual time=0.087..32.381 rows=20,966 loops=2)

  • Filter: ((deactivated_at IS NULL) AND (((group_type)::character varying)::text = 'external'::text))
  • Rows Removed by Filter: 45044
22. 9.702 21.147 ↑ 1.0 24,114 2

Hash (cost=824.33..824.33 rows=24,114 width=104) (actual time=21.147..21.147 rows=24,114 loops=2)

  • Buckets: 32768 Batches: 1 Memory Usage: 3319kB
23. 11.445 11.445 ↑ 1.0 24,114 2

Seq Scan on stories (cost=0.00..824.33 rows=24,114 width=104) (actual time=0.034..11.445 rows=24,114 loops=2)

  • Filter: (cancelled_at IS NULL)
  • Rows Removed by Filter: 619
24. 0.002 0.002 ↑ 1.0 1 41,675

Index Scan using content_types_pkey on content_types (cost=0.28..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=41,675)

  • Index Cond: (id = stories.content_type_id)
25. 0.002 0.002 ↑ 1.0 1 41,675

Index Scan using workflows_pkey on workflows (cost=0.28..0.38 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=41,675)

  • Index Cond: (id = content_types.workflow_id)
26. 0.268 0.787 ↑ 1.0 882 2

Hash (cost=57.82..57.82 rows=882 width=38) (actual time=0.787..0.787 rows=882 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 71kB
27. 0.519 0.519 ↑ 1.0 882 2

Seq Scan on projects (cost=0.00..57.82 rows=882 width=38) (actual time=0.029..0.519 rows=882 loops=2)

28. 0.235 0.508 ↑ 1.0 799 2

Hash (cost=15.99..15.99 rows=799 width=19) (actual time=0.508..0.508 rows=799 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
29. 0.273 0.273 ↑ 1.0 799 2

Seq Scan on accounts (cost=0.00..15.99 rows=799 width=19) (actual time=0.040..0.273 rows=799 loops=2)

30. 8.053 17.615 ↑ 1.0 20,254 2

Hash (cost=668.54..668.54 rows=20,254 width=35) (actual time=17.614..17.615 rows=20,254 loops=2)

  • Buckets: 32768 Batches: 1 Memory Usage: 1628kB
31. 9.562 9.562 ↑ 1.0 20,254 2

Seq Scan on workflow_steps (cost=0.00..668.54 rows=20,254 width=35) (actual time=0.074..9.562 rows=20,254 loops=2)

32. 0.002 0.002 ↑ 1.0 1 41,675

Index Scan using users_pkey on users story_creators (cost=0.28..0.37 rows=1 width=624) (actual time=0.002..0.002 rows=1 loops=41,675)

  • Index Cond: (id = stories.created_by)
33. 0.002 0.002 ↑ 1.0 1 41,675

Index Scan using index_assignments_on_step_id on assignments (cost=0.29..5.53 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=41,675)

  • Index Cond: (steps.id = step_id)
34. 0.002 0.002 ↑ 1.0 1 41,675

Index Scan using profiles_pkey on profiles profiles_4 (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=41,675)

  • Index Cond: (assignments.profile_id = id)
35. 0.002 0.002 ↑ 1.0 1 41,675

Index Scan using users_pkey on users (cost=0.28..0.63 rows=1 width=624) (actual time=0.002..0.002 rows=1 loops=41,675)

  • Index Cond: (profiles_4.user_id = id)
36. 0.477 9.622 ↑ 1.5 750 2

Sort (cost=4,233.19..4,236.03 rows=1,135 width=4) (actual time=9.333..9.622 rows=750 loops=2)

  • Sort Key: rated_steps.id
  • Sort Method: quicksort Memory: 60kB
37. 0.493 9.145 ↑ 1.5 750 2

HashAggregate (cost=4,152.90..4,164.25 rows=1,135 width=4) (actual time=8.992..9.145 rows=750 loops=2)

  • Group Key: rated_steps.id
38. 3.821 8.652 ↑ 1.2 982 2

Nested Loop (cost=27.56..4,150.06 rows=1,135 width=4) (actual time=0.304..8.652 rows=982 loops=2)

39. 4.020 4.828 ↓ 1.1 1,283 2

Nested Loop (cost=27.14..2,012.14 rows=1,155 width=8) (actual time=0.255..4.828 rows=1,283 loops=2)

40. 0.704 0.805 ↑ 1.0 1,347 2

Bitmap Heap Scan on adjustment_requests (cost=26.72..170.19 rows=1,347 width=8) (actual time=0.121..0.805 rows=1,347 loops=2)

  • Recheck Cond: (solved_at IS NULL)
  • Heap Blocks: exact=130
41. 0.101 0.101 ↑ 1.0 1,347 2

Bitmap Index Scan on index_adjustment_requests_on_solved_at (cost=0.00..26.39 rows=1,347 width=0) (actual time=0.101..0.101 rows=1,347 loops=2)

  • Index Cond: (solved_at IS NULL)
42. 0.003 0.003 ↑ 1.0 1 2,694

Index Only Scan using index_deactivated_steps_on_id on steps rated_steps (cost=0.42..1.37 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,694)

  • Index Cond: (id = adjustment_requests.target_id)
  • Heap Fetches: 0
43. 0.003 0.003 ↑ 1.0 1 2,566

Index Only Scan using index_deleted_messages_on_id on messages (cost=0.42..1.85 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,566)

  • Index Cond: (id = adjustment_requests.message_id)
  • Heap Fetches: 0
44. 0.010 0.010 ↓ 5.2 21 41,675

Index Scan using index_collaborators_roles_on_role_id on collaborators_roles (cost=0.29..0.42 rows=4 width=8) (actual time=0.003..0.010 rows=21 loops=41,675)

  • Index Cond: (workflow_steps.role_id = role_id)
45. 0.001 0.001 ↓ 0.0 0 50,307

Index Scan using collaborators_pkey on collaborators collaborators_4 (cost=0.29..0.36 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=50,307)

  • Index Cond: (collaborators_roles.collaborator_id = id)
46. 0.001 0.001 ↓ 0.0 0 50,307

Index Scan using profiles_pkey on profiles selected_profiles (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=50,307)

  • Index Cond: (collaborators_4.profile_id = id)
47. 0.001 0.001 ↓ 0.0 0 50,307

Index Scan using users_pkey on users selected_users (cost=0.28..0.63 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=50,307)

  • Index Cond: (selected_profiles.user_id = id)
48. 0.003 0.003 ↑ 1.0 1 44,272

Index Scan using index_bills_on_step_id on bills (cost=0.29..4.17 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=44,272)

  • Index Cond: (steps.id = step_id)
49. 0.375 2,110.712 ↓ 112.0 112 1

Nested Loop Semi Join (cost=5.79..66.45 rows=1 width=528) (actual time=591.439..2,110.712 rows=112 loops=1)

50. 0.561 2,109.239 ↓ 122.0 122 1

Nested Loop Semi Join (cost=5.22..49.82 rows=1 width=524) (actual time=591.429..2,109.239 rows=122 loops=1)

51. 4.895 2,106.116 ↓ 122.0 122 1

Nested Loop Semi Join (cost=4.61..49.02 rows=1 width=532) (actual time=591.409..2,106.116 rows=122 loops=1)

52. 1,515.321 1,515.321 ↓ 3,906.0 3,906 1

CTE Scan on freelancer_taskis (cost=0.00..24.84 rows=1 width=524) (actual time=588.105..1,515.321 rows=3,906 loops=1)

  • Filter: (((assignee_profile_id = 16) OR (assignee_profile_id IS NULL)) AND ((assignee_profile_id = 16) OR (assignee_profile_id IS NULL)) AND ((status <> 'finished'::text) OR (submitted_at > (CURRENT_DATE - '15 days'::interval))) AND ((status <> 'finished'::text) OR (submitted_at > (CURRENT_DATE - '15 days'::interval))))
  • Rows Removed by Filter: 40366
53. 167.449 585.900 ↓ 0.0 0 3,906

Nested Loop (cost=4.61..24.17 rows=1 width=8) (actual time=0.150..0.150 rows=0 loops=3,906)

54. 191.394 226.548 ↓ 9.8 49 3,906

Bitmap Heap Scan on collaborators (cost=4.33..22.58 rows=5 width=4) (actual time=0.015..0.058 rows=49 loops=3,906)

  • Recheck Cond: ((profile_id = freelancer_taskis.viewable_profile_id) AND (declined_at IS NULL) AND (finished_at IS NULL))
  • Filter: (started_at IS NOT NULL)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=138608
55. 35.154 35.154 ↓ 10.6 53 3,906

Bitmap Index Scan on index_collaborators_on_profile_id_and_project_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.009..0.009 rows=53 loops=3,906)

  • Index Cond: (profile_id = freelancer_taskis.viewable_profile_id)
56. 191.903 191.903 ↓ 0.0 0 191,903

Index Scan using profiles_pkey on profiles (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=191,903)

  • Index Cond: (id = collaborators.profile_id)
  • Filter: (user_id = 16)
  • Rows Removed by Filter: 1
57. 1.342 2.562 ↑ 1.0 1 122

Hash Join (cost=0.61..0.79 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=122)

  • Hash Cond: (collaborators_2.profile_id = profiles_2.id)
58. 0.610 0.610 ↑ 5.0 1 122

Index Scan using index_collaborators_on_profile_id_and_project_id on collaborators collaborators_2 (cost=0.29..0.45 rows=5 width=4) (actual time=0.005..0.005 rows=1 loops=122)

  • Index Cond: (profile_id = collaborators.profile_id)
  • Filter: (started_at IS NOT NULL)
59. 0.244 0.610 ↑ 1.0 1 122

Hash (cost=0.31..0.31 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=122)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
60. 0.366 0.366 ↑ 1.0 1 122

Index Scan using profiles_pkey on profiles profiles_2 (cost=0.28..0.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=122)

  • Index Cond: (id = collaborators.profile_id)
  • Filter: (user_id = 16)
61. 0.244 1.098 ↑ 1.0 1 122

Nested Loop (cost=0.57..16.62 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=122)

62. 0.488 0.488 ↑ 1.0 1 122

Index Scan using index_profiles_on_user_id_and_name on profiles profiles_1 (cost=0.28..8.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=122)

  • Index Cond: (user_id = 16)
63. 0.366 0.366 ↑ 1.0 1 122

Index Scan using index_collaborators_on_profile_id_and_project_id on collaborators collaborators_1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=122)

  • Index Cond: ((profile_id = profiles_1.id) AND (project_id = freelancer_taskis.project_id))
  • Filter: (started_at IS NOT NULL)
64. 0.776 5.936 ↑ 1.0 1 112

Nested Loop (cost=0.57..9.11 rows=1 width=4) (actual time=0.053..0.053 rows=1 loops=112)

65. 2.464 2.464 ↑ 2.2 12 112

Index Scan using index_collaborators_on_project_id on collaborators collaborators_3 (cost=0.29..0.96 rows=26 width=8) (actual time=0.008..0.022 rows=12 loops=112)

  • Index Cond: (project_id = collaborators_1.project_id)
  • Filter: ((declined_at IS NULL) AND (started_at IS NOT NULL) AND (finished_at IS NULL))
  • Rows Removed by Filter: 9
66. 2.696 2.696 ↓ 0.0 0 1,348

Index Scan using profiles_pkey on profiles profiles_3 (cost=0.28..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,348)

  • Index Cond: (id = collaborators_3.profile_id)
  • Filter: (user_id = 16)
  • Rows Removed by Filter: 1
Planning time : 41.105 ms
Execution time : 2,186.825 ms