mvanhalen

-- Additional indexes for BitClout Node Postgres DB
-- Indexes take space. more columns added = more space But it can speed up queries a lot. Use wisely. 
-- Index creation can take some time to complete

-- pg_post indexes. Based on what you want you might not need all
-- Index: pg_posts_timestamp_desc to sort posts when when quering by timestamp. From most recent to old

CREATE INDEX pg_posts_timestamp_desc
    ON public.pg_posts USING btree

mvanhalen

--get last records from pg_post which correct hash for post
select encode(post_hash,'hex'),body,pg_posts.timestamp,like_count,reclout_count,quote_reclout_count,diamond_count,comment_count,nft from pg_posts order by pg_posts.timestamp desc limit 10 

--get last records from pg_post which correct hash for post and username included
select pg_profiles.username, encode(post_hash,'hex'),body,pg_posts.timestamp,like_count,reclout_count,quote_reclout_count,diamond_count,comment_count,nft from pg_posts inner join pg_profiles on pg_posts.poster_public_key = pg_profiles.public_key order by pg_posts.timestamp desc limit 10 

--get last records from pg_post for a user based on username which correct hash for post
select pg_profiles.username, encode(post_hash,'hex'),body,pg_posts.timestamp,like_count,reclout_count,quote_reclout_count,diamond_count,comment_count,nft from pg_posts inner join pg_profiles on pg_posts.poster_public_key = pg_profiles.public_key where username='mvanhalen' order by pg_posts.timestamp desc limit 1

mvanhalen

using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Org.BouncyCastle.Asn1;
using Org.BouncyCastle.Asn1.Sec;
using Org.BouncyCastle.Crypto.Digests;