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
29.08.2023, 23:11, "Jeff Janes" <jeff.janes@xxxxxxxxx>:
On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag <rondatflyag@xxxxxxxxx> 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
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=766983.18..767070.52 rows=100 width=498) (actual time=5508.261..5508.532 rows=100 loops=1)
Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
-> Merge Join (cost=766983.18..2008284.16 rows=1421289 width=498) (actual time=5508.260..5508.527 rows=100 loops=1)
Merge Cond: ((books.asin)::text = (asins.value)::text)
Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
-> Index Scan using books_asin_key on books (cost=0.43..1216522.35 rows=1403453 width=333) (actual time=0.007..0.150 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=766980.48..774092.68 rows=1422439 width=155) (actual time=5508.248..5508.304 rows=100 loops=1)
Buffers: shared hit=30146 read=342473, temp read=16856 written=28392
-> Sort (cost=766980.48..770536.58 rows=1422439 width=155) (actual time=5508.245..5508.293 rows=100 loops=1)
Sort Key: asins.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=30146 read=342473, temp read=16856 written=28392
-> Hash Join (cost=55734.25..509782.68 rows=1422439 width=155) (actual time=412.394..2071.400 rows=1404582 loops=1)
Hash Cond: (asins_statistics.asin_id = asins.id)
Buffers: shared hit=30146 read=342473, temp read=11281 written=11279
-> Seq Scan on asins_statistics (cost=0.00..373686.39 rows=1422439 width=120) (actual time=0.005..782.893 rows=1404582 loops=1)
Buffers: shared hit=16989 read=342473
-> Hash (cost=27202.89..27202.89 rows=1404589 width=35) (actual time=412.025..412.026 rows=1404589 loops=1)
Buckets: 1048576 Batches: 2 Memory Usage: 51393kB
Buffers: shared hit=13157, temp written=4363
-> Seq Scan on asins (cost=0.00..27202.89 rows=1404589 width=35) (actual time=0.010..151.754 rows=1404589 loops=1)
Buffers: shared hit=13157
Planning time: 0.770 ms
Execution time: 5525.959 ms
(25 rows)
SET enable_seqscan = OFF;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10001314403.06..10001314490.39 rows=100 width=498) (actual time=6171.243..6171.489 rows=100 loops=1)
Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
-> Merge Join (cost=10001314403.06..10002555704.04 rows=1421289 width=498) (actual time=6171.241..6171.478 rows=100 loops=1)
Merge Cond: ((books.asin)::text = (asins.value)::text)
Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
-> Index Scan using books_asin_key on books (cost=0.43..1216522.35 rows=1403453 width=333) (actual time=0.019..0.144 rows=100 loops=1)
Buffers: shared hit=103
-> Materialize (cost=10001314400.36..10001321512.55 rows=1422439 width=155) (actual time=6171.213..6171.265 rows=100 loops=1)
Buffers: shared hit=1030630 read=346050, temp read=22327 written=34044
-> Sort (cost=10001314400.36..10001317956.46 rows=1422439 width=155) (actual time=6171.133..6171.178 rows=100 loops=1)
Sort Key: asins.value
Sort Method: external merge Disk: 136864kB
Buffers: shared hit=1030630 read=346050, temp read=22327 written=34044
-> Hash Join (cost=10000416471.30..10001057202.55 rows=1422439 width=155) (actual time=1074.832..2719.409 rows=1404582 loops=1)
Hash Cond: (asins.id = asins_statistics.asin_id)
Buffers: shared hit=1030630 read=346050, temp read=16937 written=16931
-> Index Scan using isbns_pkey on asins (cost=0.43..574466.58 rows=1404589 width=35) (actual time=0.012..688.506 rows=1404589 loops=1)
Buffers: shared hit=1015561 read=1657
-> Hash (cost=10000373686.39..10000373686.39 rows=1422439 width=120) (actual time=1065.611..1065.611 rows=1404582 loops=1)
Buckets: 524288 Batches: 4 Memory Usage: 35931kB
Buffers: shared hit=15069 read=344393, temp written=10377
-> Seq Scan on asins_statistics (cost=10000000000.00..10000373686.39 rows=1422439 width=120) (actual time=0.025..795.542 rows=1404582 loops=1)
Buffers: shared hit=15069 read=344393
Planning time: 1.128 ms
Execution time: 6189.046 ms
(25 rows)
SET enable_sort = OFF;
Query takes a lot of time.
