The index usage of post is all wrong, wrong thinking…

CREATE INDEX idx_post_aggregates_featured_community_active ON public.post_aggregates USING btree (featured_community DESC, hot_rank_active DESC, published DESC);

CREATE INDEX idx_post_aggregates_featured_community_controversy ON public.post_aggregates USING btree (featured_community DESC, controversy_rank DESC);

SELECT ends with…
ORDER BY “post_aggregates”.“featured_community” DESC , “post_aggregates”.“hot_rank_active” DESC , “post_aggregates”.“published” DESC

they created compound indexes based on how ORDER BY is used. But there is a maximum page length of 50 posts. So really that is only helping with sorting 50 posts each SQL SELECT.

INDEX needs to be the heart of WHERE clause. Not ORDER BY hot_rank_active, and LIMIT 50. The hot_rank_active could have been put into the WHERE clause, but it wasn’t.