explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dz4F

Settings
# exclusive inclusive rows x rows loops node
1. 4.592 337.494 ↓ 603.5 1,207 1

Sort (cost=1,567.84..1,567.85 rows=2 width=929) (actual time=337.374..337.494 rows=1,207 loops=1)

  • Sort Key: job.priority_id, job.id DESC
  • Sort Method: quicksort Memory: 1,275kB
2. 48.345 332.902 ↓ 603.5 1,207 1

GroupAggregate (cost=1,567.55..1,567.83 rows=2 width=929) (actual time=284.337..332.902 rows=1,207 loops=1)

  • Group Key: job.id, (true), title.id, line_of_business.id, priority.id, client.id, vendor_location.id, release_type.id, client_group.id, job_type.id
3. 9.468 284.557 ↓ 1,221.5 2,443 1

Sort (cost=1,567.55..1,567.55 rows=2 width=2,303) (actual time=283.880..284.557 rows=2,443 loops=1)

  • Sort Key: job.id DESC, (true), title.id, line_of_business.id, priority.id, client.id, vendor_location.id, release_type.id, client_group.id, job_type.id
  • Sort Method: quicksort Memory: 1,356kB
4. 3.232 275.089 ↓ 1,221.5 2,443 1

Nested Loop Left Join (cost=1,144.91..1,567.54 rows=2 width=2,303) (actual time=29.818..275.089 rows=2,443 loops=1)

5. 3.759 271.857 ↓ 1,221.5 2,443 1

Nested Loop Left Join (cost=1,144.63..1,559.55 rows=2 width=2,274) (actual time=29.808..271.857 rows=2,443 loops=1)

6. 2.639 263.212 ↓ 1,221.5 2,443 1

Nested Loop Left Join (cost=1,144.35..1,558.96 rows=2 width=2,274) (actual time=29.785..263.212 rows=2,443 loops=1)

7. 5.907 250.801 ↓ 1,221.5 2,443 1

Nested Loop Left Join (cost=1,144.07..1,558.16 rows=2 width=2,274) (actual time=29.736..250.801 rows=2,443 loops=1)

8. 2.220 235.118 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,143.44..1,553.48 rows=1 width=2,282) (actual time=29.682..235.118 rows=1,222 loops=1)

9. 2.270 232.898 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,143.30..1,552.52 rows=1 width=1,766) (actual time=29.668..232.898 rows=1,222 loops=1)

10. 2.060 229.406 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,143.16..1,552.35 rows=1 width=1,758) (actual time=29.651..229.406 rows=1,222 loops=1)

11. 2.032 227.346 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,142.89..1,552.05 rows=1 width=1,751) (actual time=29.641..227.346 rows=1,222 loops=1)

12. 2.523 225.314 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,142.61..1,551.35 rows=1 width=1,743) (actual time=29.631..225.314 rows=1,222 loops=1)

13. 3.055 220.347 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,142.33..1,551.04 rows=1 width=1,735) (actual time=29.617..220.347 rows=1,222 loops=1)

14. 5.815 217.292 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,142.18..1,548.05 rows=1 width=1,723) (actual time=29.601..217.292 rows=1,222 loops=1)

  • Join Filter: (om_job_countries.job_id = job.id)
  • Rows Removed by Join Filter: 12,220
15. 2.664 209.033 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,142.18..1,546.83 rows=1 width=1,715) (actual time=29.584..209.033 rows=1,222 loops=1)

16. 8.514 206.369 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,142.04..1,546.66 rows=1 width=1,651) (actual time=29.574..206.369 rows=1,222 loops=1)

  • Join Filter: (om_job_territories.job_id = job.id)
  • Rows Removed by Join Filter: 2,444
17. 2.164 188.079 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,141.00..1,544.47 rows=1 width=1,611) (actual time=29.518..188.079 rows=1,222 loops=1)

18. 2.181 185.915 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,140.72..1,540.73 rows=1 width=1,611) (actual time=29.512..185.915 rows=1,222 loops=1)

19. 2.728 173.958 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,140.57..1,540.56 rows=1 width=1,603) (actual time=29.504..173.958 rows=1,222 loops=1)

20. 1.953 168.786 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,140.30..1,540.25 rows=1 width=1,596) (actual time=29.494..168.786 rows=1,222 loops=1)

21. 2.466 163.167 ↓ 1,222.0 1,222 1

Nested Loop Left Join (cost=1,140.02..1,539.84 rows=1 width=1,588) (actual time=29.484..163.167 rows=1,222 loops=1)

22. 3.040 155.817 ↓ 1,221.0 1,221 1

Nested Loop Left Join (cost=1,139.73..1,539.49 rows=1 width=1,580) (actual time=29.474..155.817 rows=1,221 loops=1)

23. 1.698 149.114 ↓ 1,221.0 1,221 1

Nested Loop Left Join (cost=1,139.45..1,539.08 rows=1 width=1,545) (actual time=29.464..149.114 rows=1,221 loops=1)

  • Filter: (ojrt.skipped IS NOT TRUE)
24. 1.866 142.588 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,139.17..1,538.69 rows=1 width=1,541) (actual time=29.451..142.588 rows=1,207 loops=1)

25. 1.850 140.722 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,139.03..1,538.51 rows=1 width=1,528) (actual time=29.446..140.722 rows=1,207 loops=1)

26. 2.441 136.458 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,138.75..1,538.20 rows=1 width=1,520) (actual time=29.437..136.458 rows=1,207 loops=1)

27. 1.553 134.017 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,138.61..1,538.00 rows=1 width=1,512) (actual time=29.428..134.017 rows=1,207 loops=1)

28. 2.180 130.050 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,138.34..1,537.66 rows=1 width=1,504) (actual time=29.417..130.050 rows=1,207 loops=1)

29. 1.574 126.663 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,138.20..1,537.49 rows=1 width=1,503) (actual time=29.409..126.663 rows=1,207 loops=1)

30. 1.861 122.675 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,138.06..1,537.33 rows=1 width=987) (actual time=29.401..122.675 rows=1,207 loops=1)

31. 1.352 120.814 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,137.92..1,537.17 rows=1 width=972) (actual time=29.396..120.814 rows=1,207 loops=1)

32. 2.103 117.048 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,137.78..1,537.01 rows=1 width=928) (actual time=29.389..117.048 rows=1,207 loops=1)

33. 1.603 112.531 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,137.65..1,536.85 rows=1 width=896) (actual time=29.382..112.531 rows=1,207 loops=1)

34. 1.880 84.374 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,137.52..1,536.69 rows=1 width=380) (actual time=29.375..84.374 rows=1,207 loops=1)

35. 1.934 82.494 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,137.24..1,536.39 rows=1 width=370) (actual time=29.364..82.494 rows=1,207 loops=1)

36. 34.239 75.732 ↓ 1,207.0 1,207 1

Nested Loop Left Join (cost=1,137.10..1,536.23 rows=1 width=357) (actual time=29.350..75.732 rows=1,207 loops=1)

37. 8.469 39.079 ↓ 1,207.0 1,207 1

Hash Join (cost=1,128.51..1,519.90 rows=1 width=375) (actual time=29.332..39.079 rows=1,207 loops=1)

  • Hash Cond: ((job.title_id)::text = (title.id)::text)
38. 1.475 1.475 ↑ 1.0 4,704 1

Seq Scan on om_jobs job (cost=0.00..379.04 rows=4,704 width=327) (actual time=0.013..1.475 rows=4,704 loops=1)

39. 0.120 29.135 ↓ 5.3 16 1

Hash (cost=1,128.47..1,128.47 rows=3 width=59) (actual time=29.135..29.135 rows=16 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
40. 29.015 29.015 ↓ 5.3 16 1

Seq Scan on om_titles title (cost=0.00..1,128.47 rows=3 width=59) (actual time=0.807..29.015 rows=16 loops=1)

  • Filter: ((title)::text ~~* '%see%'::text)
  • Rows Removed by Filter: 30,102
41. 0.000 2.414 ↓ 0.0 0 1,207

Bitmap Heap Scan on om_titles parent_title (cost=8.59..16.31 rows=2 width=10) (actual time=0.002..0.002 rows=0 loops=1,207)

  • Recheck Cond: (((title.tv_season_id)::text = (id)::text) OR ((title.tv_show_id)::text = (id)::text))
42. 2.414 2.414 ↓ 0.0 0 1,207

BitmapOr (cost=8.59..8.59 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1,207)

43. 0.000 0.000 ↓ 0.0 0 1,207

Bitmap Index Scan on om_titles_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1,207)

  • Index Cond: ((id)::text = (title.tv_season_id)::text)
44. 0.000 0.000 ↓ 0.0 0 1,207

Bitmap Index Scan on om_titles_pkey (cost=0.00..4.29 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1,207)

  • Index Cond: ((id)::text = (title.tv_show_id)::text)
45. 4.828 4.828 ↑ 1.0 1 1,207

Index Scan using clients_pkey on clients client (cost=0.14..0.16 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=1,207)

  • Index Cond: (id = job.client_id)
46. 0.000 0.000 ↓ 0.0 0 1,207

Index Scan using vendor_locations_pkey on vendor_locations vendor_location (cost=0.27..0.30 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=1,207)

  • Index Cond: (id = job.job_office_id)
47. 26.554 26.554 ↑ 1.0 1 1,207

Index Scan using om_release_types_pkey on om_release_types release_type (cost=0.13..0.15 rows=1 width=524) (actual time=0.022..0.022 rows=1 loops=1,207)

  • Index Cond: (id = job.om_release_type_id)
48. 2.414 2.414 ↑ 1.0 1 1,207

Index Scan using line_of_businesses_pkey on line_of_businesses line_of_business (cost=0.14..0.16 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=1,207)

  • Index Cond: (id = job.line_of_business_id)
49. 2.414 2.414 ↑ 1.0 1 1,207

Index Scan using priorities_pkey on priorities priority (cost=0.13..0.15 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=1,207)

  • Index Cond: (id = job.priority_id)
50. 0.000 0.000 ↓ 0.0 0 1,207

Index Scan using client_teams_pkey on client_teams client_group (cost=0.14..0.16 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=1,207)

  • Index Cond: (id = job.client_team_id)
51. 2.414 2.414 ↑ 1.0 1 1,207

Index Scan using om_job_types_pkey on om_job_types job_type (cost=0.14..0.16 rows=1 width=524) (actual time=0.002..0.002 rows=1 loops=1,207)

  • Index Cond: (id = job.job_type_id)
52. 1.207 1.207 ↓ 0.0 0 1,207

Index Scan using idx_om_job_bookmarks_job_id on om_job_bookmarks (cost=0.14..0.16 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1,207)

  • Index Cond: (job_id = job.id)
  • Filter: (user_id = 7,496)
53. 2.414 2.414 ↓ 0.0 0 1,207

Index Only Scan using idx_om_jobs_job_tags_job_id_tag_id on om_jobs_job_tags ojjt (cost=0.27..0.31 rows=2 width=16) (actual time=0.002..0.002 rows=0 loops=1,207)

  • Index Cond: (job_id = job.id)
  • Heap Fetches: 3
54. 0.000 0.000 ↓ 0.0 0 1,207

Index Scan using om_job_tags_pkey on om_job_tags (cost=0.14..0.20 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1,207)

  • Index Cond: (id = ojjt.tag_id)
55. 2.414 2.414 ↓ 0.0 0 1,207

Index Scan using idx_om_jobs_languages_om_job_id on om_jobs_languages ojl (cost=0.28..0.30 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,207)

  • Index Cond: (om_job_id = job.id)
56. 0.000 0.000 ↓ 0.0 0 1,207

Index Scan using languages_pkey on languages (cost=0.14..0.18 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=1,207)

  • Index Cond: (id = ojl.language_id)
57. 4.828 4.828 ↑ 1.0 1 1,207

Index Scan using idx_om_job_runs_tasks_job_run_id on om_job_runs_tasks ojrt (cost=0.28..0.38 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=1,207)

  • Index Cond: (job_run_id = job.latest_job_run_id)
58. 3.663 3.663 ↑ 1.0 1 1,221

Index Scan using om_tasks_pkey on om_tasks inner_task (cost=0.28..0.41 rows=1 width=43) (actual time=0.003..0.003 rows=1 loops=1,221)

  • Index Cond: (id = ojrt.task_id)
59. 4.884 4.884 ↑ 1.0 1 1,221

Index Scan using idx_om_jobs_project_managers_job_id on om_jobs_project_managers (cost=0.28..0.33 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,221)

  • Index Cond: (job_id = job.id)
60. 3.666 3.666 ↑ 1.0 1 1,222

Index Scan using users_pkey on users (cost=0.28..0.41 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,222)

  • Index Cond: (id = om_jobs_project_managers.user_id)
61. 2.444 2.444 ↑ 1.0 1 1,222

Index Scan using contacts_pkey on contacts (cost=0.28..0.31 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=1,222)

  • Index Cond: (id = users.contact_id)
62. 9.776 9.776 ↓ 0.0 0 1,222

Index Scan using idx_om_jobs_client_managers_job_id on om_jobs_client_managers (cost=0.14..0.16 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1,222)

  • Index Cond: (job_id = job.id)
63. 0.000 0.000 ↓ 0.0 0 1,222

Index Only Scan using users_pkey on users client_users (cost=0.28..3.74 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,222)

  • Index Cond: (id = om_jobs_client_managers.user_id)
  • Heap Fetches: 0
64. 7.311 9.776 ↑ 1.0 2 1,222

Hash Right Join (cost=1.04..2.16 rows=2 width=48) (actual time=0.003..0.008 rows=2 loops=1,222)

  • Hash Cond: (territories.id = om_job_territories.territory_id)
65. 2.444 2.444 ↑ 1.0 7 1,222

Seq Scan on territories (cost=0.00..1.07 rows=7 width=40) (actual time=0.001..0.002 rows=7 loops=1,222)

66. 0.014 0.021 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=16) (actual time=0.021..0.021 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on om_job_territories (cost=0.00..1.02 rows=2 width=16) (actual time=0.007..0.007 rows=2 loops=1)

68. 0.000 0.000 ↓ 0.0 0 1,222

Index Scan using index_client_contacts_on_user_id on client_contacts (cost=0.14..0.16 rows=1 width=72) (actual time=0.000..0.000 rows=0 loops=1,222)

  • Index Cond: (user_id = client_users.id)
69. 2.444 2.444 ↑ 1.0 10 1,222

Seq Scan on om_job_countries (cost=0.00..1.10 rows=10 width=16) (actual time=0.001..0.002 rows=10 loops=1,222)

70. 0.000 0.000 ↓ 0.0 0 1,222

Index Scan using countries_pkey on countries (cost=0.14..2.96 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=1,222)

  • Index Cond: (id = om_job_countries.country_id)
71. 2.444 2.444 ↓ 0.0 0 1,222

Index Scan using idx_om_task_assignees_task_id on om_task_assignees (cost=0.28..0.30 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,222)

  • Index Cond: (task_id = inner_task.id)
72. 0.000 0.000 ↓ 0.0 0 1,222

Index Scan using users_pkey on users task_assignees_users (cost=0.28..0.70 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1,222)

  • Index Cond: (id = om_task_assignees.user_id)
73. 0.000 0.000 ↓ 0.0 0 1,222

Index Scan using contacts_pkey on contacts task_assignees_contacts (cost=0.28..0.31 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=1,222)

  • Index Cond: (id = task_assignees_users.contact_id)
74. 1.222 1.222 ↓ 0.0 0 1,222

Index Only Scan using idx_om_jobs_job_configurations_job_id_config_id on om_jobs_job_configurations ojjc (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,222)

  • Index Cond: (job_id = job.id)
  • Heap Fetches: 9
75. 0.000 0.000 ↓ 0.0 0 1,222

Index Scan using om_job_configurations_pkey on om_job_configurations (cost=0.14..0.82 rows=1 width=524) (actual time=0.000..0.000 rows=0 loops=1,222)

  • Index Cond: (id = ojjc.job_configuration_id)
76. 3.666 9.776 ↑ 1.0 2 1,222

Bitmap Heap Scan on om_base_io_requirements base_io_requiement (cost=0.63..4.66 rows=2 width=8) (actual time=0.007..0.008 rows=2 loops=1,222)

  • Recheck Cond: ((id = inner_task.input_requirement_id) OR (id = inner_task.output_requirement_id))
  • Heap Blocks: exact=1,233
77. 1.222 6.110 ↓ 0.0 0 1,222

BitmapOr (cost=0.63..0.63 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=1,222)

78. 3.666 3.666 ↑ 1.0 1 1,222

Bitmap Index Scan on om_base_io_requirements_pkey (cost=0.00..0.32 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1,222)

  • Index Cond: (id = inner_task.input_requirement_id)
79. 1.222 1.222 ↑ 1.0 1 1,222

Bitmap Index Scan on om_base_io_requirements_pkey (cost=0.00..0.32 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1,222)

  • Index Cond: (id = inner_task.output_requirement_id)
80. 9.772 9.772 ↑ 1.0 1 2,443

Index Scan using idx_om_requirements_base_io_requirement_id on om_requirements requirement (cost=0.29..0.39 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=2,443)

  • Index Cond: (base_io_requirement_id = base_io_requiement.id)
  • Filter: ((source_io_requirement_id IS NULL) AND ((dtype)::text = 'AssetRequirement'::text))
  • Rows Removed by Filter: 0
81. 4.886 4.886 ↑ 1.0 1 2,443

Index Scan using om_abstract_assets_pkey on om_abstract_assets asset (cost=0.27..0.30 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2,443)

  • Index Cond: (id = requirement.abstract_asset_id)
82. 0.000 0.000 ↓ 0.0 0 2,443

Index Scan using alphas_pkey on alphas alpha (cost=0.29..4.00 rows=1 width=37) (actual time=0.000..0.000 rows=0 loops=2,443)

  • Index Cond: (id = asset.alpha_id)
Planning time : 21.339 ms
Execution time : 339.594 ms