Draft: Refactor get_ranked_posts.sql to add a CTE to ensure posts get filtered by...

Refactor get_ranked_posts.sql to add a CTE to ensure posts get filtered by community_id first (fix big slowdown).

hive/db/sql_scripts/postgrest/utilities/get_ranked_posts.sql

@@ -22,26 +22,16 @@ BEGIN
       SELECT jsonb_agg (
         hivemind_postgrest_utilities.create_bridge_post_object(row, _truncate_body, NULL, row.is_pinned, True)
       ) FROM (
-        WITH
-        community_data AS -- get_ranked_posts_for_communities pinned
-        (
-          SELECT
-            id
-          FROM hivemind_app.hive_communities
-          WHERE
-            name = _tag
-          LIMIT 1
-        ),
+        WITH -- get_ranked_posts_for_communities pinned
         pinned_post AS
         (
           SELECT 
             hp.id,
             blacklist.source
-          FROM hivemind_app.live_posts_comments_view hp
-          JOIN community_data cd ON hp.community_id = cd.id
+          FROM hivemind_app.live_posts_view hp
           LEFT OUTER JOIN hivemind_app.blacklisted_by_observer_view blacklist ON (_observer_id != 0 AND blacklist.observer_id = _observer_id AND blacklist.blacklisted_id = hp.author_id)
           WHERE
-            hp.is_pinned
+            hp.is_pinned  AND hp.community_id = (SELECT id FROM hivemind_app.hive_communities WHERE name = _tag) --use hive_posts_community_id_is_pinned_idx
             AND NOT (_post_id <> 0 AND hp.id >= _post_id)
             AND NOT (_observer_id <> 0 AND EXISTS (SELECT 1 FROM hivemind_app.muted_accounts_by_id_view WHERE observer_id = _observer_id AND muted_id = hp.author_id))
           ORDER BY hp.id DESC
@@ -145,17 +135,21 @@ BEGIN
     )
     ) FROM (
       WITH -- get_trending_ranked_posts_for_communities
-      community_posts as
+      community_posts as MATERIALIZED
       (
+        SELECT id, author_id, sc_trend FROM hivemind_app.live_posts_view
+        WHERE community_id = (SELECT id FROM hivemind_app.hive_communities WHERE name = _tag) 
+        AND NOT hp.is_paidout --use index hive_posts_community_id_not_is_paidout_idx
+      ),
+      ranked_community_posts as (
         SELECT
           hp.id,
           blacklist.source
-        FROM hivemind_app.live_posts_view hp
-        JOIN hivemind_app.hive_communities hc ON hp.community_id = hc.id
+        FROM community_posts hp
         LEFT OUTER JOIN hivemind_app.blacklisted_by_observer_view blacklist ON (_observer_id != 0 AND blacklist.observer_id = _observer_id AND blacklist.blacklisted_id = hp.author_id)
         WHERE
-          hc.name = _tag AND NOT hp.is_paidout AND NOT(_called_from_bridge_api AND hp.is_pinned)
-          AND NOT (_post_id <> 0 AND hp.sc_trend >= _trending_limit AND NOT ( hp.sc_trend = _trending_limit AND hp.id < _post_id ))
+          NOT (_post_id <> 0 AND hp.sc_trend >= _trending_limit AND NOT ( hp.sc_trend = _trending_limit AND hp.id < _post_id ))
+          AND NOT(_called_from_bridge_api AND hp.is_pinned)
           AND NOT (_observer_id <> 0 AND EXISTS (SELECT 1 FROM hivemind_app.muted_accounts_by_id_view WHERE observer_id = _observer_id AND muted_id = hp.author_id))
         ORDER BY
           hp.sc_trend DESC, hp.id DESC
@@ -199,10 +193,10 @@ BEGIN
         hp.is_pinned,
         hp.curator_payout_value,
         hp.is_muted,
-        community_posts.source AS blacklists,
+        ranked_community_posts.source AS blacklists,
         hp.muted_reasons
-      FROM community_posts,
-      LATERAL hivemind_app.get_post_view_by_id(community_posts.id) hp
+      FROM ranked_community_posts,
+      LATERAL hivemind_app.get_post_view_by_id(ranked_community_posts.id) hp
       ORDER BY
         hp.sc_trend DESC, hp.id DESC
       LIMIT _limit
@@ -618,29 +612,23 @@ BEGIN
     )
     ) FROM (
       WITH -- get_created_ranked_posts_for_communities
-      community_data AS
+      live_community_posts as
       (
-        SELECT
-          id
-        FROM hivemind_app.hive_communities
-        WHERE
-          name = _tag
-        LIMIT 1
+        SELECT id, author_id, is_pinned FROM hivemind_app.live_posts_view
+        where community_id = (SELECT id FROM hivemind_app.hive_communities WHERE name = _tag)
+        AND NOT(_called_from_bridge_api AND is_pinned) --use index hive_posts_community_id_not_is_pinned_idx
       ),
       community_posts as
       (
         SELECT
           hp.id,
           blacklist.source
-        FROM hivemind_app.live_posts_view hp
-        JOIN community_data cd ON hp.community_id = cd.id
+        FROM live_community_posts hp
         LEFT OUTER JOIN hivemind_app.blacklisted_by_observer_view blacklist ON (_observer_id != 0 AND blacklist.observer_id = _observer_id AND blacklist.blacklisted_id = hp.author_id)
         WHERE
           NOT (_post_id <> 0 AND hp.id >= _post_id)
-          AND NOT(_called_from_bridge_api AND hp.is_pinned)
           AND NOT (_observer_id <> 0 AND EXISTS (SELECT 1 FROM hivemind_app.muted_accounts_by_id_view WHERE observer_id = _observer_id AND muted_id = hp.author_id))
-        ORDER BY
-          hp.id DESC
+        ORDER BY hp.id DESC
         LIMIT _limit
       )
       SELECT