MIME-Version: 1.0
References: 
 <CAC0w7L+wLa6ATLFrmMKui+q0XZhgxNLptpge_1BF+L0ULFeedg@mail.gmail.com>
 <CAMZ99nfaAGBHhzLuzqTAxnCmy7gnPm+6KUxZPUY285Vs4N7hHA@mail.gmail.com>
 <CAC0w7LLXqLrRLt+tGCx8d1z4YCZo2ydakSMPLEOzGTQ_a10-zw@mail.gmail.com>
In-Reply-To: 
 <CAC0w7LLXqLrRLt+tGCx8d1z4YCZo2ydakSMPLEOzGTQ_a10-zw@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Wed, 20 Apr 2022 00:03:20 -0400
Message-ID: 
 <CAMkU=1wyoF64YXrkV83D5zyjq+T_cE+=h-vq2GLXGfn7wN4w6g@mail.gmail.com>
Subject: Re: PG Query Planner
To: Kenny Bachman <kenny.bachman17@gmail.com>
Cc: Gaurav Anand <gaurav.anand@saama.com>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000096594f05dd0e1394"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMkU%3D1wyoF64YXrkV83D5zyjq%2BT_cE%2B%3Dh-vq2GLXGfn7wN4w6g%40mail.gmail.com>
Precedence: bulk

--00000000000096594f05dd0e1394
Content-Type: text/plain; charset="UTF-8"

On Tue, Apr 19, 2022 at 1:57 PM Kenny Bachman <kenny.bachman17@gmail.com>
wrote:

> Hello,
>
> My query is :
>
> 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.

Cheers,

Jeff

>

--00000000000096594f05dd0e1394
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Apr 19, 2022 at 1:57 PM Kenny Bac=
hman &lt;<a href=3D"mailto:kenny.bachman17@gmail.com">kenny.bachman17@gmail=
.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hello,<div><br></div><div=
>My query is :</div><div><br></div><div>SELECT <a href=3D"http://subs.id" t=
arget=3D"_blank">subs.id</a> AS id1_109, <br>scriber_id AS subs_109 <br>FRO=
M subscription subs<br>LEFT OUTER JOIN offer offer1 <br>ON subs.offer_id =
=3D <a href=3D"http://offer1.id" target=3D"_blank">offer1.id</a> <br>WHERE =
offer1.is_external_lifecycle_management =3D FALSE <br>AND subs.job_next_pro=
cess_time &lt; &#39;2022-04-19 09:25:25.535&#39; <br>AND subs.job_in_progre=
ss =3D FALSE<br>ORDER BY <a href=3D"http://subs.id" target=3D"_blank">subs.=
id</a> ASC LIMIT 1 ;<br></div></div></blockquote><div><br></div><div>An eas=
y (if you can control the queries) way to force the faster plan is to prohi=
bit it from using using the index to fulfill the ORDER BY, by changing it t=
o &quot;ORDER BY=C2=A0<a href=3D"http://subs.id">subs.id</a>+0=C2=A0ASC LIM=
IT 1&quot;</div><div><br></div><div>As for getting the planner to get a bet=
ter plan on its own, I don&#39;t think there is much hope.=C2=A0 The differ=
ence between 0 rows with is_external_lifecycle_management=3DFALSE, and 1 ro=
w meeting that, is the smallest possible difference. Yet still the ratio be=
tween them is infinite.=C2=A0 Changing a histogram bin count or adding a mu=
ltivariate statistic is not going to change that.</div><div><br></div><div>=
Maybe the executor should be smart enough to cut off the nested loop once i=
t sees the=C2=A0<span style=3D"color:rgb(80,0,80)">Materialize will never r=
eturn a row.=C2=A0 But that is not a change you can make in user-land.</spa=
n></div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">
</blockquote></div></div>

--00000000000096594f05dd0e1394--