Skip to content

Instantly share code, notes, and snippets.

@splhack

splhack/opencue_find_jobs_by_show_original.analyze.txt

Created November 30, 2021 04:29
Show Gist options
  • Save splhack/bd7c5221acb43e095dffb3e8ddafd472 to your computer and use it in GitHub Desktop.
Save splhack/bd7c5221acb43e095dffb3e8ddafd472 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
opencue_find_jobs_by_show_original.analyze.txt
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on t1 (cost=1054.66..1054.69 rows=1 width=57) (actual time=245.933..249.048 rows=500 loops=1)
Filter: (t1.rank <= 500)
Rows Removed by Filter: 6835
-> WindowAgg (cost=1054.66..1054.68 rows=1 width=57) (actual time=245.932..248.679 rows=7335 loops=1)
-> Sort (cost=1054.66..1054.66 rows=1 width=49) (actual time=245.920..246.306 rows=7335 loops=1)
Sort Key: job_resource.int_priority DESC, job.ts_started
Sort Method: quicksort Memory: 1224kB
-> Nested Loop Semi Join (cost=732.09..1054.65 rows=1 width=49) (actual time=9.360..241.127 rows=7335 loops=1)
Join Filter: ((layer.pk_layer)::text = (l.pk_layer)::text)
-> Nested Loop (cost=1.27..320.45 rows=1 width=131) (actual time=0.091..140.927 rows=7335 loops=1)
Join Filter: ((host.str_tags)::text ~* ('(?x)'::text || (layer.str_tags)::text))
-> Nested Loop (cost=1.27..318.43 rows=1 width=139) (actual time=0.075..114.076 rows=7335 loops=1)
-> Nested Loop (cost=0.99..317.65 rows=1 width=94) (actual time=0.064..83.614 rows=7335 loops=1)
Join Filter: (((job_resource.int_cores + layer.int_cores_min) < (job_resource.int_max_cores + 1000)) AND ((job_resource.int_gpus + layer.int_gpus_min) < (job_resource.int_max_gpus + 1000)))
-> Nested Loop (cost=0.71..317.16 rows=1 width=106) (actual time=0.052..51.917 rows=7335 loops=1)
Join Filter: ((job.pk_folder)::text = (folder_resource.pk_folder)::text)
-> Nested Loop (cost=0.71..316.13 rows=1 width=233) (actual time=0.047..38.775 rows=7335 loops=1)
-> Nested Loop (cost=0.43..315.71 rows=1 width=172) (actual time=0.037..9.389 rows=7335 loops=1)
-> Nested Loop (cost=0.14..9.18 rows=1 width=90) (actual time=0.021..0.025 rows=1 loops=1)
-> Seq Scan on point (cost=0.00..1.01 rows=1 width=74) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using i_folder_pkshow on folder (cost=0.14..8.16 rows=1 width=270) (actual time=0.009..0.011 rows=1 loops=1)
Index Cond: ((pk_show)::text = (point.pk_show)::text)
Filter: ((point.pk_dept)::text = (pk_dept)::text)
-> Index Scan using i_job_pkgroup on job (cost=0.28..233.21 rows=7331 width=82) (actual time=0.015..8.195 rows=7335 loops=1)
Index Cond: ((pk_folder)::text = (folder.pk_folder)::text)
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)))
-> Index Scan using c_job_resource_uk on job_resource (cost=0.28..0.42 rows=1 width=61) (actual time=0.003..0.003 rows=1 loops=7335)
Index Cond: ((pk_job)::text = (job.pk_job)::text)
-> Seq Scan on folder_resource (cost=0.00..1.02 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=7335)
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.46 rows=1 width=98) (actual time=0.004..0.004 rows=1 loops=7335)
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))
-> 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=7335)
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=7335)
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.013..0.013 rows=1 loops=7335)
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=7335)
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: 453
-> Nested Loop Left Join (cost=730.53..733.83 rows=1 width=73) (actual time=0.009..0.009 rows=1 loops=7335)
-> 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=7335)
Index Cond: ((pk_layer)::text = (l.pk_layer)::text)
-> Hash Right Join (cost=730.25..733.41 rows=1 width=126) (actual time=0.006..0.006 rows=1 loops=7335)
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.002..0.002 rows=1 loops=7335)
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=3.082..7.720 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.839 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.606 rows=7335 loops=1)
-> Hash (cost=11.20..11.20 rows=120 width=90) (actual time=0.003..0.004 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.001..0.002 rows=1 loops=1)
-> Hash (cost=269.64..269.64 rows=7264 width=45) (actual time=3.047..3.048 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.795 rows=7335 loops=1)
-> Hash (cost=0.17..0.17 rows=1 width=94) (actual time=0.002..0.002 rows=1 loops=7335)
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=7335)
Index Cond: ((pk_limit_record)::text = (layer_limit.pk_limit_record)::text)
Planning Time: 4.054 ms
Execution Time: 249.285 ms
(67 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment