mirror of
https://github.com/ClusterCockpit/cc-backend
synced 2026-03-11 02:27:30 +01:00
20 lines
1.6 KiB
SQL
20 lines
1.6 KiB
SQL
-- Expression indexes on footprint JSON fields for WHERE and ORDER BY optimization.
|
|
-- SQLite matches expressions textually, so queries must use exactly:
|
|
-- json_extract(footprint, '$.field')
|
|
|
|
-- Standalone expression indexes (for filtering and sorting)
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_flops_any_avg ON job (json_extract(footprint, '$.flops_any_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_mem_bw_avg ON job (json_extract(footprint, '$.mem_bw_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_mem_used_max ON job (json_extract(footprint, '$.mem_used_max'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_cpu_load_avg ON job (json_extract(footprint, '$.cpu_load_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_net_bw_avg ON job (json_extract(footprint, '$.net_bw_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_net_data_vol_total ON job (json_extract(footprint, '$.net_data_vol_total'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_file_bw_avg ON job (json_extract(footprint, '$.file_bw_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_fp_file_data_vol_total ON job (json_extract(footprint, '$.file_data_vol_total'));
|
|
|
|
-- Composite indexes with cluster (for common filter+sort combinations)
|
|
CREATE INDEX IF NOT EXISTS jobs_cluster_fp_cpu_load_avg ON job (cluster, json_extract(footprint, '$.cpu_load_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_cluster_fp_flops_any_avg ON job (cluster, json_extract(footprint, '$.flops_any_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_cluster_fp_mem_bw_avg ON job (cluster, json_extract(footprint, '$.mem_bw_avg'));
|
|
CREATE INDEX IF NOT EXISTS jobs_cluster_fp_mem_used_max ON job (cluster, json_extract(footprint, '$.mem_used_max'));
|