Slow query

[ety001]

SELECT post_id FROM hive_posts_cache WHERE depth = ? AND category = ? AND post_id IN (SELECT post_id FROM hive_post_tags WHERE tag = ?) ORDER BY post_id DESC LIMIT ?

[only-dev-time]

The query is used in pids_by_query. with params:

sort = created,
tag = hive-xxxx,
start_permlink = None

Is called by condenser_api.get_discussions_by_created and condenser_api.get_state

As I see there is no index for this query.

[ety001]

Jun 19 15:18:54 ip-172-31-22-208 beta-hivemind[3407]: INFO:hive.utils.stats:  16.4%    9854ms      5.54     1778x -- SELECT parent_id, array_agg(id) FROM hive_posts WHERE parent_id IN :ids AND is_deleted ... NOT IN (SELECT author FROM hive_posts_status WHERE list_type = '3') GROUP BY parent_id

Jun 19 15:18:54 ip-172-31-22-208 beta-hivemind[3407]: INFO:hive.utils.stats:  12.7%    7591ms      8.51      892x -- SELECT post_id, community_id, author, permlink, title, body, category, depth, promoted ... idden, is_grayed, total_votes, flag_weight FROM hive_posts_cache WHERE post_id IN :ids

Jun 19 15:18:54 ip-172-31-22-208 beta-hivemind[3407]: INFO:hive.utils.stats:   8.7%    5193ms      2.79     1859x -- SELECT post_id FROM hive_posts_status WHERE list_type = '1' AND post_id IN :ids

Jun 19 15:18:54 ip-172-31-22-208 beta-hivemind[3407]: INFO:hive.utils.stats:   8.0%    4824ms     24.24      199x -- SELECT post_id FROM hive_feed_cache WHERE account_id = :account_id AND post_id NOT IN  ... _id FROM hive_reblogs WHERE account = :account)) ORDER BY created_at DESC LIMIT :limit