-1

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?

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).

0

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.

  • I have installed btree_gin still i'm getting the same error, we can't use boolean column for gin indexing it seems. I will go with the BitmapAnd method. Thanks – NAREN PUSHPARAJU Jun 6 at 7:43
  • That cannot be. What PostgreSQL version is that? Are you sure you installed the extension in the same database (\dx). – Laurenz Albe Jun 6 at 8:40
  • Yeah sure. My postgreSQL version is 10.5 – NAREN PUSHPARAJU Jun 6 at 10:31
  • It worked for me on v11. But since the idea won't work anyway, and you said you are willing to try the approach with two indexes, I won't check if something changed here since v10. – Laurenz Albe Jun 6 at 10:57
  • Yep fine. BitmapAnd method will work in v10 right? – NAREN PUSHPARAJU Jun 6 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.