Re: PG Query Planner

On Tue, Apr 19, 2022 at 10:57 AM Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com>
wrote:

> 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
>

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.

David J.