Slow Query with LIMIT
Adding LIMIT to the query often makes Postgres choose a different query plan. If statistics or cost estimates stray too far from actual data distribution / actual costs, you may end up with a slower query, even though Postgres reckoned it would be faster. Irregular data distribution may cause the query planner to misjudge the selectivity of WHERE conditions.

In particular, increasing the STATISTICS target for status, account_id, created_at and id might help.

Details:


How can I avoid PostgreSQL sometimes choosing a bad query plan for one of two nearly identical queries?


Aside from that, a (partial) multi column index may help. Depends on the complete situation. Which of your WHERE conditions are constant? How can they vary? Is some additional maintenance cost and disk space acceptable for a special index?

For example:

CREATE INDEX messages_special_idx ON messages (account_id, created_at, id)
WHERE status = 'unread'
AND   created_at &gt;= '2014-01-01 01:00:00';


Run ANALYZE messages; before you try your queries again.