Re: Specific query taking time to process

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