Speed up hivemind replay

Preliminary performance analysis using htop suggests that hivemind replay is likely CPU-bound in the python code nowadays, so the python code needs to be profiled. Early guesses for culprits would be routines doing string processing.

In the meantime, despite the python code being the likely bottleneck, I'm still attaching pghero data for the postgres times of an 81M block replay so we have an easily available record of it (data is from s16, our fastest system):

272 min 7%	7 ms	2,350,502hivemind
SELECT * FROM hivemind_app.enum_operations4hivemind($1, $2)
234 min 6%	6 ms	2,350,502hivemind
SELECT ho.id, ho.block_num, ho.op_type_id, ho.op_type_id >= $3 AS is_virtual, ho.body::VARCHAR
    FROM hive.hivemind_app_operations_view ho
    WHERE ho.block_num BETWEEN _first_block AND _last_block
          AND (ho.op_type_id < $4
               OR ho.op_type_id in ($5, $6, $7, $8, $9)
              )
    ORDER BY ho.block_num, ho.id
119 min 3%	0 ms	64,535,752hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8])::VARCHAR[])
94 min 3%	0 ms	97,530,592hivemind
INSERT INTO hivemind_app.hive_posts as hp
  (parent_id, depth, community_id, category_id,
    root_id, is_muted, is_valid,
    author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num, block_num_created)
      SELECT
          s.parent_id,
          s.depth,
          (s.composite).community_id,
          s.category_id,
          s.root_id,
          (s.composite).is_muted,
          s.is_valid,
          s.author_id,
          s.permlink_id,
          s.created_at,
          s.updated_at,
          s.sc_hot,
          s.sc_trend,
          s.active,
          s.payout_at,
          s.cashout_time,
          s.counter_deleted,
          s.block_num,
          s.block_num_created
      FROM (
               SELECT
                   hivemind_app.process_community_post(_block_num, _community_support_start_block, _parent_permlink, ha.id, $8, php.is_muted, php.community_id) as composite,
                   php.id AS parent_id, php.depth + $9 AS depth,
                   COALESCE(php.category_id, (select hcg.id from hivemind_app.hive_category_data hcg where hcg.category = _parent_permlink)) AS category_id,
                   (CASE(php.root_id)
                        WHEN $10 THEN php.id
                        ELSE php.root_id
                       END) AS root_id,
                   php.is_valid AS is_valid,
                   ha.id AS author_id, hpd.id AS permlink_id, _date AS created_at,
                   _date AS updated_at,
                   hivemind_app.calculate_time_part_of_hot(_date) AS sc_hot,
                   hivemind_app.calculate_time_part_of_trending(_date) AS sc_trend,
                   _date AS active, (_date + INTERVAL $11) AS payout_at, (_date + INTERVAL $12) AS cashout_time,
                   $13 AS counter_deleted,
                   _block_num as block_num, _block_num as block_num_created
               FROM hivemind_app.hive_accounts ha,
                    hivemind_app.hive_permlink_data hpd,
                    hivemind_app.hive_posts php
                        INNER JOIN hivemind_app.hive_accounts pha ON pha.id = php.author_id
                        INNER JOIN hivemind_app.hive_permlink_data phpd ON phpd.id = php.permlink_id
               WHERE pha.name = _parent_author AND phpd.permlink = _parent_permlink AND
                       ha.name = _author AND hpd.permlink = _permlink AND php.counter_deleted = $14
           ) s
      ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
          --- During post update it is disallowed to change: parent-post, category, community-id
          --- then also depth, is_valid and is_muted is impossible to change
          --- post edit part
          updated_at = _date,
          active = _date,
          block_num = _block_num
      RETURNING (xmax = $15) as is_new_post, hp.id, hp.author_id, hp.permlink_id, (SELECT hcd.category FROM hivemind_app.hive_category_data hcd WHERE hcd.id = hp.category_id) as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
93 min 3%	0 ms	130,197,762hivemind
INSERT INTO hivemind_app.hive_permlink_data
(permlink)
values
(
_permlink
)
ON CONFLICT DO NOTHING
89 min 2%	69 ms	77,414hived_group
SELECT hive.set_irreversible($1)
78 min 2%	0 ms	32,667,170hivemind
INSERT INTO hivemind_app.hive_posts as hp
  (parent_id, depth, community_id, category_id,
    root_id, is_muted, is_valid,
    author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend,
    active, payout_at, cashout_time, counter_deleted, block_num, block_num_created,
    tags_ids)
  SELECT
        s.parent_id,
        s.depth,
        (s.composite).community_id,
        s.category_id,
        s.root_id,
        (s.composite).is_muted,
        s.is_valid,
        s.author_id,
        s.permlink_id,
        s.created_at,
        s.updated_at,
        s.sc_hot,
        s.sc_trend,
        s.active,
        s.payout_at,
        s.cashout_time,
        s.counter_deleted,
        s.block_num,
        s.block_num_created,
        s.tags_ids
  FROM (
        SELECT
            hivemind_app.process_community_post(_block_num, _community_support_start_block, _parent_permlink, ha.id, $9,$10, $11) as composite,
            $12 AS parent_id, $13 AS depth,
            (SELECT hcg.id FROM hivemind_app.hive_category_data hcg WHERE hcg.category = _parent_permlink) AS category_id,
            $14 as root_id, -- will use id as root one if no parent
            $15 AS is_valid,
            ha.id AS author_id, hpd.id AS permlink_id, _date AS created_at,
            _date AS updated_at,
            hivemind_app.calculate_time_part_of_hot(_date) AS sc_hot,
            hivemind_app.calculate_time_part_of_trending(_date) AS sc_trend,
            _date AS active, (_date + INTERVAL $16) AS payout_at, (_date + INTERVAL $17) AS cashout_time,
            $18 AS counter_deleted,
            _block_num as block_num, _block_num as block_num_created,
            (
                SELECT ARRAY_AGG( prepare_tags )
                FROM hivemind_app.prepare_tags( ARRAY_APPEND(_metadata_tags, _parent_permlink ) )
            ) as tags_ids
        FROM
            hivemind_app.hive_accounts ha,
            hivemind_app.hive_permlink_data hpd
        WHERE ha.name = _author and hpd.permlink = _permlink
       ) s

      ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET
    --- During post update it is disallowed to change: parent-post, category, community-id
    --- then also depth, is_valid and is_muted is impossible to change
    --- post edit part
    updated_at = _date,
    active = _date,
    block_num = _block_num,
    tags_ids = EXCLUDED.tags_ids

  RETURNING (xmax = $19) as is_new_post, hp.id, hp.author_id, hp.permlink_id, _parent_permlink as post_category, hp.parent_id, hp.community_id, hp.is_valid, hp.is_muted, hp.depth
73 min 2%	0 ms	28,166,528hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, ($8)::VARCHAR[])
62 min 2%	0 ms	16,763,302hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12])::VARCHAR[])
60 min 2%	183 ms	19,625hivemind
SELECT hivemind_app.update_hive_posts_root_id(1, 81522953)
60 min 2%	183 ms	19,625hivemind
UPDATE hivemind_app.hive_posts uhp
SET root_id = id
WHERE uhp.root_id = 0 AND (_first_block_num IS NULL OR (uhp.block_num >= _first_block_num AND uhp.block_num <= _last_block_num))
49 min 1%	0 ms	32,667,170hivemind
INSERT INTO
     hivemind_app.hive_tag_data AS htd(tag)
  SELECT UNNEST( __tags )
  ON CONFLICT("tag") DO UPDATE SET tag=EXCLUDED.tag --trick to always return id
  RETURNING htd.id
44 min 1%	34 ms	77,414hived_group
SELECT hive.copy_transactions_to_irreversible( __irreversible_head_block, _block_num )
44 min 1%	34 ms	77,414hived_group
INSERT INTO hive.transactions
    SELECT
          htr.block_num
        , htr.trx_in_block
        , htr.trx_hash
        , htr.ref_block_num
        , htr.ref_block_prefix
        , htr.expiration
        , htr.signature
    FROM
        hive.transactions_reversible htr
    JOIN ( SELECT
              DISTINCT ON ( hbr.num ) hbr.num
            , hbr.fork_id
            FROM hive.blocks_reversible hbr
            WHERE
                    hbr.num <= _new_irreversible_block
                AND hbr.num > _head_block_of_irreversible_blocks
            ORDER BY hbr.num ASC, hbr.fork_id DESC
    ) as num_and_forks ON htr.block_num = num_and_forks.num AND htr.fork_id = num_and_forks.fork_id
30 min 0.8%	91 ms	19,625hivemind
SELECT hivemind_app.update_notification_cache($1, $2, $3)
28 min 0.8%	84 ms	19,625hivemind
INSERT INTO hivemind_app.hive_notification_cache
  (block_num, type_id, created_at, src, dst, dst_post_id, post_id, score, payload, community, community_title)
  SELECT nv.block_num, nv.type_id, nv.created_at, nv.src, nv.dst, nv.dst_post_id, nv.post_id, nv.score, nv.payload, nv.community, nv.community_title
  FROM hivemind_app.hive_raw_notifications_view nv
  WHERE nv.block_num > __limit_block AND (_first_block_num IS NULL OR nv.block_num BETWEEN _first_block_num AND _last_block_num)
  ORDER BY nv.block_num, nv.type_id, nv.created_at, nv.src, nv.dst, nv.dst_post_id, nv.post_id
23 min 0.6%	69 ms	19,625hivemind
SELECT hivemind_app.update_hive_posts_api_helper(1, 81522953)
23 min 0.6%	69 ms	19,625hivemind
INSERT INTO hivemind_app.hive_posts_api_helper (id, author_s_permlink)
  SELECT hp.id, ha.name || '/' || hpd_p.permlink
  FROM hivemind_app.live_posts_comments_view hp
  JOIN hivemind_app.hive_accounts ha ON (ha.id = hp.author_id)
  JOIN hivemind_app.hive_permlink_data hpd_p ON (hpd_p.id = hp.permlink_id)
  WHERE hp.block_num BETWEEN _first_block_num AND _last_block_num
  ON CONFLICT (id) DO NOTHING
22 min 0.6%	5,241 ms	256hivemind
INSERT INTO hivemind_app.__post_children
    (id, child_count)
      SELECT
        h1.parent_id AS queried_parent,
        SUM(COALESCE((SELECT pc.child_count FROM hivemind_app.__post_children pc WHERE pc.id = h1.id),
                      $3
                    ) + $4
        ) AS count
      FROM hivemind_app.hive_posts h1
      WHERE (h1.parent_id != $5 OR __depth = $6) AND h1.counter_deleted = $7 AND h1.id != $8 AND h1.depth = __depth
      GROUP BY h1.parent_id

    ON CONFLICT ON CONSTRAINT __post_children_pkey DO UPDATE
      SET child_count = hivemind_app.__post_children.child_count + excluded.child_count
22 min 0.6%	0 ms	11,118,867hived_group
SELECT $3 FROM ONLY "hive"."operations_reversible" x WHERE "id" OPERATOR(pg_catalog.=) $1 AND "fork_id" OPERATOR(pg_catalog.=) $2 FOR KEY SHARE OF x
21 min 0.6%	17 ms	77,414hived_group
SELECT hive.remove_obsolete_reversible_data( _block_num )
21 min 0.6%	16 ms	77,414hived_group
SELECT hive.copy_account_operations_to_irreversible( __irreversible_head_block, _block_num )
21 min 0.6%	16 ms	77,414hived_group
INSERT INTO hive.account_operations
    SELECT
           haor.block_num
         , haor.account_id
         , haor.account_op_seq_no
         , haor.operation_id
         , haor.op_type_id
    FROM
        hive.account_operations_reversible haor
        JOIN (
            SELECT
                  DISTINCT ON ( hbr.num ) hbr.num
                , hbr.fork_id
            FROM hive.blocks_reversible hbr
            WHERE
                hbr.num <= _new_irreversible_block
              AND hbr.num > _head_block_of_irreversible_blocks
            ORDER BY hbr.num ASC, hbr.fork_id DESC
        ) as num_and_forks ON haor.fork_id = num_and_forks.fork_id AND haor.block_num = num_and_forks.num
15 min 0.4%	0 ms	4,338,941hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11])::VARCHAR[])
14 min 0.4%	11 ms	77,414hived_group
DELETE FROM hive.operations_reversible hor
    WHERE hor.block_num <= _new_irreversible_block
13 min 0.4%	0 ms	3,007,711hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14,$15,$16,$17])::VARCHAR[])
13 min 0.4%	0 ms	4,598,467hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9])::VARCHAR[])
13 min 0.3%	10 ms	77,394hived_group
INSERT INTO hive.account_operations_reversible VALUES( ( unnest( _account_operations ) ).*, __fork_id )
12 min 0.3%	0 ms	21,751,239hivemind
UPDATE
                hivemind_app.hive_posts hp
            SET
                max_accepted_payout = $1,
                percent_hbd = $2,
                allow_votes = $3,
                allow_curation_rewards = $4,
                beneficiaries = $5
            WHERE
            hp.author_id = (SELECT id FROM hivemind_app.hive_accounts WHERE name = $6) AND
            hp.permlink_id = (SELECT id FROM hivemind_app.hive_permlink_data WHERE permlink = $7)
10 min 0.3%	30 ms	19,624hivemind
SELECT hivemind_app.update_posts_rshares(81522953, 81580434)
9 min 0.2%	0 ms	2,808,779hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10])::VARCHAR[])
7 min 0.2%	0 ms	1,447,096hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14])::VARCHAR[])
6 min 0.2%	0 ms	1,278,484hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14,$15])::VARCHAR[])
6 min 0.2%	0 ms	1,146,373hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13])::VARCHAR[])
5 min 0.1%	0 ms	1,095,534hivemind
SELECT is_new_post, id, author_id, permlink_id, post_category, parent_id, community_id, is_valid, is_muted, depth
            FROM hivemind_app.process_hive_post_operation(($1)::varchar, ($2)::varchar, ($3)::varchar, ($4)::varchar, ($5)::timestamp, ($6)::integer, ($7)::integer, (ARRAY[$8,$9,$10,$11,$12,$13,$14,$15,$16])::VARCHAR[])
5 min 0.1%	0 ms	6,408,849hived_group
SELECT $2 FROM ONLY "hive"."accounts" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
4 min 0.1%	0 ms	12,758,752hivemind
UPDATE hivemind_app.hive_accounts SET lastread_at = $1 WHERE name = $2
4 min 0.1%	0 ms	2,350,502hivemind
SELECT * FROM hivemind_app.enum_blocks4hivemind($1, $2)
4 min < 0.1%	0 ms	4,710,018hived_group
SELECT $3 FROM ONLY "hive"."account_operations_reversible" x WHERE $1 OPERATOR(pg_catalog.=) "operation_id" AND $2 OPERATOR(pg_catalog.=) "fork_id" FOR KEY SHARE OF x
4 min < 0.1%	3 ms	77,414hived_group
DELETE FROM hive.transactions_reversible htr
    WHERE htr.block_num <= _new_irreversible_block
4 min < 0.1%	3 ms	77,414hived_group
DELETE FROM hive.account_operations_reversible har
    USING hive.operations_reversible hor
    WHERE
            har.operation_id = hor.id
        AND har.fork_id = hor.fork_id
        AND hor.block_num <= _new_irreversible_block
3 min < 0.1%	0 ms	32,667,170hivemind
INSERT INTO hivemind_app.hive_category_data
    (category)
    VALUES (_parent_permlink)
    ON CONFLICT (category) DO NOTHING
3 min < 0.1%	10 ms	19,625hivemind
SELECT hivemind_app.update_follow_count($1, $2)