On Sat, Dec 07, 2019 at 08:05:59PM +0000, Fahiz Mohamed wrote: > 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 454M rows or ?? > 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? https://wiki.postgresql.org/wiki/Slow_Query_Questions Could you send explain ANALYZE (attach here as txt attachment or link on depesz) now and compared with shortly after a restore ? Justin
