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_ranked_post_for_tag.sql

 DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_created_for_tag;
-CREATE FUNCTION bridge_get_ranked_post_by_created_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
+CREATE OR REPLACE FUNCTION bridge_get_ranked_post_by_created_for_tag( _tag VARCHAR, _author VARCHAR, _permlink VARCHAR, _limit SMALLINT, _observer VARCHAR )
 RETURNS SETOF bridge_api_post
 AS
 $function$
@@ -11,7 +11,24 @@ BEGIN
   __post_id = find_comment_id( _author, _permlink, True );
   __hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ));
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY
+  WITH created AS
+  (
+    SELECT
+      hp1.id AS id,
+      blacklist.source AS source
+    FROM
+      hive_posts hp1
+      JOIN hive_accounts_view ha ON hp1.author_id = ha.id
+      LEFT OUTER JOIN blacklisted_by_observer_view blacklist ON (blacklist.observer_id = __observer_id AND blacklist.blacklisted_id = hp1.author_id)
+    WHERE hp1.tags_ids @> __hive_tag
+      AND hp1.counter_deleted = 0 AND hp1.depth = 0
+      AND ( __post_id = 0 OR hp1.id < __post_id )
+      AND NOT ha.is_grayed AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
+    ORDER BY hp1.id DESC
+    LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -50,21 +67,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       created.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          blacklisted_by_observer_view.source as source
-      FROM
-          hive_posts hp1
-          JOIN hive_accounts_view ha ON hp1.author_id = ha.id
-          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.tags_ids @> __hive_tag AND hp1.counter_deleted = 0 AND hp1.depth = 0 AND NOT ha.is_grayed AND ( __post_id = 0 OR hp1.id < __post_id )
-      --ORDER BY hp1.id + 0 DESC -- this workaround helped the query to better choose indexes, but after some time it started to significally slow down
-      AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
-      ORDER BY hp1.id DESC
-      LIMIT _limit
-  ) as created,
+  FROM created,
   LATERAL get_post_view_by_id(created.id) hp
   ORDER BY created.id DESC
   LIMIT _limit;
@@ -89,7 +92,23 @@ BEGIN
   END IF;
   __hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ));
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY 
+  WITH hot AS 
+  (
+      SELECT
+          hp1.id as id,
+          hp1.sc_hot as hot,
+          blacklisted_by_observer_view.source as source
+      FROM
+          hive_posts hp1
+          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
+      WHERE hp1.tags_ids @> __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth = 0
+          AND ( __post_id = 0 OR hp1.sc_hot < __hot_limit OR ( hp1.sc_hot = __hot_limit AND hp1.id < __post_id ) )
+          AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
+      ORDER BY hp1.sc_hot DESC, hp1.id DESC
+      LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -128,21 +147,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       hot.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          hp1.sc_hot as hot,
-          blacklisted_by_observer_view.source as source
-      FROM
-          hive_posts hp1
-          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.tags_ids @> __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth = 0
-          AND ( __post_id = 0 OR hp1.sc_hot < __hot_limit OR ( hp1.sc_hot = __hot_limit AND hp1.id < __post_id ) )
-          AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
-      ORDER BY hp1.sc_hot DESC, hp1.id DESC
-      LIMIT _limit
-  ) as hot,
+  FROM hot,
   LATERAL get_post_view_by_id(hot.id) hp
   ORDER BY hot.hot DESC, hot.id DESC
   LIMIT _limit;
@@ -167,7 +172,24 @@ BEGIN
   END IF;
   __hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ) );
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY
+  WITH payout AS
+  (
+    SELECT
+        hp1.id,
+        ( hp1.payout + hp1.pending_payout ) as all_payout,
+        blacklist.source as source
+    FROM hive_posts hp1
+    JOIN hive_accounts_view ha ON hp1.author_id = ha.id
+    LEFT OUTER JOIN blacklisted_by_observer_view blacklist ON (blacklist.observer_id = __observer_id AND blacklist.blacklisted_id = hp1.author_id)
+    WHERE hp1.tags_ids @> __hive_tag 
+      AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout 
+      AND ha.is_grayed AND ( hp1.payout + hp1.pending_payout ) > 0
+      AND ( __post_id = 0 OR ( hp1.payout + hp1.pending_payout ) < __payout_limit OR ( ( hp1.payout + hp1.pending_payout ) = __payout_limit AND hp1.id < __post_id ) )
+    ORDER BY ( hp1.payout + hp1.pending_payout ) DESC, hp1.id DESC
+    LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -206,21 +228,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       payout.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          ( hp1.payout + hp1.pending_payout ) as all_payout,
-          blacklisted_by_observer_view.source as source
-      FROM
-          hive_posts hp1
-          JOIN hive_accounts_view ha ON hp1.author_id = ha.id
-          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.tags_ids @> __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND ha.is_grayed AND ( hp1.payout + hp1.pending_payout ) > 0
-          AND ( __post_id = 0 OR ( hp1.payout + hp1.pending_payout ) < __payout_limit OR ( ( hp1.payout + hp1.pending_payout ) = __payout_limit AND hp1.id < __post_id ) )
-      ORDER BY ( hp1.payout + hp1.pending_payout ) DESC, hp1.id DESC
-      LIMIT _limit
-  ) as payout,
+  FROM payout,
   LATERAL get_post_view_by_id(payout.id) hp
   ORDER BY payout.all_payout DESC, payout.id DESC
   LIMIT _limit;
@@ -245,7 +253,24 @@ BEGIN
   END IF;
   __hive_category = find_category_id( _category, True );
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY
+  WITH payout AS
+  (
+    SELECT
+        hp1.id,
+        ( hp1.payout + hp1.pending_payout ) as all_payout,
+        blacklist.source as source
+    FROM
+        hive_posts hp1
+        LEFT OUTER JOIN blacklisted_by_observer_view blacklist ON (blacklist.observer_id = __observer_id AND blacklist.blacklisted_id = hp1.author_id)
+    WHERE hp1.category_id = __hive_category 
+        AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth > 0
+        AND ( __post_id = 0 OR ( hp1.payout + hp1.pending_payout ) < __payout_limit OR ( ( hp1.payout + hp1.pending_payout ) = __payout_limit AND hp1.id < __post_id ) )
+        AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
+    ORDER BY ( hp1.payout + hp1.pending_payout ) DESC, hp1.id DESC
+    LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -284,21 +309,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       payout.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          ( hp1.payout + hp1.pending_payout ) as all_payout,
-          blacklisted_by_observer_view.source as source
-      FROM
-          hive_posts hp1
-          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.category_id = __hive_category AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth > 0
-          AND ( __post_id = 0 OR ( hp1.payout + hp1.pending_payout ) < __payout_limit OR ( ( hp1.payout + hp1.pending_payout ) = __payout_limit AND hp1.id < __post_id ) )
-          AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
-      ORDER BY ( hp1.payout + hp1.pending_payout ) DESC, hp1.id DESC
-      LIMIT _limit
-  ) as payout,
+  FROM payout,
   LATERAL get_post_view_by_id(payout.id) hp
   ORDER BY payout.all_payout DESC, payout.id DESC
   LIMIT _limit;
@@ -325,7 +336,25 @@ BEGIN
   __hive_category = find_category_id( _category, True );
   __head_block_time = head_block_time();
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY
+  WITH payout AS
+  (
+    SELECT
+        hp1.id,
+        ( hp1.payout + hp1.pending_payout ) as all_payout,
+        blacklist.source as source
+    FROM
+        hive_posts hp1
+        LEFT OUTER JOIN blacklisted_by_observer_view blacklist ON (blacklist.observer_id = __observer_id AND blacklist.blacklisted_id = hp1.author_id)
+    WHERE hp1.category_id = __hive_category 
+        AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout
+        AND ( ( NOT _bridge_api AND hp1.depth = 0 ) OR ( _bridge_api AND hp1.payout_at BETWEEN __head_block_time + interval '12 hours' AND __head_block_time + interval '36 hours' ) )
+        AND ( __post_id = 0 OR ( hp1.payout + hp1.pending_payout ) < __payout_limit OR ( ( hp1.payout + hp1.pending_payout ) = __payout_limit AND hp1.id < __post_id ) )
+        AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
+    ORDER BY ( hp1.payout + hp1.pending_payout ) DESC, hp1.id DESC
+    LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -364,22 +393,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       payout.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          ( hp1.payout + hp1.pending_payout ) as all_payout,
-          blacklisted_by_observer_view.source as source
-      FROM
-          hive_posts hp1
-          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.category_id = __hive_category AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout
-          AND ( ( NOT _bridge_api AND hp1.depth = 0 ) OR ( _bridge_api AND hp1.payout_at BETWEEN __head_block_time + interval '12 hours' AND __head_block_time + interval '36 hours' ) )
-          AND ( __post_id = 0 OR ( hp1.payout + hp1.pending_payout ) < __payout_limit OR ( ( hp1.payout + hp1.pending_payout ) = __payout_limit AND hp1.id < __post_id ) )
-          AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
-      ORDER BY ( hp1.payout + hp1.pending_payout ) DESC, hp1.id DESC
-      LIMIT _limit
-  ) as payout,
+  FROM payout,
   LATERAL get_post_view_by_id(payout.id) hp
   ORDER BY payout.all_payout DESC, payout.id DESC
   LIMIT _limit;
@@ -404,7 +418,24 @@ BEGIN
   END IF;
   __hive_tag = ARRAY_APPEND( __hive_tag,  find_tag_id( _tag, True ) );
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY 
+  WITH promoted AS
+  (
+    SELECT
+        hp1.id,
+        hp1.promoted as promoted,
+        blacklist.source as source
+    FROM
+        hive_posts hp1
+        LEFT OUTER JOIN blacklisted_by_observer_view blacklist ON (blacklist.observer_id = __observer_id AND blacklist.blacklisted_id = hp1.author_id)
+    WHERE hp1.tags_ids @> __hive_tag 
+        AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.promoted > 0
+        AND ( __post_id = 0 OR hp1.promoted < __promoted_limit OR ( hp1.promoted = __promoted_limit AND hp1.id < __post_id ) )
+        AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
+    ORDER BY hp1.promoted DESC, hp1.id DESC
+    LIMIT _limit
+  )
+  SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -443,21 +474,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       promoted.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          hp1.promoted as promoted,
-          blacklisted_by_observer_view.source as source
-      FROM
-          hive_posts hp1
-          LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.tags_ids @> __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.promoted > 0
-          AND ( __post_id = 0 OR hp1.promoted < __promoted_limit OR ( hp1.promoted = __promoted_limit AND hp1.id < __post_id ) )
-          AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
-      ORDER BY hp1.promoted DESC, hp1.id DESC
-      LIMIT _limit
-  ) as promoted,
+  FROM promoted,
   LATERAL get_post_view_by_id(promoted.id) hp
   ORDER BY promoted.promoted DESC, promoted.id DESC
   LIMIT _limit;
@@ -482,7 +499,24 @@ BEGIN
   END IF;
   __hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ));
   __observer_id = find_account_id(_observer, True);
-  RETURN QUERY SELECT
+  RETURN QUERY 
+  WITH trends AS
+  (
+    SELECT
+        hp1.id,
+        hp1.sc_trend as trend,
+        blacklist.source as source
+    FROM
+       hive_posts hp1
+       LEFT OUTER JOIN blacklisted_by_observer_view blacklist ON (blacklist.observer_id = __observer_id AND blacklist.blacklisted_id = hp1.author_id)
+    WHERE hp1.tags_ids @> __hive_tag 
+        AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth = 0
+        AND ( __post_id = 0 OR hp1.sc_trend < __trending_limit OR ( hp1.sc_trend = __trending_limit AND hp1.id < __post_id ) )
+        AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
+    ORDER BY hp1.sc_trend DESC, hp1.id DESC
+    LIMIT _limit
+  )
+SELECT
       hp.id,
       hp.author,
       hp.parent_author,
@@ -521,21 +555,7 @@ BEGIN
       hp.curator_payout_value,
       hp.is_muted,
       trends.source
-  FROM
-  (
-      SELECT
-          hp1.id,
-          hp1.sc_trend as trend,
-          blacklisted_by_observer_view.source as source
-      FROM
-         hive_posts hp1
-         LEFT OUTER JOIN blacklisted_by_observer_view ON (blacklisted_by_observer_view.observer_id = __observer_id AND blacklisted_by_observer_view.blacklisted_id = hp1.author_id)
-      WHERE hp1.tags_ids @> __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth = 0
-          AND ( __post_id = 0 OR hp1.sc_trend < __trending_limit OR ( hp1.sc_trend = __trending_limit AND hp1.id < __post_id ) )
-          AND (NOT EXISTS (SELECT 1 FROM muted_accounts_by_id_view WHERE observer_id = __observer_id AND muted_id = hp1.author_id))
-      ORDER BY hp1.sc_trend DESC, hp1.id DESC
-      LIMIT _limit
-  ) as trends,
+  FROM trends,
   LATERAL get_post_view_by_id(trends.id) hp
   ORDER BY trends.trend DESC, trends.id DESC
   LIMIT _limit;