0

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?

1

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.

| improve this answer | |
0

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.

| improve this answer | |
  • 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 '19 at 21:05

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.