Search Postgresql Archives

Re: Suddenly all queries moved to seq scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

      

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux