On Wed, Nov 20, 2024 at 9:09 AM Sreejith P <sreej...@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 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.
