| 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(); |
|
|
