Suppose a postgresql table items like this. (keywords column is of type text[])

name   account_id      keywords
foo1   1               ['k1', 'k2']
foo2   1               ['k1', 'k3']
foo3   2               ['k4', 'k1']
foo4   2               ['k1', 'k6']

Each row in items is relate to a Account (the table is virtually splitted by account_id). We want make queries like: "items for account 1 with keyword k1". This query requires a composite GIN index on account_id and keywords column.

Actually, we need an inverted index, with rows like this: (the key for each row should be composite)

(account_id, keyword) --> [item1, item2, ...]

What's the right way to create this index in postgresql?


The answer is same as the answer for this question: Inner join using an array column

The main point is installing the btree_gin extension (this may require additional package, like postgresql-contrib)

Then, the extension can be enabled easily with: (requires postgresql >= 9.1)


And index can be created with:

CREATE INDEX index_name ON items USING GIN (account_id, keywords);

| improve this answer | |

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