0

Newbie to full text search, I have a feed_items table as follows

feed_items (...title varchar, summary varchar, fullarticle varchar,...)

I want to create a ts_vector column to search query from either the title or the summary with higher priority given if the query is found in the title.

Option 1 I can create a generated column which goes like this

ALTER TABLE feed_items
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(summary, ''))) STORED;

Option 2 I can create a trigger after insert or update like this, the example below is not perfect, I created a separate column for title_vector and summary_vector but I think I need to merge them into one column and yet decide how to give more importance to query found in title

ALTER TABLE "feed_items"
  ADD IF NOT EXISTS "title_vector" tsvector;
COMMENT ON COLUMN "feed_items"."title_vector" IS $pga$vector representation of title$pga$;
UPDATE feed_items SET title_vector = to_tsvector('english', title);
CREATE INDEX "idx_title_vector" ON "feed_items" USING gin ("title_vector");
CREATE TRIGGER "trigger_update_title_vector"
  BEFORE INSERT OR UPDATE ON "feed_items"
  FOR EACH ROW
  EXECUTE PROCEDURE "tsvector_update_trigger"($pga$title_vector$pga$, $pga$pg_catalog.english$pga$, $pga$title$pga$);

My main question is what is the difference between both? and which one should I use?

2
3

The trigger method is obsolete. If you are using a version that supports using generated columns on expressions, then you should use them. And if you are starting a new project, you should absolutely start out with a version at least that new (v12).

2

I think both ways are not ideal, since they persist unnecessary data.

I would not add an extra column, but define a GIN index on the expression:

CREATE INDEX ON feed_items USING gin (
   to_tsvector('english', concat(title, ' ', summary))
);

concat treats NULL values as empty strings, so that is simpler.

Your full-text search will then have

... WHERE to_tsvector('english', concat(title, ' ', summary)) @@ whatever
2
  • what is the overhead to compute them on the fly vs storing them?
    – PirateApp
    Dec 3 '21 at 14:08
  • 1
    A few computations of the expression per index scan. Dec 3 '21 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.