<div>Hello Jeff.</div><div>Thank you too for your efforts and help. The problem was in the dropped index for asins_statistics(asin_id). It existed, but was dropped during the dump I suppose. I created it again and everything is fine now.</div><div> </div><div>Cheers,</div><div>Serg</div><div> </div><div>31.08.2023, 19:52, "Jeff Janes" <jeff(dot)janes(at)gmail(dot)com>:</div><blockquote><div><div>On Wed, Aug 30, 2023 at 1:31 PM Rondat Flyag <<a href="mailto:rondatflyag(at)yandex(dot)ru" rel="noopener noreferrer">rondatflyag(at)yandex(dot)ru</a>> wrote:</div><div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><div>Hi and thank you for the response.</div><div> </div><div>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.</div></blockquote><div> </div><div>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.</div><div> </div><div>What I would expect to be the winning plan would be something sort-free like:</div><div> </div><div>Limit</div><div> merge join</div><div> index scan yielding books in asin order (already being done)</div><div> nested loop</div><div> index scan yielding asins in value order</div><div> index scan probing asins_statistics driven by asins_statistics.asin_id = <a href="http://asins.id/" rel="noopener noreferrer">asins.id</a></div><div> </div><div>Or possibly a 2nd nested loop rather than the merge join just below the limit, but with the rest the same</div><div> </div><div>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.</div><div> </div><div> Cheers,</div><div> </div><div>Jeff</div></div></div></blockquote>