scriber_id AS subs_109 FROM subscription subs LEFT OUTER JOIN offer offer1 ON subs.offer_id = offer1.id WHERE offer1.is_external_lifecycle_management = FALSE
You've made the left outer join specification pointless here.
What version?
The fundamental issue is the first query is optimistic - it expects it will find at least one result, potentially fairly quickly, if it just walks the possible subscriptions in order. But it doesn't ever find a result after scanning the whole thing.
The second query figures it can prove that there are no possible results because the offer1 restriction makes the final result an empty set.
I'm not sure how to rework the query here - though I'd at least clean up the lie that you are doing an outer join even if that doesn't impact the query plan (though it may very well do that).
The statistics seem OK, and if you don't actually make changes to the data, repeatedly running analyze is no better than running it once.
You may find some success using a materialized CTE explicitly to scan offer and then semi-join that result to subscriptions; the empty materialized CTE needed for the semi-join would then enable the short-circuit that is seen in the second query.
The convention on these lists is to inline or bottom post, trimming context.
