docs/migrations/V_optimize_indexes.sql

DROP INDEX IF EXISTS idx_kenyan_jobs_fts_active;
DROP INDEX IF EXISTS idx_kenyan_jobs_active_sorted_id;
DROP INDEX IF EXISTS idx_kenyan_jobs_active_sorted;
DROP INDEX IF EXISTS idx_kenyan_seniority_sorted;
DROP INDEX IF EXISTS idx_kenyan_category_sorted;
DROP INDEX IF EXISTS idx_kenyan_experience_sorted;
DROP INDEX IF EXISTS idx_kenyan_jobs_source_active;
DROP INDEX IF EXISTS idx_jobs_active_sorted;
DROP INDEX IF EXISTS idx_jobs_active_remote_sorted;
DROP INDEX IF EXISTS idx_jobs_run_id;
DROP INDEX IF EXISTS idx_jobs_category_sorted;
DROP INDEX IF EXISTS idx_jobs_fts_active;
CREATE INDEX idx_jobs_fts_active ON jobs USING GIN (search_vector) WITH (fastupdate = on);

DROP INDEX IF EXISTS idx_jobs_active_sorted_id;
CREATE INDEX idx_jobs_active_sorted_id ON jobs (sorted_at DESC, id DESC);
DROP INDEX IF EXISTS idx_jobs_active_remote_sorted_id;
CREATE INDEX idx_jobs_active_remote_sorted_id ON jobs (remote, sorted_at DESC, id DESC);
DROP INDEX IF EXISTS idx_jobs_experience_sorted;
CREATE INDEX idx_jobs_experience_sorted ON jobs (experience_level, sorted_at DESC, id DESC);
DROP INDEX IF EXISTS idx_jobs_seniority_sorted;
CREATE INDEX idx_jobs_seniority_sorted ON jobs (seniority, sorted_at DESC, id DESC);
DROP INDEX IF EXISTS idx_jobs_cat_lower_sorted;
CREATE INDEX idx_jobs_cat_lower_sorted ON jobs (lower(job_category), sorted_at DESC, id DESC);
DROP INDEX IF EXISTS idx_jobs_active_run_id;
CREATE INDEX idx_jobs_active_run_id ON jobs (run_id);

-- Optimize countries filter
ALTER TABLE jobs ALTER COLUMN countries TYPE jsonb USING countries::jsonb;
CREATE INDEX IF NOT EXISTS idx_jobs_countries ON jobs USING GIN (countries jsonb_ops);

ALTER TABLE kenyan_jobs ALTER COLUMN countries TYPE jsonb USING countries::jsonb;
CREATE INDEX IF NOT EXISTS idx_kj_countries ON kenyan_jobs USING GIN (countries jsonb_ops);