spb/datacenterindex

1-- Extensions + search/geo indexes2CREATE EXTENSION IF NOT EXISTS pg_trgm;3CREATE EXTENSION IF NOT EXISTS unaccent;4--> statement-breakpoint5ALTER TABLE facilities ADD COLUMN IF NOT EXISTS search tsvector6  GENERATED ALWAYS AS (7    setweight(to_tsvector('simple'::regconfig, coalesce(name,'')), 'A') ||8    setweight(to_tsvector('simple'::regconfig, coalesce(city,'') || ' ' || coalesce(region_name,'') || ' ' || coalesce(address,'')), 'B') ||9    setweight(to_tsvector('simple'::regconfig, coalesce(description,'')), 'C')10  ) STORED;11--> statement-breakpoint12CREATE INDEX IF NOT EXISTS facilities_search_idx ON facilities USING gin (search);13--> statement-breakpoint14CREATE INDEX IF NOT EXISTS facilities_name_trgm_idx ON facilities USING gin (name gin_trgm_ops);15--> statement-breakpoint16CREATE INDEX IF NOT EXISTS facilities_city_trgm_idx ON facilities USING gin (city gin_trgm_ops);17--> statement-breakpoint18CREATE INDEX IF NOT EXISTS operators_name_trgm_idx ON operators USING gin (name gin_trgm_ops);19--> statement-breakpoint20CREATE INDEX IF NOT EXISTS metros_name_trgm_idx ON metros USING gin (name gin_trgm_ops);21--> statement-breakpoint22CREATE INDEX IF NOT EXISTS projects_name_trgm_idx ON projects USING gin (name gin_trgm_ops);23--> statement-breakpoint24CREATE INDEX IF NOT EXISTS facilities_latlng_idx ON facilities (lat, lng) WHERE lat IS NOT NULL;25--> statement-breakpoint26CREATE INDEX IF NOT EXISTS facilities_live_idx ON facilities (status, country_iso2) WHERE merged_into IS NULL;27