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

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