Skip to content

Instantly share code, notes, and snippets.

@splhack

splhack/opencue_find_jobs_by_show_optimized.analyze.txt

Created November 30, 2021 04:30
Show Gist options
  • Save splhack/f93f4e784649a94bd9fdfc50129a9349 to your computer and use it in GitHub Desktop.
Save splhack/f93f4e784649a94bd9fdfc50129a9349 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
opencue_find_jobs_by_show_optimized.analyze.txt
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2208.55..2463.59 rows=1 width=57) (actual time=22.030..36.003 rows=500 loops=1)
-> Nested Loop Semi Join (cost=2208.55..2463.59 rows=1 width=57) (actual time=22.028..35.937 rows=500 loops=1)
Join Filter: ((layer.pk_layer)::text = (l.pk_layer)::text)
-> Nested Loop (cost=1477.74..1729.39 rows=1 width=139) (actual time=13.377..21.092 rows=500 loops=1)
Join Filter: ((host.str_tags)::text ~* ('(?x)'::text || (layer.str_tags)::text))
-> Nested Loop (cost=1477.74..1727.37 rows=1 width=147) (actual time=13.339..19.231 rows=500 loops=1)
-> Nested Loop (cost=1477.45..1726.60 rows=1 width=102) (actual time=13.328..17.074 rows=500 loops=1)
-> Nested Loop (cost=1477.17..1726.11 rows=1 width=77) (actual time=13.311..14.785 rows=500 loops=1)
Join Filter: ((job.pk_folder)::text = (folder_resource.pk_folder)::text)
-> Hash Join (cost=1477.17..1725.08 rows=1 width=204) (actual time=13.302..13.911 rows=500 loops=1)
Hash Cond: ((job.pk_folder)::text = (folder.pk_folder)::text)
-> WindowAgg (cost=1467.97..1614.59 rows=7331 width=114) (actual time=13.273..13.684 rows=500 loops=1)
-> Sort (cost=1467.97..1486.30 rows=7331 width=106) (actual time=13.265..13.326 rows=501 loops=1)
Sort Key: job_resource.int_priority DESC, job.ts_started
Sort Method: quicksort Memory: 1224kB
-> Hash Join (cost=339.04..997.33 rows=7331 width=106) (actual time=3.264..9.675 rows=7335 loops=1)
Hash Cond: ((job.pk_job)::text = (job_resource.pk_job)::text)
-> Seq Scan on job (cost=0.00..639.04 rows=7331 width=82) (actual time=0.005..4.306 rows=7335 loops=1)
Filter: ((NOT b_paused) AND ((str_state)::text = 'PENDING'::text) AND ((pk_show)::text = '00000000-0000-0000-0000-000000000000'::text) AND ((pk_facility)::text = '00ea22fa-61c0-4d52-8168-91fbbb4ef379'::text) AND ((str_os IS NULL) OR ((str_os)::text = ''::text) OR ((str_os)::text = 'linux'::text)))
-> Hash (cost=247.35..247.35 rows=7335 width=61) (actual time=3.243..3.244 rows=7335 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 759kB
-> Seq Scan on job_resource (cost=0.00..247.35 rows=7335 width=61) (actual time=0.005..1.734 rows=7335 loops=1)
-> Hash (cost=9.18..9.18 rows=1 width=90) (actual time=0.022..0.024 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.14..9.18 rows=1 width=90) (actual time=0.018..0.021 rows=1 loops=1)
-> Seq Scan on point (cost=0.00..1.01 rows=1 width=74) (actual time=0.009..0.009 rows=1 loops=1)
-> Index Scan using i_folder_pkshow on folder (cost=0.14..8.16 rows=1 width=270) (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: ((pk_show)::text = (point.pk_show)::text)
Filter: ((point.pk_dept)::text = (pk_dept)::text)
-> Seq Scan on folder_resource (cost=0.00..1.02 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=500)
Filter: (((int_max_cores = '-1'::integer) OR (int_cores < int_max_cores)) AND ((int_max_gpus = '-1'::integer) OR (int_gpus < int_max_gpus)))
-> Index Scan using i_layer_pkjob on layer (cost=0.28..0.47 rows=1 width=98) (actual time=0.004..0.004 rows=1 loops=500)
Index Cond: ((pk_job)::text = (job.pk_job)::text)
Filter: ((int_cores_min <= 1000) AND (int_mem_min <= 113355443) AND (CASE WHEN b_threadable THEN 1 ELSE 0 END >= 0) AND (int_gpus_min <= 0) AND (int_gpu_mem_min >= 0) AND (int_gpu_mem_min <= 1) AND ((job_resource.int_cores + int_cores_min) < job_resource.int_max_cores) AND ((job_resource.int_gpus + int_gpus_min) < job_resource.int_max_gpus))
-> Index Scan using i_layerstat_int_waiting_count on layer_stat (cost=0.28..0.77 rows=1 width=45) (actual time=0.003..0.004 rows=1 loops=500)
Index Cond: ((CASE WHEN (int_waiting_count > 0) THEN 1 ELSE NULL::integer END = 1) AND ((CASE WHEN (int_waiting_count > 0) THEN pk_layer ELSE NULL::character varying END)::text = (layer.pk_layer)::text))
-> Seq Scan on host (cost=0.00..2.00 rows=1 width=111) (actual time=0.001..0.001 rows=1 loops=500)
Filter: ((str_name)::text = '2401:db00:eef0:1120:3520:0:2001:9e16'::text)
-> Nested Loop Left Join (cost=730.81..734.19 rows=1 width=37) (actual time=0.029..0.029 rows=1 loops=500)
Filter: (((sum(layer_stat_1.int_running_count)) < (limit_record.int_max_value)::numeric) OR ((sum(layer_stat_1.int_running_count)) IS NULL))
-> Index Only Scan using c_layer_pk on layer l (cost=0.29..0.35 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=500)
Index Cond: (pk_layer = (CASE WHEN (layer_stat.int_waiting_count > 0) THEN layer_stat.pk_layer ELSE NULL::character varying END)::text)
Heap Fetches: 33
-> Nested Loop Left Join (cost=730.53..733.83 rows=1 width=73) (actual time=0.025..0.025 rows=1 loops=500)
-> Index Scan using i_layer_limit_pk_layer on layer_limit (cost=0.28..0.41 rows=1 width=74) (actual time=0.003..0.003 rows=1 loops=500)
Index Cond: ((pk_layer)::text = (l.pk_layer)::text)
-> Hash Right Join (cost=730.25..733.41 rows=1 width=126) (actual time=0.021..0.021 rows=1 loops=500)
Hash Cond: ((limit_record_1.pk_limit_record)::text = (limit_record.pk_limit_record)::text)
-> HashAggregate (cost=730.07..731.57 rows=120 width=122) (actual time=0.018..0.018 rows=1 loops=500)
Group Key: limit_record_1.pk_limit_record
Batches: 1 Memory Usage: 40kB
-> Hash Left Join (cost=373.14..693.43 rows=7328 width=98) (actual time=2.846..7.233 rows=7335 loops=1)
Hash Cond: ((layer_limit_1.pk_layer)::text = (layer_stat_1.pk_layer)::text)
-> Hash Left Join (cost=12.70..313.74 rows=7328 width=127) (actual time=0.016..2.690 rows=7335 loops=1)
Hash Cond: ((layer_limit_1.pk_limit_record)::text = (limit_record_1.pk_limit_record)::text)
-> Seq Scan on layer_limit layer_limit_1 (cost=0.00..200.28 rows=7328 width=74) (actual time=0.005..0.605 rows=7335 loops=1)
-> Hash (cost=11.20..11.20 rows=120 width=90) (actual time=0.004..0.005 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on limit_record limit_record_1 (cost=0.00..11.20 rows=120 width=90) (actual time=0.003..0.003 rows=1 loops=1)
-> Hash (cost=269.64..269.64 rows=7264 width=45) (actual time=2.814..2.815 rows=7335 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 616kB
-> Seq Scan on layer_stat layer_stat_1 (cost=0.00..269.64 rows=7264 width=45) (actual time=0.005..1.533 rows=7335 loops=1)
-> Hash (cost=0.17..0.17 rows=1 width=94) (actual time=0.002..0.002 rows=1 loops=500)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using i_limit_record_pk_limit_record on limit_record (cost=0.14..0.17 rows=1 width=94) (actual time=0.001..0.001 rows=1 loops=500)
Index Cond: ((pk_limit_record)::text = (layer_limit.pk_limit_record)::text)
Planning Time: 2.784 ms
Execution Time: 36.217 ms
(68 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment