public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Kenny Bachman <[email protected]>
Cc: Gaurav Anand <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: PG Query Planner
Date: Tue, 19 Apr 2022 15:23:11 -0700
Message-ID: <CAKFQuwaVcULRyJwco1iSNoNb_LC5oUDui8gSPMfgYT_gMGbPEg@mail.gmail.com> (raw)
In-Reply-To: <CAC0w7LLXqLrRLt+tGCx8d1z4YCZo2ydakSMPLEOzGTQ_a10-zw@mail.gmail.com>
References: <CAC0w7L+wLa6ATLFrmMKui+q0XZhgxNLptpge_1BF+L0ULFeedg@mail.gmail.com>
<CAMZ99nfaAGBHhzLuzqTAxnCmy7gnPm+6KUxZPUY285Vs4N7hHA@mail.gmail.com>
<CAC0w7LLXqLrRLt+tGCx8d1z4YCZo2ydakSMPLEOzGTQ_a10-zw@mail.gmail.com>
On Tue, Apr 19, 2022 at 10:57 AM Kenny Bachman <[email protected]>
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.
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: PG Query Planner
In-Reply-To: <CAKFQuwaVcULRyJwco1iSNoNb_LC5oUDui8gSPMfgYT_gMGbPEg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox