Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ie65E-000766-2y
	for pgsql-performance@arkaria.postgresql.org; Sun, 08 Dec 2019 23:35:24 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1ie64E-0002Ib-OL
	for pgsql-performance@arkaria.postgresql.org; Sun, 08 Dec 2019 23:34:22 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <fahiz@netwidz.com>)
	id 1idgLv-0008BS-7s
	for pgsql-performance@lists.postgresql.org; Sat, 07 Dec 2019 20:06:55 +0000
Received: from mail-wm1-x336.google.com ([2a00:1450:4864:20::336])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128)
	(Exim 4.92)
	(envelope-from <fahiz@netwidz.com>)
	id 1idgLq-0003rn-Ik
	for pgsql-performance@lists.postgresql.org; Sat, 07 Dec 2019 20:06:53 +0000
Received: by mail-wm1-x336.google.com with SMTP id q9so11428742wmj.5
        for <pgsql-performance@lists.postgresql.org>;
 Sat, 07 Dec 2019 12:06:50 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=netwidz-com.20150623.gappssmtp.com; s=20150623;
        h=date:from:to:message-id:in-reply-to:references:subject:mime-version;
        bh=Nem//H/ONf0b+zzVfJj4fUSCMhmZu/Prg9fb6cc/7Ro=;
        b=Podgo5Cgesvvz/r3yTZfw9aOkcX/fKWiNrjKrQO3lYOGs/xxoYWqFX1Q+zC1fiHU8J
         TlgvFOHd7gWMOggb4fAJVfsHHMJOD+9aB7TB/d5FxkMQlo4Ct1hmevoFyjjK/fgL0L/L
         v6UNdcVD1thficBlseoO8iFpTMQACSq88OmGFETQQJtFemPySIe3akTBhH+u2xtpufW4
         Bwd58ZWxOVRy/RKmhNwrztiKA3PYThZN7xLgT1ua7CLwqORieAvkZ3n3lZyS+62moZ+0
         zAKhfUHpNoKXzBI0HE2nXYk+ffVlnEE5fg0EZ5cE5JIXfYDmrDV+SbH6Snntfz+dOhJk
         kUlw==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:date:from:to:message-id:in-reply-to:references
         :subject:mime-version;
        bh=Nem//H/ONf0b+zzVfJj4fUSCMhmZu/Prg9fb6cc/7Ro=;
        b=uNCRmE6XurzLSVyFaAXNJccFhqN5foMEmgcc57Bblo6uj0JvXLCXadUn0VIVvF5tCs
         Nt0WKq5rzCIHuIJTdMV3xM4USR2WA5DbaXQ2akP91X0BLhWAvOE/wHzJPyOWL3JszSOZ
         4Q9FfH0RfMVMl75jAae1BZZoxRp7oR+Y1oh8v0YOJTbBJtK7JmauCcJTGtiEuchRaWDe
         tnlmqfNXpZnKI7PZCTsO36jNmGtVRZTLZcp6MiGfEzopyR7qc1I+61CUVl7x3CujjGh+
         sbTiPH9fNsuKJN16fQGuLB9ib91yl0ZTIizz/+/9muy/WQqkpkgRYH1+JTlHl29+03cO
         Sozg==
X-Gm-Message-State: APjAAAVxjYkfXBna6fDW7Nv70H5lfblrfmU95IJZqNf8/IqZXbq8qCAM
	U0vi27uf7qAS/otFVO2k81Qqj1yK7ds=
X-Google-Smtp-Source: 
 APXvYqwMleVJ+jUnyFQDgBgJN23KRHbFHN1D4/6svHHmw0Lo4Lwx0uqjvk94TuRqchCrXjTvaiQoXg==
X-Received: by 2002:a05:600c:21c6:: with SMTP id
 x6mr16191053wmj.177.1575749207749;
        Sat, 07 Dec 2019 12:06:47 -0800 (PST)
Received: from Fahizs-MacBook-Pro.local ([81.134.202.29])
        by smtp.gmail.com with ESMTPSA id
 w188sm8073458wmg.32.2019.12.07.12.06.46
        for <pgsql-performance@lists.postgresql.org>
        (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128);
        Sat, 07 Dec 2019 12:06:46 -0800 (PST)
Date: Sat, 7 Dec 2019 20:05:59 +0000
From: Fahiz Mohamed <fahiz@netwidz.com>
To: pgsql-performance@lists.postgresql.org
Message-ID: <d8d95b54-9e85-4137-8ff2-7589b121e17d@Spark>
In-Reply-To: <6bd9b9b7-f127-4e48-a456-b3bbcfb741ab@Spark>
References: <6bd9b9b7-f127-4e48-a456-b3bbcfb741ab@Spark>
Subject: Specific query taking time to process
X-Readdle-Message-ID: d8d95b54-9e85-4137-8ff2-7589b121e17d@Spark
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="5dec062c_5072367_168"
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

--5dec062c_5072367_168
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi team,

Could you please help me with this strange issue I am facing in my curren=
t live server I am maintaining.

There is a specific search query I am running to get list of Documents an=
d 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=C2=A0454,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 sec=
ond.

But after 3 weeks of use of the DB it sudenly started to slowdown only fo=
r this perticular query and it takes 20+ seconds to respond. If I do a re=
store the DB again then it continues to work fine and the symptom pops ou=
t after 3 weeks time.

I am just suspecting is there any cache or index maxing out causes this i=
ssue=3F

Could you please guide me what can it be the root cause of this issue=3F


Thank you,
=46ahiz


--5dec062c_5072367_168
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hi team,
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>Could you please help me with this strange issue I =
am facing in my current live server I am maintaining.</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>There is a specific search query I am running to ge=
t list of Documents and their metadata from several table in the DB.</div=
>
<div dir=3D=22auto=22>We are running Postgres 9.6.9 on Amazon RDS (<span =
style=3D=22color: rgb(209, 210, 211); font-family: Monaco, Menlo, Consola=
s, &quot;Courier New&quot;, monospace; font-size: 12px; orphans: 2; white=
-space: pre-wrap; widows: 2; background: rgba(var(--sk=5Fforeground=5Fmin=
,29,28,29),0.04);=22>db.m5.4xlarge instance)</span></div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>Our current DB consists of 500GB of data and indexe=
s. Most of the rows in table are consist of&=23160;<span style=3D=22color=
: rgb(209, 210, 211); font-family: Slack-Lato, appleLogo, sans-serif; fon=
t-size: 15px; orphans: 2; white-space: pre-wrap; widows: 2; background-co=
lor: rgb(26, 29, 33);=22>454,078,915</span></div>
</div>
</div>
<div name=3D=22messageSignatureSection=22><br />
<div dir=3D=22auto=22>With the fresh DB with the restore of the DATA with=
out any indexes Search query performs relatively quick and most of the ti=
me its less than a second.&=23160;</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>But after 3 weeks of use of the DB it sudenly start=
ed 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 a=
nd the symptom pops out after 3 weeks time.&=23160;</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>I am just suspecting is there any cache or index ma=
xing out causes this issue=3F</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>Could you please guide me what can it be the root c=
ause of this issue=3F</div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22><br /></div>
<div dir=3D=22auto=22>Thank you,</div>
<div dir=3D=22auto=22>=46ahiz</div>
<div dir=3D=22auto=22><br /></div>
</div>
</body>
</html>

--5dec062c_5072367_168--