Postgres full text search indexing across multiple columns

Question

I'm interesting in applying an index for querying across multiple columns using Postgres full text search.

According to the docs, I could apply a multi-column index like so:

CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));

but I'm confused as to when this index would actually be used. Would the to_tsvector function used in the WHERE clause have to be in precisely the same format as what was used in the index?

SELECT title FROM pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('something')

Would the following queries still make use of the index?:

SELECT title FROM pgweb WHERE to_tsvector(coalesce(title,'') || ' ' || coalesce(body,'')) @@ to_tsquery('something')

SELECT title FROM pgweb WHERE to_tsvector(body) @@ to_tsquery('something')

jjanes · Accepted Answer · 2020-01-10 01:14:04Z

Would the to_tsvector function used in the WHERE clause have to be in precisely the same format as what was used in the index?

Yes. It will ignore things like whitespace differences and extraneous parentheses. But other than that, they must be the same.

Would the following queries still make use of the index?:

No.

Note that what you have here is expression index, where the expression references multiple columns. It is not the same thing as a multi-column index.

Collectives™ on Stack Overflow

Postgres full text search indexing across multiple columns

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related