From: Justin Pryzby <pryzby@telsasoft.com>
To: Fahiz Mohamed <fahiz@netwidz.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Specific query taking time to process
Date: Sun, 8 Dec 2019 22:13:07 -0600
Message-ID: <20191209041307.GW2082@telsasoft.com> (raw)
In-Reply-To: <d8d95b54-9e85-4137-8ff2-7589b121e17d@Spark>
References: <6bd9b9b7-f127-4e48-a456-b3bbcfb741ab@Spark>
<d8d95b54-9e85-4137-8ff2-7589b121e17d@Spark>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, fahiz@netwidz.com, pgsql-performance@lists.postgresql.org
Subject: Re: Specific query taking time to process
In-Reply-To: <20191209041307.GW2082@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox