Fulltext index on fact__profile

fact__profile.sql

-- Column: vectors type of tsvector

ALTER TABLE profiles.fact__profile ADD COLUMN vectors tsvector;

-- Fill column: vectors (fulltext index)

UPDATE profiles.fact__profile
  SET tsvector = to_tsvector('english', identifier || ' ' || title);

-- Trigger: ts_vectors on profiles.fact__profile (keep fulltext index)

CREATE TRIGGER ts_vectors
  BEFORE INSERT OR UPDATE
  ON profiles.fact__profile
  FOR EACH ROW
  EXECUTE PROCEDURE tsvector_update_trigger('vectors', 'pg_catalog.english', 'identifier', 'title');

-- Index: profiles.fact__profile_vectors_idx

CREATE INDEX fact__profile_vectors_idx
  ON profiles.fact__profile
  USING gin
  (vectors);


-- Example: WHERE through 2 columns + LIKE - cca 5000 ms

SELECT *
  FROM profiles.fact__profile
  WHERE identifier = 'Red Bulletin' OR title ILIKE '%Red Bulletin%'

-- Example: fulltext search - cca 250 ms

SELECT *
  FROM profiles.fact__profile
  WHERE vectors @@ to_tsquery('english', 'Red+Bulletin:*')