opencue_find_jobs_by_show_original.sql

/*EXPLAIN ANALYZE*/
SELECT rank, pk_job, int_priority, ts_started FROM ( 
    SELECT 
        ROW_NUMBER() OVER (ORDER BY job_resource.int_priority DESC, job.ts_started ASC) AS rank, 
        job.pk_job, 
        job_resource.int_priority,
        job.ts_started
    FROM 
        job            , 
        job_resource   , 
        folder         , 
        folder_resource, 
        point          , 
        layer          , 
        layer_stat     , 
        host             
    WHERE 
        job.pk_job                 = job_resource.pk_job 
        AND job.pk_folder          = folder.pk_folder 
        AND folder.pk_folder       = folder_resource.pk_folder 
        AND folder.pk_dept         = point.pk_dept 
        AND folder.pk_show         = point.pk_show 
        AND job.pk_job             = layer.pk_job 
        AND job_resource.pk_job    = job.pk_job 
        AND (CASE WHEN layer_stat.int_waiting_count > 0 THEN layer_stat.pk_layer ELSE NULL END) = layer.pk_layer 
        AND 
            (
                folder_resource.int_max_cores = -1 
            OR 
                folder_resource.int_cores < folder_resource.int_max_cores 
            ) 
        AND 
            (
                folder_resource.int_max_gpus = -1 
                OR 
                folder_resource.int_gpus < folder_resource.int_max_gpus 
            ) 
        AND job.str_state                  = 'PENDING' 
        AND job.b_paused                   = false 
        AND job.pk_show                    = '00000000-0000-0000-0000-000000000000'
        AND job.pk_facility                = '00ea22fa-61c0-4d52-8168-91fbbb4ef379'
        AND 
            (
                job.str_os IS NULL OR job.str_os = '' 
            OR 
                job.str_os = 'linux'
            ) 
        AND (CASE WHEN layer_stat.int_waiting_count > 0 THEN 1 ELSE NULL END) = 1 
        AND layer.int_cores_min            <= 1000
        AND layer.int_mem_min              <= 113355443
        AND (CASE WHEN layer.b_threadable = true THEN 1 ELSE 0 END) >= 0
        AND layer.int_gpus_min  <= 0
        AND layer.int_gpu_mem_min BETWEEN 0 AND 1
        AND 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
        AND host.str_tags ~* ('(?x)' || layer.str_tags)
        AND host.str_name       = 'host'
        AND layer.pk_layer IN (
            SELECT 
                l.pk_layer 
            FROM 
                layer l 
            LEFT JOIN layer_limit ON layer_limit.pk_layer = l.pk_layer 
            LEFT JOIN limit_record ON limit_record.pk_limit_record = layer_limit.pk_limit_record 
            LEFT JOIN (
                SELECT 
                    limit_record.pk_limit_record, 
                    SUM(layer_stat.int_running_count) AS int_sum_running 
                FROM 
                    layer_limit 
                LEFT JOIN limit_record ON layer_limit.pk_limit_record = limit_record.pk_limit_record 
                LEFT JOIN layer_stat ON layer_stat.pk_layer = layer_limit.pk_layer 
                GROUP BY limit_record.pk_limit_record) AS sum_running 
            ON limit_record.pk_limit_record = sum_running.pk_limit_record 
            WHERE 
                sum_running.int_sum_running < limit_record.int_max_value 
                OR sum_running.int_sum_running IS NULL 
        ) 
) AS t1 WHERE rank <= 500;