Gin indexing in postgres

Question

I have four columns project_id, custom_event(boolean), page_view(boolean), event_url(text). I usually do like a regex operation in event_url column and my query will be all the four columns in that order I specified. When I try to create like this

CREATE INDEX event_url_index ON event_data
  USING gin (project_id, custom_event, page_view, event_url gin_trgm_ops)

it's giving me an error like this

data type boolean has no default operator class for access method "gin"

Can anyone help me how to solve this issue?

Answer 1

As the other answer says, this likely won't work well even when it is supported (which starts in v11 for bool). A four column GIN index is really not much different from 4 single-column GIN indexes. And the boolean columns are unlikely to be helpful on their own, as returning half the index is probably going to cost more than it helps. By combining the three scalars into one btree index, that gives you the good shot at maximizing the selectivity and minimizing the overhead (and it works in v10). If the distribution of bools are lopsided, it might help to create partial indexes where you only index rows which the rarer bool value.

Another possibility is to use a GiST index instead of GIN

CREATE INDEX event_url_index ON event_data
  USING gist (project_id, custom_event, page_view, event_url gist_trgm_ops)

Multi-column GiST indexes can combine the columns in a very targeted useful way. However, signature based GiST indexes can also degenerate very badly when they get over-filled, and it is hard to predict when this will happen. So you have to test them on your real dataset. You might also want to try different ordering (putting the bools last, for example, or just leaving them out altogether).

Answer 2

To create a GIN index on a boolean column, you'd have to

CREATE EXTENSION btree_gin;

But that index is pretty pointless, since PostgreSQL cannot use a combined GIN index like this for trigram matching on the string and the other conditions at the same time.

If the other conditions narrow down the search sufficiently, consider creating two indexes: a trigram index on the string column, and a regular B-tree index on the other columns. PostgreSQL can combine the two indexes using a BitmapAnd.