slow index creation with gist and gist__int_ops I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array.

The gin index was added since we have query of "X exists in array?" in high profile endpoint.

I created a one-time replica of the production DB and removed the index and replaced it with GIST and gist__int_ops to check it's performance (since it should work better on frequently updated /inserts table).

The gist index creation never finishes, I let it run for hours and no results on the other side, gin index creation takes few minutes.

I tried cleaning the data - I had arrays with 50 to 120 items, and I removed them and now all of my data is an array with one element and still, index creation is slow.

DB: PostgreSQL 11.5 on RDS  (db.m4.4xlarge)
Table size: 9373 MB

Any ideas what I can do here?
I have also had this problem, and have never found a solution, other than "don't use gist__int_ops".  The picksplit or penalty function seems to go pathologically wrong on large tables.  I don't know if this is a bug someone could fix with enough effort, or if it is an inherent problem.


  (since it should work better on frequently updated /inserts table)


I don't know how good this advice actually is (or ever was).  Note that it was removed from the docs in version 9.5.  But if you can never get the build to finish anyway, then surely it can't be good advice for your situation.