Private posts exclusion from preload can generate big queries and high CPU usage in some cases

[alfonso100]

Before submitting an issue please check that you’ve completed the following steps:
YES - Made sure you’re on the latest version
YES - Used the search feature to ensure that the bug hasn’t been reported before

Describe the bug

Per @engahmeds3ed investigation:

The issue is a regression from this PR:
#5920

Exactly here:

wp-rocket/inc/Engine/Preload/Subscriber.php

Lines 496 to 501 in fee96d7

	$arg = [
	'post_type' => 'any',
	'post_status' => 'private',
	'posts_per_page' => -1,
	];
	$query = new \WP_Query( $arg );

This returns any private post from any post type. On a customer's site, we found about 19.000 posts most of which are attachments.

This query is inside a callback method exclude_private_post_uri for the action rocket_preload_exclude_urls
So if the preload is enabled or not we are doing this check before any partial clean, exactly here:

wp-rocket/inc/Engine/Preload/Controller/ClearCache.php

Lines 34 to 43 in f6904c1

	if ( ! $this->is_excluded_by_filter( $url ) ) {
	$this->query->create_or_update(
	[
	'url' => $url,
	'status' => 'pending',
	]
	);
	} else {
	$this->query->delete_by_url( $url );
	}

When this number of URLs is too much, the site behaves very slowly and consumes lots of CPU/memory.

To Reproduce
Steps to reproduce the behavior:

1. Create some private posts, the more the better
2. Set a log mark after this $private_urls variable
3. Clear a post cache
4. See the amount collected URLs

Expected behavior
We should guard against any potential case of excessive CPU usage here. Optimizing this function to prevent errors on sites where there are too many private URLs

Additional context
Ticket escalation:
https://wp-media.slack.com/archives/C056ZJMHG0P/p1691092512259509
https://wp-media.slack.com/archives/C056ZJMHG0P/p1691504485298989

tickets:
https://secure.helpscout.net/conversation/2314596786/432903?folderId=2683093
https://secure.helpscout.net/conversation/2319508756/433783?folderId=2683093

Backlog Grooming (for WP Media dev team use only)

• Reproduce the problem
• Identify the root cause
• Scope a solution
• Estimate the effort