Simple query is slow on 4M-rows table
In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index.

This will probably lower the query execution significantly if you have a large enough buffer pool and the query is relatively frequent.

If after doing that, the query is still slow, you will need to do (pre)caching in order to speed up the query execution.