From: Rondat Flyag <rondatflyag@yandex.ru>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Queries containing ORDER BY and LIMIT started to work slowly
Date: Fri, 01 Sep 2023 16:41:13 +0300
Message-ID: <1282241693574959@mail.yandex.ru> (raw)
In-Reply-To: <CAApHDvpUhYcXOZ6Popb95GubAUEGnnvdCZP-aSY-USEtRuwpPg@mail.gmail.com>
References: <32431693330715@mail.yandex.ru>
<CAMkU=1w+RgLQVkqLudpfsax5HQgY94WFmSH+RfydmsN55a_QGQ@mail.gmail.com>
<2854111693335271@mail.yandex.ru>
<CAMkU=1yX2TtLw+Fcb=ZSYWmGvY-Mx1LOAU67VTryZLMcor6gRA@mail.gmail.com>
<203321693416472@mail.yandex.ru>
<CAApHDvpUhYcXOZ6Popb95GubAUEGnnvdCZP-aSY-USEtRuwpPg@mail.gmail.com>
<div>Hi David.</div><div> </div><div>Thank you so much for your help. The problem was in the dropped asins_statistics(asin_id) index. I had set it, but it was dropped somehow during the dump. I set it again andeverything works fine now.</div><div>Thank you again.</div><div> </div><div>P.S. There are two close terms: ASIN and ISBN. I use ASIN in my tables, but ISBN is well-known to people. I changed ASIN to ISBN in the text files, but forgot to replace the last time.This is why the names didn't correspond.</div><div> </div><div>Cheers,</div><div>Serg</div><div> </div><div>31.08.2023, 00:43, "David Rowley" <dgrowleyml@gmail.com>:</div><blockquote><p>On Thu, 31 Aug 2023 at 06:32, Rondat Flyag <<a href="mailto:rondatflyag@yandex.ru" rel="noopener noreferrer">rondatflyag@yandex.ru</a>> wrote:</p><blockquote> 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.<br /><br /> Please see the attached query plans.</blockquote><p><br />It's a little hard to comment here as I don't see what the plan was<br />before when you were happy with the performance. I also see the<br />queries you mentioned in the initial email don't match the plans.<br />There's no table called "isbns" in the query. I guess this is "asins"?<br /><br />Likely you could get a faster plan if there was an index on<br />asins_statistics (asin_id). That would allow a query plan that scans<br />the isbns_value_key index and performs a parameterised nested loop on<br />asins_statistics using the asins_statistics (asin_id) index. Looking<br />at your schema, I don't see that index, so it's pretty hard to guess<br />why the plan used to be faster. Even if the books/asins merge join<br />used to take place first, there'd have been no efficient way to join<br />to the asins_statistics table and preserve the Merge Join's order (I'm<br />assuming non-parameterized nested loops would be inefficient in this<br />case). Doing that would have also required the asins_statistics<br />(asin_id) index. Are you sure that index wasn't dropped?<br /><br />However, likely it's a waste of time to try to figure out what the<br />plan used to be. Better to focus on trying to make it faster. I<br />suggest you create the asins_statistics (asin_id) index. However, I<br />can't say with any level of confidence that the planner would opt to<br />use that index if it did exist. Lowering random_page_cost or<br />increasing effective_cache_size would increase the chances of that.<br /><br />David</p></blockquote>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rondatflyag@yandex.ru, dgrowleyml@gmail.com, jeff.janes@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Queries containing ORDER BY and LIMIT started to work slowly
In-Reply-To: <1282241693574959@mail.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox