Creating a multi-column index in PostgreSQL containing both scalar and massive columns

Question

To optimize the complex PostgreSQL query, I tried to create an index containing both scalar strings and arrays and supporting array operations ( @> , <@ and && ).

But I managed to create the BTREE index:

 CREATE INDEX idx1 ON "MyTable" USING btree ("Char_1", "Array_1", "Array_2", "Array_3", "Char_2"); 

which do not support array operations ( @> , <@ and && ).

I tried using GIN and GiST (using the extensions btree_gin and btree_gist ), but did not find a way to use both scalar and massive columns in the same index.

GIN doesn't seem to support scalars:

 ERROR: data type character has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data type. 

and GiST does not support arrays:

 ERROR: data type character varying[] has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. 

The only way I found to create such an index is to use the to_tsvector function to convert all scalar strings to a tsvector data type. But I do not need a full text search. I even tried to create my own operator class, but quickly realized that it was outside of me.

Is there a way to create a multi- GIN / GiST index containing both scalar strings and arrays?

Answer 1

You need to install the optional module btree_gin or btree_gist respectively, which provide the missing operator classes.

Run once for each database:

 CREATE EXTENSION btree_gin; -- or btree_gist 

Then you can create your multi-column index:

 CREATE INDEX idx1 ON "MyTable" USING gin ("Varchar_1", "Array_1", "Array_2", "Array_3", "Varchar_2"); 

More details:

• 3-column multi-column index with heterogeneous data types

As for indexes in array types: GIN is the ideal index type for them. Documentation:

GIN indices are inverted indices that can handle values ​​that contain more than one key, arrays, for example .

My bold accent. The operators @> , <@ and && defined for different data types. Some of them also work with GiST indices. But with arrays as operands always GIN AFAIC indices.

I wrote a related answer on dba.SE only today, with requests for a list of related operators:

• Use the built-in PostgreSQL <@ operator after enabling the intarray extension

And the character data type is most likely not what you want:

• Any disadvantages of using the text data type to store strings?