Query speedups (index optimizations + replacing hive_posts_view to speedup query planning)

We don't care about deleted replies, so just exclude counter_deleted != 0 from index to make it smaller (2 fields instead of 3). This is for further optimizing of bridge_get_account_posts_by_replies.

Query planning for hive_posts_view takes too long because it has many joins, but mostly the order of these joins isn't very important since they are simple 1-1 joins that don't filter the number of rows or add a lot of data, so replacing it with new get_post_view_by_id function which forces the join order to eliminate the planning time.

There's still a few usages of hive_posts_view in python code (and even a few in SQL files) that can be replaced later.

There's a lot of changes to queries in this commit, so it's possible that existing tests won't detect all problems from this commit...

Note: need to look into PARALLEL and see if this is something we can safely be adding to many of our functions.

hive/db/sql_scripts/bridge_get_account_posts_by_comments.sql

@@ -10,7 +10,19 @@ DECLARE
 BEGIN
   __account_id = find_account_id( _account, True );
   __post_id = find_comment_id( _author, _permlink, True );
-  RETURN QUERY SELECT
+  RETURN QUERY
+  WITH ds AS --bridge_get_account_posts_by_comments
+  (
+    SELECT hp1.id
+    FROM hive_posts hp1
+    WHERE hp1.author_id = __account_id
+      AND hp1.counter_deleted = 0
+      AND hp1.depth > 0
+      AND (__post_id = 0 OR hp1.id < __post_id)
+    ORDER BY hp1.id DESC
+    LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -49,16 +61,9 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       NULL
-  FROM
-  (
-    SELECT hp1.id
-    FROM hive_posts hp1 
-    WHERE hp1.author_id = __account_id AND hp1.counter_deleted = 0 AND hp1.depth > 0 AND ( __post_id = 0 OR hp1.id < __post_id )
-    ORDER BY hp1.id DESC
-    LIMIT _limit
-  ) ds,
+  FROM ds,
   LATERAL get_post_view_by_id(ds.id) hp
-  ORDER BY hp.id DESC
+  ORDER BY ds.id DESC
   LIMIT _limit;
 END
 $function$