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?

3 Answers 3


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.


The most likely explanation is that there is a concurrent session that has some lock on the table and is idle in transaction. Check pg_stat_activity and pg_locks to find the session at fault and pg_terminate_backend to kill it.

Since this is a database newly created from a dump, it is very unlikely that you are suffering from data corruption. And for a table with less than 10GB, hours would be way too long, even on a hosted database.

  • I can't have locks since the only user connected to this DB is me and I am running this query. Other users don't have access to this DB
    – Yosi
    Dec 8, 2019 at 21:05

I was able to get around this by creating the index with empty values in the column and then filling the data after, but it still took around 20 hours with a table of only 14 million rows. I'm not sure how viable this would be for a production environment but I wanted to run performance tests on GiST vs GIN.

  1. Copy the the data from the column you're creating the index on to a new temporary column
  2. Clear the data from the original column by dropping and recreating, or setting it to '{}'::int[]
  3. Create the GiST index
  4. Run and update to move the data back to the original column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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