blacklist speedup

CREATE OR REPLACE FUNCTION hivemind_app.get_blacklisted_by_observer(_observer_id INTEGER)
RETURNS TABLE (
    blacklisted_id INTEGER,
    source TEXT
) AS $$
BEGIN
    RETURN QUERY
    WITH blacklisters AS MATERIALIZED 
    (
      SELECT following as id FROM hivemind_app.hive_follows WHERE follow_blacklists AND follower = _observer_id
    )
    SELECT following AS blacklisted_id,        -- get directly blacklisted accounts
           'my blacklist'::text AS source
    FROM hivemind_app.hive_follows
    WHERE hive_follows.blacklisted
      AND hive_follows.follower = _observer_id
    UNION ALL
    SELECT blacklister_follows.following AS blacklisted_id,              -- get indirectly blacklisted accounts
           string_agg('blacklisted by '::text || blacklister_accounts.name::text, ','::text ORDER BY blacklister_accounts.name) AS source
    FROM blacklisters
    JOIN hivemind_app.hive_follows blacklister_follows ON blacklister_follows.follower = blacklisters.id -- need this to get all accounts blacklisted by blacklister
    JOIN hivemind_app.hive_accounts blacklister_accounts ON blacklister_accounts.id = blacklisters.id    -- need this to get name of blacklister
    WHERE blacklister_follows.blacklisted
    GROUP BY blacklister_follows.following;
END;
$$ LANGUAGE plpgsql STABLE;

/* OLD VIEW
CREATE OR REPLACE VIEW hivemind_app.blacklisted_by_observer_view AS
 SELECT hive_follows.follower AS observer_id,
    following_accounts.id AS blacklisted_id,
    following_accounts.name AS blacklisted_name,
    'my blacklist'::text AS source
   FROM hivemind_app.hive_follows
     JOIN hivemind_app.hive_accounts following_accounts ON hive_follows.following = following_accounts.id
  WHERE hive_follows.blacklisted
UNION ALL
 SELECT hive_follows_direct.follower AS observer_id,
    following_accounts.id AS blacklisted_id,
    following_accounts.name AS blacklisted_name,
    ''::text COLLATE "C" AS source
   FROM hivemind_app.hive_follows hive_follows_direct
     JOIN hivemind_app.hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
     JOIN hivemind_app.hive_accounts following_accounts ON hive_follows_indirect.following = following_accounts.id
     JOIN hivemind_app.hive_accounts indirect_accounts ON hive_follows_indirect.follower = indirect_accounts.id
  WHERE hive_follows_direct.follow_blacklists AND hive_follows_indirect.blacklisted
*/
explain (analyze, verbose, format text)
WITH -- get_trending_ranked_posts_for_tag
tag_posts as materialized
(
SELECT
  hp.id,
  blacklist.source
FROM hivemind_app.live_posts_view hp
JOIN hivemind_app.hive_post_tags hpt ON hpt.post_id = hp.id
LEFT OUTER JOIN hivemind_app.blacklisted_by_observer_view blacklist ON (blacklist.observer_id = 1371790 AND blacklist.blacklisted_id = hp.author_id)
WHERE
  hpt.tag_id = /*32894*/ 134406 /*138176221*/ AND NOT hp.is_paidout
  --AND NOT (0 <> 0 AND hp.sc_trend >= 0.0 AND NOT (hp.sc_trend = 0.0 AND hp.id < 0))
  AND (0 /*__post_id*/ = 0 OR hp.sc_trend < 0.0 /* __trending_limit*/ OR (hp.sc_trend = 0.0 /* __trending_limit*/ AND hp.id < 0))
  AND NOT (1371790 <> 0 AND EXISTS (SELECT 1 FROM hivemind_app.muted_accounts_by_id_view WHERE observer_id = 1371790 AND muted_id = hp.author_id))
ORDER BY
  hp.sc_trend DESC, hp.id DESC
LIMIT 100
)
,supplemented_data as materialized
(
      SELECT
        hp.id,
        hp.author,
        hp.parent_author,
        hp.author_rep,
        hp.root_title,
        hp.beneficiaries,
        hp.max_accepted_payout,
        hp.percent_hbd,
        hp.url,
        hp.permlink,
        hp.parent_permlink_or_category,
        hp.title,
        hp.body,
        hp.category,
        hp.depth,
        hp.promoted,
        hp.payout,
        hp.pending_payout,
        hp.payout_at,
        hp.is_paidout,
        hp.children,
        hp.votes,
        hp.created_at,
        hp.updated_at,
        hp.rshares,
        hp.abs_rshares,
        hp.json,
        hp.is_hidden,
        hp.is_grayed,
        hp.total_votes,
        hp.sc_trend,
        hp.role_title,
        hp.community_title,
        hp.role_id,
        hp.is_pinned,
        hp.curator_payout_value,
        hp.is_muted,
        tag_posts.source AS blacklists,
        hp.muted_reasons
      FROM tag_posts,
      LATERAL hivemind_app.get_post_view_by_id(tag_posts.id) hp
      --ORDER BY
        --hp.sc_trend DESC, hp.id DESC
      --LIMIT 100
)
SELECT jsonb_agg (
      CASE
        WHEN True THEN hivemind_postgrest_utilities.create_bridge_post_object(sd, 0, NULL, sd.is_pinned, True)
        ELSE hivemind_postgrest_utilities.create_condenser_post_object(sd, 0, False)
      END
	ORDER BY sd.sc_trend DESC, sd.id DESC
    )
FROM supplemented_data sd

explain (analyze, verbose, format text)
WITH -- get_trending_ranked_posts_for_tag
tag_posts as materialized
(
SELECT
  hp.id,
  blacklist.source
FROM hivemind_app.live_posts_view hp
JOIN hivemind_app.hive_post_tags hpt ON hpt.post_id = hp.id
LEFT OUTER JOIN hivemind_app.blacklisted_by_observer_view blacklist ON (blacklist.observer_id = 1371790 AND blacklist.blacklisted_id = hp.author_id)
WHERE
  hpt.tag_id = /*32894*/ 134406 /*138176221*/ AND NOT hp.is_paidout
  --AND NOT (0 <> 0 AND hp.sc_trend >= 0.0 AND NOT (hp.sc_trend = 0.0 AND hp.id < 0))
  AND (0 /*__post_id*/ = 0 OR hp.sc_trend < 0.0 /* __trending_limit*/ OR (hp.sc_trend = 0.0 /* __trending_limit*/ AND hp.id < 0))
  AND NOT (1371790 <> 0 AND EXISTS (SELECT 1 FROM hivemind_app.muted_accounts_by_id_view WHERE observer_id = 1371790 AND muted_id = hp.author_id))
ORDER BY
  hp.sc_trend DESC, hp.id DESC
LIMIT 100
)
,supplemented_data as materialized
(
      SELECT
        hp.id,
        hp.author,
        hp.parent_author,
        hp.author_rep,
        hp.root_title,
        hp.beneficiaries,
        hp.max_accepted_payout,
        hp.percent_hbd,
        hp.url,
        hp.permlink,
        hp.parent_permlink_or_category,
        hp.title,
        hp.body,
        hp.category,
        hp.depth,
        hp.promoted,
        hp.payout,
        hp.pending_payout,
        hp.payout_at,
        hp.is_paidout,
        hp.children,
        hp.votes,
        hp.created_at,
        hp.updated_at,
        hp.rshares,
        hp.abs_rshares,
        hp.json,
        hp.is_hidden,
        hp.is_grayed,
        hp.total_votes,
        hp.sc_trend,
        hp.role_title,
        hp.community_title,
        hp.role_id,
        hp.is_pinned,
        hp.curator_payout_value,
        hp.is_muted,
        tag_posts.source AS blacklists,
        hp.muted_reasons
      FROM tag_posts,
      LATERAL hivemind_app.get_post_view_by_id(tag_posts.id) hp
      --ORDER BY
        --hp.sc_trend DESC, hp.id DESC
      --LIMIT 100
)
SELECT jsonb_agg (
      CASE
        WHEN True THEN hivemind_postgrest_utilities.create_bridge_post_object(sd, 0, NULL, sd.is_pinned, True)
        ELSE hivemind_postgrest_utilities.create_condenser_post_object(sd, 0, False)
      END
	ORDER BY sd.sc_trend DESC, sd.id DESC
    )
FROM supplemented_data sd