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.
