Sample Header Ad - 728x90

slow index creation with gist and gist__int_ops

0 votes
3 answers
548 views
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?
Asked by Yosi (175 rep)
Dec 8, 2019, 03:42 PM
Last activity: Jun 6, 2022, 03:47 PM