Re: Queries containing ORDER BY and LIMIT started to work slowly

Hi and thank you for the response.

I tried VACUUM ANALYZE for three tables, but without success. I also tried to set enable_seqscan=off and the query took even more time. If I set enable_sort=off then the query takes a lot of time and I cancel it.

Please see the attached query plans.

Cheers,

Serg

29.08.2023, 23:11, "Jeff Janes" <jeff(dot)janes(at)gmail(dot)com>:

On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag <rondatflyag(at)yandex(dot)ru> wrote:




I took the dump just to store it on another storage (external HDD). I didn't do anything with it.



 


I don't see how that could cause the problem, it is probably just a coincidence.  Maybe taking the dump held a long-lived snapshot open which caused some bloat.   But if that was enough to push your system over the edge, it was probably too close to the edge to start with.


 


Do you have a plan for the query while it was fast?  If not, maybe you can force it back to the old plan by setting enable_seqscan=off or perhaps enable_sort=off, to let you capture the old plan for comparison.


 


The estimate for the seq scan of  isbns_statistics is off by almost a factor of 2.  A seq scan with no filters and which can not stop early should not be hard to estimate accurately, so this suggests autovac is not keeping up.  VACUUM ANALYZE all of the involved tables and see if that fixes things.


 


Cheers,


 


Jeff