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.
You haven't been quite clear on the situation and your use case, but assuming this table has 454 million rows and experiences updates/deletes then this sounds like you may be having problems with autovacuum. Have you customized parameters to ensure it is running more frequently than default? How are you doing those data restores? Perhaps that process is cleaning up the accumulated bloat and you can run fine again for a while. Check pg_stat_user_tables for the last (auto)vacuum that ran, assuming you didn't just restore again and are expecting the issue to occur again soon.
