0

In our application we have a field that can be used to match with several columns in our database. So if the user searches for somestring we will return all the rows WHERE column1 = 'somestring' OR column2 = 'somestring' etc. We are using Postgres.

I am wondering if it is viable to use GIN index for this purpose. Normally i see GIN indexes used for long and variable-length data, however. So I am wondering there is any point using them to index short, fixed length tsvectors or should I just index each column separately with a BTREE and let the planner handle the search.

Improve this question

1 Answer 1

Reset to default
2

You can, with the aid of the btree_gin standard extension. But there likely is not much point, as normal btree indexes on each column separately can also be used for this, using a BitmapOr planner node.

duplicate value compression is better in GIN indexes than in btree indexes, so if each column has many duplicate values the GIN index might be smaller than the sum of each btree index.

Improve this answer
2
  • The question said that the index is on tsvectors, although it was not totally clear.
    – Laurenz Albe
    Commented Jan 19 at 7:38
  • @LaurenzAlbe I thought he mentioned that just because he didn't know about btree_gin and so thought he needed tsvector as a workaround.
    – jjanes
    Commented Jan 19 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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