Yoast Page Sitemap Not Loading On Website With 500-600 pages Out Of Memory But Already Maxed Hosting Provider's Memory

Hi there,

I have a website that's a Beaver Builder website. I recently start doing some programmatic SEO stuff and added in about 350 pages to my website bringing the total number of pages on the site to around 500 pages. I have started bumping into some out of memory exceptions. I have increased the memory to the point where I have maxed the amount of allowable memory that my hosting provider offers (Kinsta). I have 4 threads with 1024MB per thread and am still getting out of memory exceptions when loading my page sitemap.

I am honestly shocked at this though because knowing what I know about database queries making a query for a list of pages should be an EXTREMELY efficient operation assuming there are the appropriate indexes on the table. Since we are talking about the post IDs where post type = page type of query I have to assume there are indexes there but maybe not.

I am trying to restore the loading of my page sitemap. I tried cutting down the max number of entries per page to 250 but even then I am still getting out of memory exceptions. I don't think cutting down the max number of entries per page is the ultimate cure for the problems I am looking for. I am in touch with Beaver Builder to try to reduce the size of my post meta table as it is rather large and I suspect the reason for the OOM exceptions, but at the same time I view this as more of a query design problem than a table size problem. The only way I can think that Yoast would be running out of memory when querying sitemaps is that it's querying for data that it does not need to make a sitemap. Or there are indexes that need to be added to tables on higher volume websites.

Is there something I can do such as add an index to my WP DB tables to make sitemap queries more efficient or other troubleshooting steps that I can make to restore the ability for my sitemaps to load?

No repository file, test, or entry point is mentioned in the issue. Start by reproducing the page sitemap out-of-memory failure and tracing the sitemap query and memory usage; done means identifying a repository-level cause and confirming that the sitemap loads within the reported hosting limit.