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

@@ -618,22 +618,17 @@ BEGIN
     )
     ) FROM (
       WITH
-      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 LIMIT 1)
       ),
       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)