@emrebeyler I added an index for it (took about 15 minutes) with
CREATE INDEX ON hive_posts_cache((json::json->>'app'));
and then I could query like this
SELECT json::json->>'app' as app, author, permlink, body, created_at from hive_posts_cache where json::json->>'app' like 'busy%' order by post_id desc limit 100
Not with the api, yet :) I will add it if it returns in a feasible time to serve in the api.
@emrebeyler I added an index for it (took about 15 minutes) with
CREATE INDEX ON hive_posts_cache((json::json->>'app'));and then I could query like this
SELECT json::json->>'app' as app, author, permlink, body, created_at from hive_posts_cache where json::json->>'app' like 'busy%' order by post_id desc limit 100This is great @jrawsthorne!
How long does this example query take to complete after the index?
145ms
Fantastic. Thanks for the tip. I will try adding an additional index after the hive update/migration.