Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ngwFy-000627-EB for pgsql-admin@arkaria.postgresql.org; Tue, 19 Apr 2022 22:23:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ngwFx-0000Xv-9Z for pgsql-admin@arkaria.postgresql.org; Tue, 19 Apr 2022 22:23:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ngwFw-0000R3-R8 for pgsql-admin@lists.postgresql.org; Tue, 19 Apr 2022 22:23:32 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ngwFu-0002hV-A1 for pgsql-admin@lists.postgresql.org; Tue, 19 Apr 2022 22:23:32 +0000 Received: by mail-ed1-x52f.google.com with SMTP id s25so22593393edi.13 for ; Tue, 19 Apr 2022 15:23:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=YpGNutGapgpZiH99Wg9rRgyXgioCcWEpwPMYLtMLSTo=; b=pZ9AZF3+56/zWIqghIek3RIcC0dCRGly2jKKDquW7X+Q8b3Cm/y6p2O25rtnsPd0oH D0/Wd/yTpEnuww508r7PoxcRsCTozxsRQWJGGbhtzC6FkqWyZ1GOoK93li/4HdkAFH7z Y5ADIHTZydCetlrMXVwXvc+lxqJq6Q36YYTJYksgGmcXsIYsoREVAkfLMn5ikfboNaWP /lpYAHEydeEA195SuLHfDYPUnbOI+g7kFBg6RAT4iEjV8wX1mZnP4PJ55JZIhkxb7s77 T2SbDZar/FH4rkwCIwXoEXgb0wfajYHCg3rMIQMRSxB20qvKovAFWEWwkCz7xwfxs0Qq VRbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=YpGNutGapgpZiH99Wg9rRgyXgioCcWEpwPMYLtMLSTo=; b=0g+62pbxc8uFe87ehqW7uc82s96xSCMA0RUrcqeO+rFFxJY7LXnbdvEe8X9+IyFH2n ZEuOnsWB6Sbg+d0WHFYgv6ZUs4e2gfHZ+SXIWvlIjogepdcJdheYqBwgJ5f/1S0xF7wN pUJJzaAO/cj+SW3iy0YHfSHXIBWwMVRnweoBudqH4D3PLX9JM1OmLn1iT4ph30cLPWuc pWTi6eI0ijF3W3mEOFMD5VTFiBW+wwRs7i+FxpwnAbeQYBrYeqcxs51A8uw5fzcfNeZh CCThDNmsrKZHjUuHdG41pAFQeUqB2l8ApTnjyJhO/KRc+wfUE+AxVGRuXXcslMYwXF8b wgUQ== X-Gm-Message-State: AOAM530XgGbZEsmc3C9t0n3R7nnxKA8CFUyYh/ITJ5O13lgeiCbpRcCO CGO58vHFfRZsnvYV0unSGxtJCo9NmDX8aFP2ddg= X-Google-Smtp-Source: ABdhPJyCwFHcB7ogWHXtUL9bNhjpfP/3Yu/NsqAvvgwNKJyJJvJgqQe+NTWWOvXrGJL9xWFWNlHMhQka2y0LFZG4c+Q= X-Received: by 2002:a05:6402:c84:b0:41d:72b5:fb05 with SMTP id cm4-20020a0564020c8400b0041d72b5fb05mr19302952edb.361.1650407008750; Tue, 19 Apr 2022 15:23:28 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 19 Apr 2022 15:23:11 -0700 Message-ID: Subject: Re: PG Query Planner To: Kenny Bachman Cc: Gaurav Anand , Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000007dcdd005dd09535a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007dcdd005dd09535a Content-Type: text/plain; charset="UTF-8" On Tue, Apr 19, 2022 at 10:57 AM Kenny Bachman 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. --0000000000007dcdd005dd09535a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 19, 2022 at 10:57 AM Kenny Bachman <kenny.bachman17@gmail.com> wro= te:
SELECT subs.id AS id1_109,
scriber_id= AS subs_109
FROM subscription subs
LEFT OUTER JOIN offer offer1 ON subs.offer_id =3D offer1= .id
WHERE offer1.is_external_lifecycle_management =3D FALSE

You've made the left outer join= specification pointless here.

What versi= on?

The fundamental issue is the first query is optimi= stic - it expects it will find at least one result, potentially fairly quic= kly, if it just walks the possible subscriptions in order.=C2=A0 But it doe= sn't ever find a result after scanning=C2=A0the 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 - thou= gh 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 actuall= y make changes to the data,=C2=A0repeatedly running analyze is no better th= an 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 t= hen enable the short-circuit that is seen in the second query.

=
The convention on these lists is to i= nline or bottom post, trimming context.
<= br>
David J.


--0000000000007dcdd005dd09535a--