Hi team,
Could you please help me with this strange issue I am facing in my current live server I am maintaining.
There is a specific search query I am running to get list of Documents and their metadata from several table in the DB.
We are running Postgres 9.6.9 on Amazon RDS (db.m5.4xlarge instance)
Our current DB consists of 500GB of data and indexes. Most of the rows in table are consist of 454,078,915
With the fresh DB with the restore of the DATA without any indexes Search query performs relatively quick and most of the time its less than a second.
But after 3 weeks of use of the DB it sudenly started to slowdown only for this perticular query and it takes 20+ seconds to respond. If I do a restore the DB again then it continues to work fine and the symptom pops out after 3 weeks time.
I am just suspecting is there any cache or index maxing out causes this issue?
Could you please guide me what can it be the root cause of this issue?
Thank you,
Fahiz
