SELECT subs.id AS id1_109, 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 AND subs.job_next_process_time < '2022-04-19 09:25:25.535' AND subs.job_in_progress = FALSE ORDER BY subs.id ASC LIMIT 1 ;
An easy (if you can control the queries) way to force the faster plan is to prohibit it from using using the index to fulfill the ORDER BY, by changing it to "ORDER BY subs.id+0 ASC LIMIT 1"
As for getting the planner to get a better plan on its own, I don't think there is much hope. The difference between 0 rows with is_external_lifecycle_management=FALSE, and 1 row meeting that, is the smallest possible difference. Yet still the ratio between them is infinite. Changing a histogram bin count or adding a multivariate statistic is not going to change that.
Maybe the executor should be smart enough to cut off the nested loop once it sees the Materialize will never return a row. But that is not a change you can make in user-land.
