Re: Merge join doesn't seem to break early when I (and planner) think it should - 10.4

--000000000000b6c28c059ab251bf
Content-Type: text/plain; charset="UTF-8"

On Thu, Dec 26, 2019 at 6:57 PM Timothy Garnett <[email protected]>
wrote:

>
> So far I've been unable to create a smaller / toy example that exhibits
> the same behavior. Some things that may be unusual about the situation:
> keytbl is bigint and the values are large (all are > 2^48) and sparse/dense
> (big chunks where the id advances by 1 separated by large (> 2^48) regions
> with no rows), the top 200k or so rows of tmp_table by keytbl don't have a
> corresponding row in tbl, and this is a bit of an older dot release
> (10.4).  I have a workaround (disabling merge join for the query) so I'm
> mostly trying to figure out what's going on and if I'm understanding the
> situation correctly.
>

Can you share the toy example, using things like random() and
generate_series() to populate it?  Preferably scaled down to 10 million
rows or so in the larger table.

Does it reproduce in 10.11?  If not, then there is really nothing worth
looking into.  Any fix that can be done would certainly not be re-released
into 10.4.  And does it reproduce in 12.1 or 13dev?  Because chances are
any improvement wouldn't even be back-patches into any minor release at all.


> It's interesting that even if it worked as expected, the merge join plan
> seems a lot riskier in that if the analyze didn't catch a single large
> outlier value of keytbl in tmp_tbl or a row with a large value for keytbl
> was inserted into tmp_tbl since the last analyze it could be forced to walk
> the entire index of the tbl (which based on the filter count looks like it
> involves touching each row of this large table for the filter even if it
> doesn't have a corresponding row to merge to).
>

There has been discussion of building a riskiness factor into the planner,
but it has never gone anywhere.  Everything has its own risk (with Hash
Joins, for example, the data could be pathological and everything might
hash to a few buckets, or 32 bits of hashcode might not be enough bits).
By the time you can adequately analyze all the risks, you would probably
learn enough to just make the planner better absolutely, without adding
another dimension to all the things it considers.

Cheers,

Jeff

>

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Dec 26, 2019 at 6:57 PM Timothy G=
arnett &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">tgarne=
[email protected]</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><br></div><d=
iv>So far I&#39;ve been unable to create a smaller / toy example that exhib=
its the same behavior. Some things that may be unusual about the situation:=
 keytbl is bigint and the values are large (all are &gt; 2^48) and sparse/d=
ense (big chunks where the id advances by 1 separated by large (&gt; 2^48) =
regions with no rows), the top 200k or so rows of tmp_table by keytbl don&#=
39;t have a corresponding row in tbl, and this is a bit of an older dot rel=
ease (10.4).=C2=A0 I have a workaround (disabling merge join for the query)=
 so I&#39;m mostly trying to figure out what&#39;s going on and if I&#39;m =
understanding the situation correctly.</div></div></blockquote><div><br></d=
iv><div>Can you share the toy example, using things like random() and gener=
ate_series() to populate it?=C2=A0 Preferably scaled down to 10 million row=
s or so in the larger table.</div><div><br></div><div>Does it reproduce in =
10.11?=C2=A0 If not, then there is really nothing worth looking into.=C2=A0=
 Any fix that can be done would certainly not be re-released into 10.4.=C2=
=A0 And does it reproduce in 12.1 or 13dev?=C2=A0 Because chances are any i=
mprovement wouldn&#39;t even be back-patches into any minor release at all.=
</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr"><div>It&#39;s interesting that even if it worked as expected=
, the merge join plan seems a lot riskier in that if the analyze didn&#39;t=
 catch a single large outlier value of keytbl=C2=A0in tmp_tbl or a row with=
 a large value for keytbl was inserted into tmp_tbl since the last analyze =
it could be forced to walk the entire index of the tbl (which based on the =
filter count looks like it involves touching each row of this large table f=
or the filter even if it doesn&#39;t have a corresponding row to merge to).=
</div></div></blockquote><div><br></div><div>There has been discussion of b=
uilding a riskiness factor into the planner, but it has never gone anywhere=
.=C2=A0 Everything has its own risk (with Hash Joins, for example, the data=
 could be pathological and everything might hash to a few buckets, or 32 bi=
ts of hashcode might not be enough bits).=C2=A0 By the time you can adequat=
ely analyze all the risks, you would probably learn enough to just make the=
 planner better absolutely, without adding another dimension to all the thi=
ngs it considers.</div><div>=C2=A0</div><div>Cheers,</div><div><br></div><d=
iv>Jeff</div><blockquote 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>

--000000000000b6c28c059ab251bf--