Link: https://github.com/steemit/hivemind/pull/361

Docker Image: docker pull steemit/hivemind:latest-bc9ef13

Done

• Add database migration v22->v23 to create 4 optimization indexes:

  • idx_feed_cache_account_created_desc: Optimize ORDER BY created_at DESC
  • idx_feed_cache_created_account_post: Optimize JOIN and WHERE conditions
  • idx_follows_follower_following_state: Optimize JOIN performance
  • idx_follows_follower_state_created_desc: Optimize ORDER BY created_at DESC

• Add ANALYZE command support in db_adapter._is_write_query()

• Add comments in schema.py explaining migration-created indexes

Expected

These indexes are expected to improve query performance by 35-40% for:

• get_following queries (5.8ms -> 2.5ms)
• pids_by_blog queries (0.049ms -> 0.020ms)
• pids_by_feed_with_reblog queries (7.3ms -> 3.5ms)

The optimization work of Hivemind can probably come to a temporary end.