5

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

But I only managed to create a BTREE index so far:

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

which have no support for array operations (@>, <@ and &&).

I've tried to use GIN and GiST (using btree_gin and btree_gist extensions), but I found no way to use both scalar and array columns in the same index.

It looks like GIN doesn't 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.

while GiST does't 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 by using to_tsvector function to convert all scalar strings to tsvector datatype. But I don't need full text search here. I even tried to create my own operator class, but quickly realized that it's beyond me.

Is there any way to create multicolumn GIN/GiST index, containing both scalar strings and arrays?

Leonid Beschastny
  • 50,364
  • 10
  • 118
  • 122

1 Answers1

6

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

Run once per database:

CREATE EXTENSION btree_gin;  -- or btree_gist

Then you should be able to create your multicolumn index:

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

See:

As for indexes on array types: GIN is the perfect index type for those. The manual:

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

Bold emphasis mine. The operators @>, <@ and && are defined for various data types. Some of them cooperate with GiST indexes as well. But with arrays as operands, it's typically GIN indexes. See:

The data type character is most probably not what you want. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Just as I said, `btree_gist` only adds support for ordinary data types. Try to create multicolumn gist index with an array field and you'll see exactly the same exception I mentioned in my question. – Leonid Beschastny Aug 11 '15 at 16:13
  • @LeonidBeschastny: Ah, I see. No GiST index for array types. Why would you want that to begin with? Use a GIN index for those. Index usage is bound to the operators used: http://stackoverflow.com/a/29245753/939860. If that's still unclear I suggest you start a new question with the details of your use case. – Erwin Brandstetter Aug 11 '15 at 16:34