Closed
Description
Describe the bug
When running we can observe the performance degrade with time.
Here's the sql of nexmark q4
CREATE MATERIALIZED VIEW nexmark_q4
AS
SELECT Q.category,
AVG(Q.final) as avg
FROM (SELECT MAX(B.price) AS final,
A.category
FROM auction A,
bid B
WHERE A.id = B.auction
AND B.date_time BETWEEN A.date_time AND A.expires
GROUP BY A.id, A.category) Q
GROUP BY Q.category;
The performance decreasing happens on the join fragment.
The auction field of Bid has fixed access patterns instead of random accessing. It only be the recent auction ids. So the cache miss rate should not be high. Ideally the performance should not degrade with data accumulated.
To Reproduce
Just run it for long.
Expected behavior
No response
Additional context
No response