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

Hello Jeff.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. Cheers,Serg 31.08.2023, 19:52, "Jeff Janes" :On Wed, Aug 30, 2023 at 1:31 PM

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

Hi David. 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.Thank you again. P.S. There are two close terms: ASIN and ISBN. I use ASIN in my tables,

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

On Wed, Aug 30, 2023 at 1:31 PM Rondat Flyag 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

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

On Thu, 31 Aug 2023 at 06:32, Rondat Flyag wrote: > 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

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

Thanks for the response.Sure, I thought about it and even bought another drive. The current drive is SSD, as far as I'm concerned write operations degrade SSDs. Even so, why other queries work fine? Why the query joining two tables instead of three works fine? Cheers,Serg 30.08.2023, 00:07, "Rick

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 

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

On Tue, Aug 29, 2023 at 3:57 PM Rondat Flyag wrote: > I took the dump just to store it on another storage (external HDD). I > didn't do anything with it. > > 29.08.2023, 21:42, "Jeff Janes" : > > > > On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag > wrote: > > I have a legacy system that uses

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

On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag 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

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

I took the dump just to store it on another storage (external HDD). I didn't do anything with it. 29.08.2023, 21:42, "Jeff Janes" :  On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag wrote:I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. Everything was fine

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

On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag wrote: > I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. > Everything was fine several days ago even with standard Postgresql > settings. I dumped a database with the compression option (maximum > compression level -Z 9) in order

Queries containing ORDER BY and LIMIT started to work slowly

I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. Everything was fine several days ago even with standard Postgresql settings. I dumped a database with the compression option (maximum compression level -Z 9) in order to have a smaller size (`pg_dump --compress=9 database_name >