MIME-Version: 1.0
References: <CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com>
 <B63DBDEA-950A-4CE3-85C4-7FBF6385FB6A@yesql.se> <C1F257F9-4CFE-4CC8-A6BB-E66F150CEF8D@lifetrenz.com>
In-Reply-To: <C1F257F9-4CFE-4CC8-A6BB-E66F150CEF8D@lifetrenz.com>
From: Justin <zzzzz.graf@gmail.com>
Date: Wed, 20 Nov 2024 12:28:15 -0500
Message-ID: <CALL-XeNFLbEE8=__jL-SvN2C7+VbZ1YOjr2krD+QwCY6k_we3w@mail.gmail.com>
Subject: Re: Suddenly all queries moved to seq scan
To: Sreejith P <sreejith@lifetrenz.com>
Cc: Daniel Gustafsson <daniel@yesql.se>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000042460006275b7ab9"
Archived-At: <https://www.postgresql.org/message-id/CALL-XeNFLbEE8%3D__jL-SvN2C7%2BVbZ1YOjr2krD%2BQwCY6k_we3w%40mail.gmail.com>
Precedence: bulk

--00000000000042460006275b7ab9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Nov 20, 2024 at 9:09=E2=80=AFAM Sreejith P <sreejith@lifetrenz.com>=
 wrote:

>
>
>
> Queries were taking 20 ms started taking 60 seconds. So have done SQL
> analyse to understand about query plan. There we found that query planner
> taking seq scan instead in index scan.
>
> I would like to add one ore point.  A delete query were running in DB fro=
m
> 2 days for deleting around 80 million records.


This can cause this specific problem where the number of dead tuples and
lack of autovacuum running can cause the statistics to favor a sequential
scan over an index scan.  Taking into account the length of time the delete
took it would hold a number of datapages and tuples in a lock state, which
can lead to blocking queries and prevent autovacuum/analyze.

It is best to do bulk deletes in batches and have a rest period between
batches to allow autovacuum and analyze to keep up..  Doing  deletes in
batches reduces the number of resources being consumed.

--00000000000042460006275b7ab9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Wed, Nov 20, 2024 at 9:09=E2=80=AF=
AM Sreejith P &lt;<a href=3D"mailto:sreejith@lifetrenz.com">sreejith@lifetr=
enz.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><br><br>
<br>
Queries were taking 20 ms started taking 60 seconds. So have done SQL analy=
se to understand about query plan. There we found that query planner taking=
 seq scan instead in index scan.<br>
<br>
I would like to add one ore point.=C2=A0 A delete query were running in DB =
from 2 days for deleting around 80 million records.</blockquote><div><br></=
div><div>This can cause this specific problem where the number of dead tupl=
es and lack of autovacuum running can cause the statistics to favor a seque=
ntial scan over an index scan.=C2=A0 Taking into account the length of time=
 the delete took it would hold a number of datapages and tuples in a lock s=
tate, which can lead to blocking queries and prevent autovacuum/analyze.=C2=
=A0<br><br>It is best to do bulk deletes in batches and have a rest period =
between=C2=A0 batches to allow autovacuum and analyze to keep up..=C2=A0 Do=
ing=C2=A0 deletes in batches reduces the number of resources being consumed=
.<br><br>=C2=A0 =C2=A0 =C2=A0=C2=A0</div></div></div>

--00000000000042460006275b7ab9--