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
thetime 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+
secondsto 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