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

On Wed, Aug 30, 2023 at 1:31 PM Rondat Flyag <rondatflyag(at)yandex(dot)ru> wrote:

> 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.
>

Maybe you could restore (to a temp server, not the production) a physical
backup taken from before the change happened, and get an old plan that
way. I'm guessing that somehow an index got dropped around the same time
you took the dump. That might be a lot of work, and maybe it would just be
easier to optimize the current query while ignoring the past. But you
seem to be interested in a root-cause analysis, and I don't see any other
way to do one of those.

What I would expect to be the winning plan would be something sort-free
like:

Limit
merge join
index scan yielding books in asin order (already being done)
nested loop
index scan yielding asins in value order
index scan probing asins_statistics driven
by asins_statistics.asin_id = asins.id

Or possibly a 2nd nested loop rather than the merge join just below the
limit, but with the rest the same

In addition to the "books" index already evident in your current plan, you
would also need an index leading with asins_statistics.asin_id, and one
leading with asins.value. But if all those indexes exists, it is hard to
see why setting enable_seqscan=off wouldn't have forced them to be used.

Cheers,

Jeff