Very slow queries on WordPress site with 750k posts

I’m migrating an old site to WordPress. It includes about 10k articles, and 750k forum posts, which will now live in BBPress.

Everything was working fine, until I put in the full dataset, and now I have some SQL queries that are taking 20 seconds. It’s an xLarge RDS instance, with a Large EC2 webserver, and obviously I’m the only traffic.

I turned on SAVEQUERIES in wp-config.php, and here seems to be the offending query:

[9] => Array
        (
            [0] => SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_parent = 9  AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'hidden') AND (wp_postmeta.meta_key = '_bbp_last_active_time' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 15
            [1] => 19.448324918747
            [2] => require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), apply_filters('template_include'), call_user_func_array, bbp_template_include, apply_filters('bbp_template_include'), call_user_func_array, bbp_template_include_theme_compat, BBP_Shortcodes->display_forum, bbp_get_template_part, bbp_locate_template, load_template, require('/themes/xx/bbpress/content-single-forum.php'), bbp_has_topics, WP_Query->__construct, WP_Query->query, WP_Query->get_posts
        )

There’s a bunch of stuff online (and on this forum) from other people having the same problem, but a solution is hard to glean. One approach seems to be using a patch from 5 years ago, another seems to be setting a no_found_rows flag during pre_get_posts. However, these apparently just remove the SQL_CALC_FOUND_ROWS part of the query, and leave it looking like this:

[9] => Array
        (
            [0] => SELECT   wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_parent = 9  AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'hidden') AND (wp_postmeta.meta_key = '_bbp_last_active_time' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 15
            [1] => 20.614035844803
            [2] => require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), apply_filters('template_include'), call_user_func_array, bbp_template_include, apply_filters('bbp_template_include'), call_user_func_array, bbp_template_include_theme_compat, BBP_Shortcodes->display_forum, bbp_get_template_part, bbp_locate_template, load_template, require('/themes/GH2/bbpress/content-single-forum.php'), bbp_has_topics, WP_Query->__construct, WP_Query->query, WP_Query->get_posts
        )

It seems the problem is INNER JOIN or perhaps the ORDER BY, I’m not really sure, but there’s a bunch of stuff on the web from people having similar problems. None of their solutions seem to work though.

Here are the dimensions of wp_posts, and wp_postmeta:

wp_postmeta ~9,695,342	rows, size 1.1 GiB
wp_posts 	~728,593 rows, size 1.4 GiB

I’ve also done a bunch of DB tuning (removing transients, and revisions, etc etc), but that barely dented the problem. Also, I know someone is going to say W3-Total-Cache, which is valid, but I’d like the site to at least work without caching first.

Is there any solution to this? Or is WordPress just not the appropriate platform for a site this large?

Thanks