PostgreSQL 9.1 index over arrays

Question

What index will enhance database performance for table

CREATE TABLE my_table (
    word_set text[]
)

for requests with WHERE my_table.word_set @> '<some word>'

Check [this topic][1], same sort of issue. [1]: http://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns/4059785#4059785 — Frank Heikens, Jul 25 '12 at 11:40

score 2 · Accepted Answer · answered Jul 25 '12 at 11:43

CREATE INDEX idx_test on my_table USING GIN (word_set);

Check EXPLAIN ANALYZE to see if the index is used. Turn enable_seqscan off when your table is almost empty or doesn't have enough unique data. Having an index doesn't mean the database will always use that index: Using an index might be slower than a sequential disk scan. It all depends.

PostgreSQL 9.1 index over arrays

1 Answers1