Re: Suddenly all queries moved to seq scan

On Wed, Nov 20, 2024 at 9:09 AM Sreejith P <sreejith(at)lifetrenz(dot)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 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.