DROP FUNCTION IF EXISTS strip_html(text);
ALTER TABLE jobs ADD COLUMN IF NOT EXISTS search_vector tsvector;
DROP INDEX IF EXISTS idx_jobs_search_vector;
DROP INDEX IF EXISTS idx_jobs_fts_active;
CREATE INDEX IF NOT EXISTS idx_jobs_fts_active ON jobs USING GIN (search_vector) WITH (fastupdate = on) WHERE is_active = true;
CREATE OR REPLACE FUNCTION jobs_search_vector_update() RETURNS trigger AS $$ BEGIN NEW.search_vector := setweight(to_tsvector('english', left(coalesce(NEW.title, ''), 500)), 'A') || setweight(to_tsvector('english', left(coalesce(NEW.company_name, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(NEW.location, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(NEW.description, ''), 2000)), 'C'); RETURN NEW; END; $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_jobs_search_vector ON jobs;
CREATE TRIGGER trg_jobs_search_vector BEFORE INSERT OR UPDATE ON jobs FOR EACH ROW EXECUTE FUNCTION jobs_search_vector_update();
ALTER TABLE kenyan_jobs ADD COLUMN IF NOT EXISTS search_vector tsvector;
DROP INDEX IF EXISTS idx_kenyan_jobs_search_vector;
DROP INDEX IF EXISTS idx_kenyan_jobs_fts_active;
CREATE INDEX IF NOT EXISTS idx_kenyan_jobs_fts_active ON kenyan_jobs USING GIN (search_vector) WITH (fastupdate = on) WHERE is_active = true;
CREATE OR REPLACE FUNCTION kenyan_jobs_search_vector_update() RETURNS trigger AS $$ BEGIN NEW.search_vector := setweight(to_tsvector('english', left(coalesce(NEW.title, ''), 500)), 'A') || setweight(to_tsvector('english', left(coalesce(NEW.company_name, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(NEW.location, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(NEW.description, ''), 2000)), 'C'); RETURN NEW; END; $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_kenyan_jobs_search_vector ON kenyan_jobs;
CREATE TRIGGER trg_kenyan_jobs_search_vector BEFORE INSERT OR UPDATE ON kenyan_jobs FOR EACH ROW EXECUTE FUNCTION kenyan_jobs_search_vector_update();
DO $$ DECLARE rows_updated int; last_id uuid := '00000000-0000-0000-0000-000000000000'; next_id uuid; BEGIN LOOP SELECT id INTO next_id FROM (SELECT id FROM jobs WHERE id > last_id ORDER BY id LIMIT 2000) sub ORDER BY id DESC LIMIT 1; EXIT WHEN next_id IS NULL; UPDATE jobs SET search_vector = setweight(to_tsvector('english', left(coalesce(title, ''), 500)), 'A') || setweight(to_tsvector('english', left(coalesce(company_name, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(location, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(description, ''), 2000)), 'C') WHERE id > last_id AND id <= next_id; GET DIAGNOSTICS rows_updated = ROW_COUNT; RAISE NOTICE 'jobs - batch updated: %', rows_updated; last_id := next_id; END LOOP; END $$;
DO $$ DECLARE rows_updated int; last_id uuid := '00000000-0000-0000-0000-000000000000'; next_id uuid; BEGIN LOOP SELECT id INTO next_id FROM (SELECT id FROM kenyan_jobs WHERE id > last_id ORDER BY id LIMIT 2000) sub ORDER BY id DESC LIMIT 1; EXIT WHEN next_id IS NULL; UPDATE kenyan_jobs SET search_vector = setweight(to_tsvector('english', left(coalesce(title, ''), 500)), 'A') || setweight(to_tsvector('english', left(coalesce(company_name, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(location, ''), 200)), 'B') || setweight(to_tsvector('english', left(coalesce(description, ''), 2000)), 'C') WHERE id > last_id AND id <= next_id; GET DIAGNOSTICS rows_updated = ROW_COUNT; RAISE NOTICE 'kenyan_jobs - batch updated: %', rows_updated; last_id := next_id; END LOOP; END $$;
VACUUM ANALYZE jobs;
VACUUM ANALYZE kenyan_jobs;
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();
