Super slow indexing query in hafbe

On shed14, HAF_BLOCK_EXPLORER_VERSION=d3a1be59

NOTICE:  Attempting to process a block range: <19040001, 19050000>
NOTICE:  btracker_app_a processed successfully. 7.327 seconds
NOTICE:  Updated last processed block.
NOTICE:  btracker_app_b processed successfully. 9.252 seconds
NOTICE:  hafbe_app_a processed successfully. 0.025 seconds
NOTICE:  hafbe_app_b processed successfully. 0.032 seconds
NOTICE:  hafbe_app_c processed successfully. 793.763 seconds
NOTICE:  state_provider processed successfully. 0.060 seconds
NOTICE:  Processed 10000 blocks in 810.474 seconds
NOTICE:  Block processing running for 3950.81 minutes

pghero data:
13 min 39%	793,763 ms	1hafbe_owner
SELECT hafbe_app.process_block_range_data_c(b, _last_block)
13 min 38%	791,485 ms	1hafbe_owner
WITH comment_operation AS (

SELECT 
    cao.body AS body,
    cao.id AS source_op,
    cao.block_num AS source_op_block,
    cao.timestamp AS _timestamp,
    cao.op_type_id AS op_type
FROM hive.hafbe_app_operations_view cao
LEFT JOIN (
  SELECT 
      DISTINCT ON (lvt.voter) 
      voter,
      lvt.id AS source_op
  FROM hafbe_views.votes_view lvt
  WHERE lvt.block_num BETWEEN _from AND _to
  ORDER BY voter, lvt.id DESC
) lvt_subquery ON cao.id = lvt_subquery.source_op
LEFT JOIN (
  WITH pow AS MATERIALIZED (
  SELECT 
      DISTINCT ON (pto.worker_account) 
      worker_account,
      pto.id,
      pto.block_num
  FROM hafbe_views.pow_view pto
  WHERE pto.block_num BETWEEN _from AND _to
  )
  SELECT po.id AS source_op FROM pow po
  JOIN hive.hafbe_app_accounts_view a ON a.name = po.worker_account
  LEFT JOIN hafbe_app.account_parameters ap ON a.id = ap.account
  WHERE ap.account IS NULL
  ORDER BY po.worker_account, po.block_num, po.id DESC
) po_subquery ON cao.id = po_subquery.source_op
LEFT JOIN (
  WITH pow_two AS MATERIALIZED (
  SELECT 
      DISTINCT ON (pto.worker_account) 
      worker_account,
      pto.id,
      pto.block_num
  FROM hafbe_views.pow_two_view pto
  WHERE pto.block_num BETWEEN _from AND _to
  )
  SELECT po.id AS source_op FROM pow_two po
  JOIN hive.hafbe_app_accounts_view a ON a.name = po.worker_account
  LEFT JOIN hafbe_app.account_parameters ap ON a.id = ap.account
  WHERE ap.account IS NULL
  ORDER BY po.worker_account, po.block_num, po.id DESC
) pto_subquery ON cao.id = pto_subquery.source_op
LEFT JOIN (
  WITH selected_range AS MATERIALIZED (
  	SELECT up.id, up.author, up.permlink
    FROM hafbe_views.comments_view up
  	WHERE up.block_num BETWEEN _from AND _to
  ),
filtered_range AS MATERIALIZED (
 SELECT up.id AS up_id, up.author, up.permlink,
	(SELECT prd.id
    FROM 
      hafbe_views.comments_view prd
    WHERE 
      prd.author = up.author 
      AND prd.permlink = up.permlink AND prd.id < up.id
	 ORDER BY prd.id DESC LIMIT 1) AS prd_id
  FROM selected_range up 
)
SELECT source_op FROM (
SELECT prd.up_id AS source_op, prd.author, prd.permlink, prd.prd_id,
COALESCE(
       (SELECT 1 
        FROM 
          hafbe_views.deleted_comments_view dp
        WHERE 
          dp.author = prd.author 
          and dp.permlink = prd.permlink 
		  AND prd.prd_id IS NOT NULL
          and dp.id between prd.prd_id and prd.up_id
	   LIMIT 1),0
) as filtered FROM filtered_range prd ) as filtered2
WHERE (filtered = 0 and prd_id IS NULL) or (filtered =1 and prd_id IS NOT NULL)
) up_subquery ON cao.id = up_subquery.source_op
WHERE 
  (cao.op_type_id IN (9, 23, 41, 80, 76, 25, 36)
  OR (cao.op_type_id = 72 AND lvt_subquery.source_op IS NOT NULL)
  OR (cao.op_type_id = 1  AND up_subquery.source_op IS NOT NULL)
  OR (cao.op_type_id = 14 AND po_subquery.source_op IS NOT NULL)
  OR (cao.op_type_id = 30 AND pto_subquery.source_op IS NOT NULL))
  AND cao.block_num BETWEEN _from AND _to
)
  SELECT * FROM comment_operation 
  ORDER BY source_op_block, source_op