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 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)
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset