Learning PostgreSQL 11 - Third Edition

Indexing arrays

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 the color column using the 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 scanSETcar_portal=> EXPLAIN SELECT * FROM color WHERE '{red}'::text[] && color;                                  QUERY PLAN ------------------------------------------------------------------------------ Bitmap ...