Looking for ways to optimize sql query with inner join
Your statistics are way off.

In the scan on index_event_subscriptions_on_user_id_group_id it estimates 4 rows returned, yet 7295 are returned.

WHERE (event_subscriptions.user_id = 456) 
AND (event_subscriptions.group_id IN (123, 321, 222, 333)) 
AND (event_subscriptions.valid IS true) 


The additional AND (events.type != 1) is not removing anything.

I would try running ANALYZE event_subscriptions (or, even better, if you can afford it VACUUM FULL ANALYZE event_subscriptions see if that fixes it. If it doesn't I would try ALTER TABLE event_subscriptions SET STATISTICS 10000 and try ANALYZE event_subscriptions again to see if that fixes it.

If neither of these worked, I would try pushing events.type check down..

INNER JOIN event_subscriptions 
  ON event_subscriptions.event_id = events.id 
  AND events.type &lt;&gt; 1


At the point that you're talking 220 million rows, you may also want to consider partitioning.