The GIN index can be used to index arrays; standard PostgreSQL distributions have the GIN operator class for one-dimensional arrays. The GIN index is supported for the following operators:
- The contains operator @>
- The is contained by operator <@
- The overlapping operator &&
- The equality operators =
The following code shows how to create an Index on color column using GIN function :
CREATE INDEX ON color USING GIN (color);
The index function can be tested by using the following code:
car_portal=> SET enable_seqscan TO off; -- To force index scan
SET
car_portal=> EXPLAIN SELECT * FROM color WHERE '{red}'::text[] && color;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on color (cost=8.00..12.01 rows=1 width=32)
Recheck Cond: ('{red}'::text[] && color)
-> Bitmap Index Scan on color_color_idx (cost=0.00..8.00 rows=1 width=0)
Index Cond: ('{red}'::text[] && color)
(4 rows)