explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qwNm

Settings
# exclusive inclusive rows x rows loops node
1. 2,218.692 16,419.526 ↑ 643.6 100 1

Limit (cost=11549515.82..11549516.07 rows=100 width=534) (actual time=16419.507..16419.805 rows=100 loops=1)-> Sort (cost=11,549,515.82..11,549,676.73 rows=64,365 width=534) (actual time=16,419.505..16,419.526 rows=100 loops=1)

2. 676.927 14,200.834 ↑ 1.0 64,199 1

Sort Key: job.actual_due_date, job.priority_id, job.id DESCSort Method: top-N heapsort Memory: 229kB-> WindowAgg (cost=369630.43..11547055.84 rows=64365 width=534) (actual time=15480.203..16365.740 rows=64199 loops=1)-> Nested Loop Left Join (cost=369630.43..11544803.06 rows=64365 width=1495) (actual time=13390.811..15339.753 rows=64199 loops=1)-> Nested Loop Left Join (cost=369630.28..11526571.95 rows=64365 width=1494) (actual time=13390.801..15223.039 rows=64199 loops=1)-> Nested Loop Left Join (cost=369551.92..6480871.35 rows=64365 width=1494) (actual time=13390.775..14948.924 rows=64199 loops=1)-> Hash Left Join (cost=369,457.53..403,401.95 rows=64,365 width=1,494) (actual time=13,390.275..14,200.834 rows=64,199 loops=1)

  • Index Cond: (job.id = job_id)
3. 1,099.574 13,459.708 ↑ 1.0 64,199 1

Hash Cond: (job.id = client_managers_group.job_id)-> Hash Left Join (cost=362,647.69..396,423.14 rows=64,365 width=1,462) (actual time=12,766.666..13,459.708 rows=64,199 loops=1)

4. 0.000 12,360.134 ↑ 1.0 64,199 1

Hash Cond: (job.id = project_managers_group.job_id)-> Hash Left Join (cost=356,136.31..389,742.79 rows=64,365 width=1,430) (actual time=11,801.174..12,360.134 rows=64,199 loops=1)

5. 8,155.591 12,317.172 ↑ 1.0 64,199 1

Hash Cond: (job.job_type_id = job_type.id)-> Hash Left Join (cost=356,123.16..389,556.17 rows=64,365 width=914) (actual time=11,801.128..12,317.172 rows=64,199 loops=1)

6. 3,808.510 4,161.581 ↑ 1.0 64,199 1

Hash Cond: (job.id = job_alphas.job_id)-> Hash Left Join (cost=48,716.78..67,145.82 rows=64,365 width=882) (actual time=3,729.150..4,161.581 rows=64,199 loops=1)

7. 0.000 353.071 ↑ 1.0 64,199 1

Hash Cond: (job.id = job_assignees.job_id)-> Hash Left Join (cost=1,498.23..5,526.29 rows=64,365 width=850) (actual time=21.337..353.071 rows=64,199 loops=1)

8. 0.000 311.979 ↑ 1.0 64,199 1

Hash Cond: (job.client_team_id = client_group.id)-> Hash Left Join (cost=1,495.85..5,353.58 rows=64,365 width=835) (actual time=21.288..311.979 rows=64,199 loops=1)

9. 0.000 268.677 ↑ 1.0 64,199 1

Hash Cond: (job.priority_id = priority.id)-> Hash Left Join (cost=1,465.15..5,153.15 rows=64,365 width=791) (actual time=21.273..268.677 rows=64,199 loops=1)

10. 41.249 228.245 ↑ 1.0 64,199 1

Hash Cond: (job.line_of_business_id = line_of_business.id)-> Hash Left Join (cost=1,433.33..4,951.63 rows=64,365 width=759) (actual time=21.252..228.245 rows=64,199 loops=1)

11. 39.435 186.996 ↑ 1.0 64,199 1

Hash Cond: (job.om_release_type_id = release_type.id)-> Hash Left Join (cost=1,420.18..4,764.40 rows=64,365 width=243) (actual time=21.236..186.996 rows=64,199 loops=1)

12. 40.011 147.561 ↑ 1.0 64,199 1

Hash Cond: (job.job_office_id = vendor_location.id)-> Hash Left Join (cost=1,402.88..4,576.21 rows=64,365 width=233) (actual time=20.992..147.561 rows=64,199 loops=1)

13. 66.042 107.550 ↑ 1.0 64,199 1

Hash Cond: (job.client_id = client.id)-> Hash Left Join (cost=1,400.57..4,392.60 rows=64,365 width=222) (actual time=20.930..107.550 rows=64,199 loops=1)

14. 0.000 41.508 ↑ 1.0 64,199 1

Hash Cond: ((job.title_id)::text = (title.id)::text)-> Seq Scan on om_jobs job (cost=0.00..2,823.05 rows=64,365 width=192) (actual time=0.013..41.508 rows=64,199 loops=1)

15. 3,652.045 3,652.045 ↓ 1.2 64,197 1

Filter: ((status_summary)::text = ANY ('{IN_PROGRESS,NOT_WORKABLE,WORKABLE}'::text[]))Rows Removed by Filter: 1-> Hash (cost=1031.92..1031.92 rows=29492 width=41) (actual time=20.863..20.863 rows=29454 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 2421kB-> Seq Scan on om_titles title (cost=0.00..1031.92 rows=29492 width=41) (actual time=0.006..9.903 rows=29454 loops=1)-> Hash (cost=1.58..1.58 rows=58 width=19) (actual time=0.046..0.046 rows=58 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 11kB-> Seq Scan on clients client (cost=0.00..1.58 rows=58 width=19) (actual time=0.009..0.024 rows=58 loops=1)-> Hash (cost=12.69..12.69 rows=369 width=18) (actual time=0.236..0.237 rows=369 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 27kB-> Seq Scan on vendor_locations vendor_location (cost=0.00..12.69 rows=369 width=18) (actual time=0.005..0.117 rows=369 loops=1)-> Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.009..0.010 rows=6 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 9kB-> Seq Scan on om_release_types release_type (cost=0.00..11.40 rows=140 width=524) (actual time=0.004..0.006 rows=6 loops=1)-> Hash (cost=19.70..19.70 rows=970 width=40) (actual time=0.013..0.014 rows=12 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 9kB-> Seq Scan on line_of_businesses line_of_business (cost=0.00..19.70 rows=970 width=40) (actual time=0.004..0.008 rows=12 loops=1)-> Hash (cost=19.20..19.20 rows=920 width=44) (actual time=0.009..0.009 rows=5 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 9kB-> Seq Scan on priorities priority (cost=0.00..19.20 rows=920 width=44) (actual time=0.004..0.005 rows=5 loops=1)-> Hash (cost=1.61..1.61 rows=61 width=23) (actual time=0.042..0.042 rows=61 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 12kB-> Seq Scan on client_teams client_group (cost=0.00..1.61 rows=61 width=23) (actual time=0.005..0.020 rows=61 loops=1)-> Hash (cost=46173.47..46173.47 rows=51447 width=40) (actual time=3707.713..3707.713 rows=64197 loops=1)Buckets: 65536 Batches: 2 Memory Usage: 2788kB-> Subquery Scan on job_assignees (cost=44630.06..46173.47 rows=51447 width=40) (actual time=3242.731..3680.374 rows=64197 loops=1)-> GroupAggregate (cost=44,630.06..45,659.00 rows=51,447 width=72) (actual time=3,242.730..3,652.045 rows=64,197 loops=1)

16. 0.000 3,393.342 ↓ 11.4 585,434 1

Group Key: om_jobs.id-> Sort (cost=44,630.06..44,758.67 rows=51,447 width=16) (actual time=3,242.700..3,393.342 rows=585,434 loops=1)

17. 0.000 5,698.710 ↓ 9.1 195,145 3

Sort Key: om_jobs.idSort Method: external merge Disk: 14904kB-> Gather (cost=6864.54..40604.12 rows=51447 width=16) (actual time=165.170..2538.014 rows=585434 loops=1)Workers Planned: 2Workers Launched: 2-> Nested Loop (cost=5864.54..34459.42 rows=21436 width=16) (actual time=147.484..2717.266 rows=195145 loops=3)-> Hash Join (cost=5,864.11..24,343.24 rows=21,478 width=32) (actual time=147.440..1,899.570 rows=195,145 loops=3)

18. 3,928.932 4,852.026 ↓ 9.1 195,145 3

Hash Cond: (ojr.id = om_jobs.latest_job_run_id)-> Hash Join (cost=2,477.86..20,661.62 rows=21,488 width=40) (actual time=74.018..1,617.342 rows=195,145 loops=3)

19. 0.000 923.094 ↓ 9.1 195,144 3

Hash Cond: (ojrt.job_run_id = ojr.id)-> Nested Loop (cost=99.92..18227.29 rows=21488 width=32) (actual time=2.578..1329.625 rows=195145 loops=3)-> Hash Join (cost=99.50..7,293.11 rows=21,488 width=16) (actual time=2.561..307.698 rows=195,144 loops=3)

20. 0.000 3.057 ↑ 1.0 415 3

Hash Cond: (om_task_assignees.user_id = users.id)-> Parallel Seq Scan on om_task_assignees (cost=0.00..6063.90 rows=243890 width=16) (actual time=0.005..91.486 rows=195144 loops=3)-> Hash (cost=94.27..94.27 rows=418 width=8) (actual time=2.510..2.510 rows=415 loops=3)Buckets: 1024 Batches: 1 Memory Usage: 25kB-> Merge Join (cost=0.56..94.27 rows=418 width=8) (actual time=0.039..1.019 rows=415 loops=3)

21. 1,756.299 1,756.299 ↑ 1.0 1 585,433

Merge Cond: (users.contact_id = contacts.id)-> Index Scan using index_users_on_contact_id on users (cost=0.28..504.68 rows=4739 width=16) (actual time=0.011..0.268 rows=416 loops=3)-> Index Only Scan using contacts_pkey on contacts (cost=0.28..42.88 rows=600 width=8) (actual time=0.024..0.270 rows=601 loops=3)Heap Fetches: 601-> Index Scan using idx_om_job_runs_tasks_task_id on om_job_runs_tasks ojrt (cost=0.42..0.50 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=585,433)

22. 1,756.302 1,756.302 ↑ 1.0 1 585,434

Index Cond: (task_id = om_task_assignees.task_id)-> Hash (cost=1572.97..1572.97 rows=64397 width=8) (actual time=71.234..71.234 rows=64200 loops=3)Buckets: 65536 Batches: 1 Memory Usage: 3020kB-> Seq Scan on om_job_runs ojr (cost=0.00..1572.97 rows=64397 width=8) (actual time=0.011..29.211 rows=64200 loops=3)-> Hash (cost=2581.67..2581.67 rows=64367 width=16) (actual time=73.210..73.210 rows=64200 loops=3)Buckets: 65536 Batches: 1 Memory Usage: 3522kB-> Seq Scan on om_jobs (cost=0.00..2581.67 rows=64367 width=16) (actual time=0.025..41.018 rows=64200 loops=3)-> Index Only Scan using om_tasks_pkey on om_tasks ot (cost=0.42..0.47 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=585,434)

23. 7,687.564 7,687.564 ↑ 1.2 263,297 1

Index Cond: (id = ojrt.task_id)Heap Fetches: 22716-> Hash (cost=306098.80..306098.80 rows=64367 width=40) (actual time=8071.886..8071.887 rows=6794 loops=1)Buckets: 65536 Batches: 2 Memory Usage: 805kB-> Subquery Scan on job_alphas (cost=302270.95..306098.80 rows=64367 width=40) (actual time=7913.412..8068.562 rows=6794 loops=1)-> GroupAggregate (cost=302270.95..305455.13 rows=64367 width=72) (actual time=7913.410..8065.583 rows=6794 loops=1)Group Key: om_jobs_1.id-> Sort (cost=302270.95..303064.15 rows=317278 width=16) (actual time=7913.372..7976.488 rows=263297 loops=1)Sort Key: om_jobs_1.idSort Method: external sort Disk: 6704kB-> Hash Join (cost=6,986.36..267,854.06 rows=317,278 width=16) (actual time=104.087..7,687.564 rows=263,297 loops=1)

24. 227.649 7,288.446 ↑ 1.0 314,053 1

Hash Cond: (requirement.abstract_asset_id = asset.id)-> Hash Join (cost=6768.51..263527.99 rows=317278 width=16) (actual time=103.937..7519.860 rows=314053 loops=1)Hash Cond: (job_run.id = om_jobs_1.latest_job_run_id)-> Hash Join (cost=3,382.26..255,778.61 rows=317,426 width=24) (actual time=52.550..7,288.446 rows=314,053 loops=1)

25. 0.000 7,060.797 ↑ 1.0 314,053 1

Hash Cond: (job_run_task.job_run_id = job_run.id)-> Merge Join (cost=1,004.32..252,567.38 rows=317,426 width=16) (actual time=5.869..7,060.797 rows=314,053 loops=1)

26. 5,490.445 7,938.518 ↑ 2.0 1 1,134,074

Merge Cond: (base_io_requiement.id = requirement.base_io_requirement_id)-> Gather Merge (cost=1001.76..2736033.88 rows=1135361 width=16) (actual time=5.844..6372.898 rows=1134076 loops=1)Workers Planned: 2Workers Launched: 2-> Nested Loop (cost=1.73..2603985.03 rows=473067 width=16) (actual time=0.065..5510.360 rows=378025 loops=3)-> Nested Loop (cost=1.31..2362021.54 rows=473982 width=16) (actual time=0.051..3768.237 rows=378025 loops=3)-> Parallel Index Only Scan using om_base_io_requirements_pkey on om_base_io_requirements base_io_requiement (cost=0.43..33965.05 rows=472026 width=8) (actual time=0.025..234.947 rows=378025 loops=3)Heap Fetches: 198370-> Bitmap Heap Scan on om_tasks task (cost=0.88..4.91 rows=2 width=24) (actual time=0.007..0.007 rows=1 loops=1,134,074)

27. 0.000 2,268.148 ↑ 1.0 1 1,134,074

Recheck Cond: ((base_io_requiement.id = input_requirement_id) OR (base_io_requiement.id = output_requirement_id))Heap Blocks: exact=203261-> BitmapOr (cost=0.88..0.88 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=1134074)-> Bitmap Index Scan on idx_om_tasks_input_requirement_id (cost=0.00..0.44 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1,134,074)

28. 2,268.148 2,268.148 ↑ 1.0 1 1,134,074

Index Cond: (base_io_requiement.id = input_requirement_id)-> Bitmap Index Scan on idx_om_tasks_output_requirement_id (cost=0.00..0.44 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1,134,074)

29. 3,402.222 3,402.222 ↑ 1.0 1 1,134,074

Index Cond: (base_io_requiement.id = output_requirement_id)-> Index Scan using idx_om_job_runs_tasks_task_id on om_job_runs_tasks job_run_task (cost=0.42..0.50 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=1,134,074)

30. 179.859 179.859 ↑ 1.0 314,053 1

Index Cond: (task_id = task.id)-> Index Scan using idx_om_requirements_base_io_requirement_id on om_requirements requirement (cost=0.42..19,777.88 rows=316,727 width=16) (actual time=0.019..179.859 rows=314,053 loops=1)

31. 0.066 0.066 ↑ 1.0 1 33

Filter: ((source_io_requirement_id IS NULL) AND ((dtype)::text = 'AssetRequirement'::text))Rows Removed by Filter: 41496-> Hash (cost=1572.97..1572.97 rows=64397 width=8) (actual time=46.584..46.585 rows=64200 loops=1)Buckets: 65536 Batches: 1 Memory Usage: 3020kB-> Seq Scan on om_job_runs job_run (cost=0.00..1572.97 rows=64397 width=8) (actual time=0.009..25.128 rows=64200 loops=1)-> Hash (cost=2581.67..2581.67 rows=64367 width=16) (actual time=51.298..51.299 rows=64200 loops=1)Buckets: 65536 Batches: 1 Memory Usage: 3522kB-> Seq Scan on om_jobs om_jobs_1 (cost=0.00..2581.67 rows=64367 width=16) (actual time=0.008..29.924 rows=64200 loops=1)-> Hash (cost=217.51..217.51 rows=27 width=16) (actual time=0.142..0.143 rows=32 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 10kB-> Nested Loop (cost=0.29..217.51 rows=27 width=16) (actual time=0.046..0.130 rows=32 loops=1)-> Seq Scan on om_abstract_assets asset (cost=0.00..1.27 rows=27 width=16) (actual time=0.022..0.031 rows=33 loops=1)-> Index Only Scan using alphas_pkey on alphas alpha (cost=0.29..8.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=33)

32. 235.275 235.275 ↓ 11.3 120,719 1

Index Cond: (id = asset.alpha_id)Heap Fetches: 32-> Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.032..0.032 rows=14 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 9kB-> Seq Scan on om_job_types job_type (cost=0.00..11.40 rows=140 width=524) (actual time=0.018..0.023 rows=14 loops=1)-> Hash (cost=6377.95..6377.95 rows=10674 width=40) (actual time=965.353..965.354 rows=64200 loops=1)Buckets: 32768 (originally 16384) Batches: 4 (originally 1) Memory Usage: 3841kB-> Subquery Scan on project_managers_group (cost=3683.68..6377.95 rows=10674 width=40) (actual time=197.042..922.927 rows=64200 loops=1)-> GroupAggregate (cost=3683.68..6271.21 rows=10674 width=104) (actual time=197.040..893.818 rows=64200 loops=1)Group Key: job_1.id-> Merge Join (cost=3683.68..6031.05 rows=10674 width=31) (actual time=196.978..350.544 rows=120719 loops=1)Merge Cond: (om_jobs_project_managers.job_id = job_1.id)-> Sort (cost=3,683.35..3,710.04 rows=10,674 width=31) (actual time=196.938..235.275 rows=120,719 loops=1)

33. 168.120 168.120 ↓ 7.3 66,379 1

Sort Key: om_jobs_project_managers.job_idSort Method: external sort Disk: 4968kB-> Hash Join (cost=99.50..2969.17 rows=10674 width=31) (actual time=1.210..95.793 rows=120719 loops=1)Hash Cond: (om_jobs_project_managers.user_id = users_1.id)-> Seq Scan on om_jobs_project_managers (cost=0.00..2309.13 rows=121013 width=16) (actual time=0.008..29.695 rows=120719 loops=1)-> Hash (cost=94.27..94.27 rows=418 width=23) (actual time=1.195..1.196 rows=415 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 33kB-> Merge Join (cost=0.56..94.27 rows=418 width=23) (actual time=0.037..1.034 rows=415 loops=1)Merge Cond: (contacts_1.id = users_1.contact_id)-> Index Scan using contacts_pkey on contacts contacts_1 (cost=0.28..42.88 rows=600 width=23) (actual time=0.020..0.257 rows=601 loops=1)-> Index Scan using index_users_on_contact_id on users users_1 (cost=0.28..504.68 rows=4739 width=16) (actual time=0.012..0.286 rows=416 loops=1)-> Index Only Scan using om_jobs_pkey on om_jobs job_1 (cost=0.29..2026.91 rows=64367 width=8) (actual time=0.031..23.752 rows=64200 loops=1)Heap Fetches: 10809-> Hash (cost=6696.39..6696.39 rows=9076 width=40) (actual time=623.573..623.573 rows=64196 loops=1)Buckets: 65536 (originally 16384) Batches: 2 (originally 1) Memory Usage: 3832kB-> Subquery Scan on client_managers_group (cost=4157.64..6696.39 rows=9076 width=40) (actual time=146.064..590.873 rows=64196 loops=1)-> GroupAggregate (cost=4157.64..6605.63 rows=9076 width=72) (actual time=146.063..560.922 rows=64196 loops=1)Group Key: job_2.id-> Merge Join (cost=4157.64..6401.42 rows=9076 width=80) (actual time=146.013..250.432 rows=66379 loops=1)Merge Cond: (om_jobs_client_managers.job_id = job_2.id)-> Sort (cost=4,157.31..4,180.00 rows=9,076 width=80) (actual time=145.970..168.120 rows=66,379 loops=1)

34. 128.398 128.398 ↑ 10.0 1 64,199

Sort Key: om_jobs_client_managers.job_idSort Method: external merge Disk: 3192kB-> Hash Join (cost=3036.41..3560.66 rows=9076 width=80) (actual time=76.044..99.816 rows=66379 loops=1)Hash Cond: (client_contacts.user_id = users_2.id)-> Seq Scan on client_contacts (cost=0.00..16.50 rows=650 width=72) (actual time=0.011..0.028 rows=43 loops=1)-> Hash (cost=1821.23..1821.23 rows=66174 width=24) (actual time=75.859..75.860 rows=66379 loops=1)Buckets: 65536 Batches: 2 Memory Usage: 3343kB-> Hash Join (cost=224.63..1821.23 rows=66174 width=24) (actual time=2.780..52.748 rows=66379 loops=1)Hash Cond: (om_jobs_client_managers.user_id = users_2.id)-> Seq Scan on om_jobs_client_managers (cost=0.00..1422.74 rows=66174 width=16) (actual time=0.008..16.651 rows=66379 loops=1)-> Hash (cost=165.39..165.39 rows=4739 width=8) (actual time=2.755..2.756 rows=4700 loops=1)Buckets: 8192 Batches: 1 Memory Usage: 248kB-> Seq Scan on users users_2 (cost=0.00..165.39 rows=4739 width=8) (actual time=0.006..1.434 rows=4700 loops=1)-> Index Only Scan using om_jobs_pkey on om_jobs job_2 (cost=0.29..2026.91 rows=64367 width=8) (actual time=0.034..22.883 rows=64200 loops=1)Heap Fetches: 10809-> Aggregate (cost=94.39..94.40 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=64199)-> Hash Join (cost=24.78..94.38 rows=1 width=23) (actual time=0.003..0.004 rows=1 loops=64199)Hash Cond: (users_3.contact_id = contacts_2.id)-> Index Scan using users_pkey on users users_3 (cost=0.28..69.86 rows=10 width=16) (actual time=0.002..0.002 rows=1 loops=64,199)

35. 64.199 64.199 ↓ 0.0 0 64,199

Index Cond: (id = ANY (job_assignees.assignees_user_id))-> Hash (cost=17.00..17.00 rows=600 width=23) (actual time=0.434..0.434 rows=601 loops=1)Buckets: 1024 Batches: 1 Memory Usage: 43kB-> Seq Scan on contacts contacts_2 (cost=0.00..17.00 rows=600 width=23) (actual time=0.011..0.213 rows=601 loops=1)-> Aggregate (cost=78.36..78.37 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=64199)-> Index Scan using alphas_pkey on alphas alpha_1 (cost=0.29..78.31 rows=10 width=37) (actual time=0.000..0.001 rows=0 loops=64,199)

36. 64.199 64.199 ↓ 0.0 0 64,199

Index Cond: (id = ANY (job_alphas.alphas_id))-> Index Scan using idx_om_job_bookmarks_job_id on om_job_bookmarks (cost=0.15..0.27 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=64,199)

Planning time : 37.331 ms
Execution time : 16,425.887 ms