Skip to content
Snippets Groups Projects

GIN index uses to find posts by tag

Merged GIN index uses to find posts by tag
mi_gin_posts_tags into develop
1 unresolved thread
Merged Marcin requested to merge mi_gin_posts_tags into develop
1 unresolved thread
Viewing commit 1f91cbc5
Show latest version
8 files
+ 56
− 125
Compare changes
  • Side-by-side
  • Inline
Files
8
  • 1f91cbc5
    Marcin
    GIN index uses to find posts by tag · 1f91cbc5
    Marcin authored 
    -remove hive_post_tags table
-extend hive_posts for a column with an arrays of tags id
-tags are added next after a post, not in paralles as it was previously
-fix bug with not removing tags for a post whose author removed tags
during edition
hive/db/sql_scripts/bridge_get_ranked_post_for_tag.sql
+ 19
− 24
@@ -5,10 +5,10 @@ AS
$function$
DECLARE
__post_id INT;
__hive_tag INT;
__hive_tag INT[];
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
__hive_tag = find_tag_id( _tag, True );
__hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ));
RETURN QUERY SELECT
hp.id,
hp.author,
@@ -51,10 +51,9 @@ BEGIN
SELECT
hp1.id
FROM
hive_post_tags hpt
JOIN hive_posts hp1 ON hp1.id = hpt.post_id
hive_posts hp1
JOIN hive_accounts_view ha ON hp1.author_id = ha.id
WHERE hpt.tag_id = __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 )
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
ORDER BY hp1.id DESC
LIMIT _limit
@@ -74,13 +73,13 @@ $function$
DECLARE
__post_id INT;
__hot_limit FLOAT;
__hive_tag INT;
__hive_tag INT[];
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
IF __post_id <> 0 THEN
SELECT hp.sc_hot INTO __hot_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__hive_tag = find_tag_id( _tag, True );
__hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ));
RETURN QUERY SELECT
hp.id,
hp.author,
@@ -124,9 +123,8 @@ BEGIN
hp1.id
, hp1.sc_hot as hot
FROM
hive_post_tags hpt
JOIN hive_posts hp1 ON hp1.id = hpt.post_id
WHERE hpt.tag_id = __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth = 0
hive_posts hp1
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 ) )
ORDER BY hp1.sc_hot DESC, hp1.id DESC
LIMIT _limit
@@ -146,13 +144,13 @@ $function$
DECLARE
__post_id INT;
__payout_limit hive_posts.payout%TYPE;
__hive_tag INT;
__hive_tag INT[];
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
IF __post_id <> 0 THEN
SELECT ( hp.payout + hp.pending_payout ) INTO __payout_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__hive_tag = find_tag_id( _tag, True );
__hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ) );
RETURN QUERY SELECT
hp.id,
hp.author,
@@ -197,9 +195,8 @@ BEGIN
, ( hp1.payout + hp1.pending_payout ) as all_payout
FROM
hive_posts hp1
JOIN hive_post_tags hpt ON hp1.id = hpt.post_id
JOIN hive_accounts_view ha ON hp1.author_id = ha.id
WHERE hpt.tag_id = __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND ha.is_grayed AND ( hp1.payout + hp1.pending_payout ) > 0
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
@@ -364,13 +361,13 @@ $function$
DECLARE
__post_id INT;
__promoted_limit hive_posts.promoted%TYPE;
__hive_tag INT;
__hive_tag INT[];
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
IF __post_id <> 0 THEN
SELECT hp.promoted INTO __promoted_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__hive_tag = find_tag_id( _tag, True );
__hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ) );
RETURN QUERY SELECT
hp.id,
hp.author,
@@ -414,9 +411,8 @@ BEGIN
hp1.id
, hp1.promoted as promoted
FROM
hive_post_tags hpt
JOIN hive_posts hp1 ON hp1.id = hpt.post_id
WHERE hpt.tag_id = __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.promoted > 0
hive_posts hp1
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 ) )
ORDER BY hp1.promoted DESC, hp1.id DESC
LIMIT _limit
@@ -436,13 +432,13 @@ $function$
DECLARE
__post_id INT;
__trending_limit FLOAT;
__hive_tag INT;
__hive_tag INT[];
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
IF __post_id <> 0 THEN
SELECT hp.sc_trend INTO __trending_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__hive_tag = find_tag_id( _tag, True );
__hive_tag = ARRAY_APPEND( __hive_tag, find_tag_id( _tag, True ));
RETURN QUERY SELECT
hp.id,
hp.author,
@@ -486,9 +482,8 @@ BEGIN
hp1.id
, hp1.sc_trend as trend
FROM
hive_post_tags hpt
JOIN hive_posts hp1 ON hp1.id = hpt.post_id
WHERE hpt.tag_id = __hive_tag AND hp1.counter_deleted = 0 AND NOT hp1.is_paidout AND hp1.depth = 0
hive_posts hp1
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 ) )
ORDER BY hp1.sc_trend DESC, hp1.id DESC
LIMIT _limit