Slow hivemind query under PostgREST

69 min 5% 7,618 ms 546hivemind 

_result = ( SELECT jsonb_agg ( ( CASE WHEN _called_from_bridge_api THEN hivemind_postgrest_utilities.create_bridge_post_object(row, _truncate_body, $7, row.is_pinned, $8) ELSE hivemind_postgrest_utilities.create_condenser_post_object(row, _truncate_body, $9) END ) ) FROM ( WITH community_data AS ( SELECT id FROM hivemind_app.hive_communities WHERE name = _tag LIMIT $10 ), 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 LEFT OUTER JOIN hivemind_app.blacklisted_by_observer_view blacklist ON (_observer_id != $11 AND blacklist.observer_id = _observer_id AND blacklist.blacklisted_id = hp.author_id) WHERE NOT (_post_id <> $12 AND hp.id >= _post_id) AND NOT(_called_from_bridge_api AND hp.is_pinned) AND NOT (_observer_id <> $13 AND EXISTS (SELECT $14 FROM hivemind_app.muted_accounts_by_id_view WHERE observer_id = _observer_id AND muted_id = hp.author_id)) ORDER BY hp.id DESC LIMIT _limit ) 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, community_posts.source AS blacklists, hp.muted_reasons FROM community_posts, LATERAL hivemind_app.get_post_view_by_id(community_posts.id) hp ORDER BY community_posts.id DESC LIMIT _limit ) row )

Here's an expalin of one such call: https://explain.dalibo.com/plan/a96fe25a7442e847#plan/node/9

Here are the exact parameters for the query explained above:

$ time curl -H "Content-Type: application/json" -d "{\"jsonrpc\":\"2.0\",\"id\":1,\"method\":\"condenser_api.get_discussions_by_created\",\"params\":[{\"limit\":10,\"tag\":\"hive-100059\"}]}" http://10.10.10.30:37002
real    0m8.010s
user    0m0.009s
sys     0m0.000s

Note: generates 225k of output

It's fast(er) on the old version served by api.hive.blog

$ time curl -H "Content-Type: application/json" -d "{\"jsonrpc\":\"2.0\",\"id\":1,\"method\":\"condenser_api.get_discussions_by_created\",\"params\":[{\"limit\":10,\"tag\":\"hive-100059\"}]}" https://api.hive.blog
real    0m0.461s
user    0m0.016s
sys     0m0.012s

Note: generates 211k of output