CODEWITHSUNDEEP
postgresqlindexing
FranGoitia
FranGoitia

Reputation: 1993

How to index a varchar array in Postgresql using array_to_string?

I'm looking to set an index in a varchar array column in postgresql 9.3. I was told to set it using array_to_string(col) but I don't really understand how this works. I came up with the following statement:

CREATE INDEX CONCURRENTLY rtb_id_search ON sites USING GIN(array_to_string(rtb_id, ''));

However, postgresql complains with:

 ERROR:  functions in index expression must be marked IMMUTABLE

Upvotes: 0

Views: 3140

Answers (2)

FranGoitia
FranGoitia

Reputation: 1993

create function string_array_to_string(text[], text, text) returns text as $$
   select array_to_string($1, $2, $3)
$$ language sql cost 1 immutable;

create index concurrently sites_rtb_ids on sites using gin (string_array_to_string(rtb_ids, ' ', ' ') gin_trgm_ops);

This is the way to create the index. The function used needs to be marked immutable.

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45835

What operations do you would to accelerate? GIN indes supports array directly:

create table foo(a text[]);
create index on foo using gin (a);
set enable_seqscan to off;

There can be some issues because not all array operators are supported by index. But almost it is.

 postgres=# explain select * from foo where a @> ARRAY['a'];
┌────────────────────────────────────────────────────────────────────────┐
│                               QUERY PLAN                               │
╞════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on foo  (cost=8.05..18.20 rows=7 width=32)            │
│   Recheck Cond: (a @> '{a}'::text[])                                   │
│   ->  Bitmap Index Scan on foo_a_idx  (cost=0.00..8.05 rows=7 width=0) │
│         Index Cond: (a @> '{a}'::text[])                               │
└────────────────────────────────────────────────────────────────────────┘
(4 rows)

Upvotes: 2

Related Questions