A hint on PostgreSQL ranking query optimization

Recently I’ve stumbled upon a few similar cases of ineffective queries which I want to share with you.