public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin <[email protected]>
To: Sreejith P <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: [email protected]
Subject: Re: Suddenly all queries moved to seq scan
Date: Wed, 20 Nov 2024 12:28:15 -0500
Message-ID: <CALL-XeNFLbEE8=__jL-SvN2C7+VbZ1YOjr2krD+QwCY6k_we3w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAOW_W8PZ7Op_vOT9szMnpZE4jOmP09NWfc3M1ORTFMzjebpK5w@mail.gmail.com>
<[email protected]>
<[email protected]>
On Wed, Nov 20, 2024 at 9:09 AM Sreejith P <[email protected]> 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 from
> 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.
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Suddenly all queries moved to seq scan
In-Reply-To: <CALL-XeNFLbEE8=__jL-SvN2C7+VbZ1YOjr2krD+QwCY6k_we3w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox