Re: Additional improvements to extended statistics

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

=C3=BAt 14. 1. 2020 v 0:00 odes=C3=ADlatel Tomas Vondra <tomas.vondra@2ndqu=
adrant.com>
napsal:

> Hi,
>
> Now that I've committed [1] which allows us to use multiple extended
> statistics per table, I'd like to start a thread discussing a couple of
> additional improvements for extended statistics. I've considered
> starting a separate patch for each, but that would be messy as those
> changes will touch roughly the same places. So I've organized it into a
> single patch series, with the simpler parts at the beginning.
>
> There are three main improvements:
>
> 1) improve estimates of OR clauses
>
> Until now, OR clauses pretty much ignored extended statistics, based on
> the experience that they're less vulnerable to misestimates. But it's a
> bit weird that AND clauses are handled while OR clauses are not, so this
> extends the logic to OR clauses.
>
> Status: I think this is fairly OK.
>
>
> 2) support estimating clauses (Var op Var)
>
> Currently, we only support clauses with a single Var, i.e. clauses like
>
>    - Var op Const
>    - Var IS [NOT] NULL
>    - [NOT] Var
>    - ...
>
> and AND/OR clauses built from those simple ones. This patch adds support
> for clauses of the form (Var op Var), of course assuming both Vars come
> from the same relation.
>
> Status: This works, but it feels a bit hackish. Needs more work.
>
>
> 3) support extended statistics on expressions
>
> Currently we only allow simple references to columns in extended stats,
> so we can do
>
>     CREATE STATISTICS s ON a, b, c FROM t;
>
> but not
>
>     CREATE STATISTICS s ON (a+b), (c + 1) FROM t;
>

+1 for expression's statisctics - it can be great feature.

Pavel


> This patch aims to allow this. At the moment it's a WIP - it does most
> of the catalog changes and stats building, but with some hacks/bugs. And
> it does not even try to use those statistics during estimation.
>
> The first question is how to extend the current pg_statistic_ext catalog
> to support expressions. I've been planning to do it the way we support
> expressions for indexes, i.e. have two catalog fields - one for keys,
> one for expressions.
>
> One difference is that for statistics we don't care about order of the
> keys, so that we don't need to bother with storing 0 keys in place for
> expressions - we can simply assume keys are first, then expressions.
>
> And this is what the patch does now.
>
> I'm however wondering whether to keep this split - why not to just treat
> everything as expressions, and be done with it? A key just represents a
> Var expression, after all. And it would massively simplify a lot of code
> that now has to care about both keys and expressions.
>
> Of course, expressions are a bit more expensive, but I wonder how
> noticeable that would be.
>
> Opinions?
>
>
> ragards
>
> [1] https://commitfest.postgresql.org/26/2320/
>
> --
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">=C3=BAt 14. 1. 2020 v=C2=A00:00 odes=
=C3=ADlatel Tomas Vondra &lt;<a href=3D"mailto:[email protected]=
">[email protected]</a>&gt; napsal:<br></div><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">Hi,<br>
<br>
Now that I&#39;ve committed [1] which allows us to use multiple extended<br=
>
statistics per table, I&#39;d like to start a thread discussing a couple of=
<br>
additional improvements for extended statistics. I&#39;ve considered<br>
starting a separate patch for each, but that would be messy as those<br>
changes will touch roughly the same places. So I&#39;ve organized it into a=
<br>
single patch series, with the simpler parts at the beginning.<br>
<br>
There are three main improvements:<br>
<br>
1) improve estimates of OR clauses<br>
<br>
Until now, OR clauses pretty much ignored extended statistics, based on<br>
the experience that they&#39;re less vulnerable to misestimates. But it&#39=
;s a<br>
bit weird that AND clauses are handled while OR clauses are not, so this<br=
>
extends the logic to OR clauses.<br>
<br>
Status: I think this is fairly OK.<br>
<br>
<br>
2) support estimating clauses (Var op Var)<br>
<br>
Currently, we only support clauses with a single Var, i.e. clauses like<br>
<br>
=C2=A0 =C2=A0- Var op Const<br>
=C2=A0 =C2=A0- Var IS [NOT] NULL<br>
=C2=A0 =C2=A0- [NOT] Var<br>
=C2=A0 =C2=A0- ...<br>
<br>
and AND/OR clauses built from those simple ones. This patch adds support<br=
>
for clauses of the form (Var op Var), of course assuming both Vars come<br>
from the same relation.<br>
<br>
Status: This works, but it feels a bit hackish. Needs more work.<br>
<br>
<br>
3) support extended statistics on expressions<br>
<br>
Currently we only allow simple references to columns in extended stats,<br>
so we can do<br>
<br>
=C2=A0 =C2=A0 CREATE STATISTICS s ON a, b, c FROM t;<br>
<br>
but not<br>
<br>
=C2=A0 =C2=A0 CREATE STATISTICS s ON (a+b), (c + 1) FROM t;<br></blockquote=
><div><br></div><div>+1 for expression&#39;s statisctics - it can be great =
feature.</div><div><br></div><div>Pavel</div><div><br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">
<br>
This patch aims to allow this. At the moment it&#39;s a WIP - it does most<=
br>
of the catalog changes and stats building, but with some hacks/bugs. And<br=
>
it does not even try to use those statistics during estimation.<br>
<br>
The first question is how to extend the current pg_statistic_ext catalog<br=
>
to support expressions. I&#39;ve been planning to do it the way we support<=
br>
expressions for indexes, i.e. have two catalog fields - one for keys,<br>
one for expressions.<br>
<br>
One difference is that for statistics we don&#39;t care about order of the<=
br>
keys, so that we don&#39;t need to bother with storing 0 keys in place for<=
br>
expressions - we can simply assume keys are first, then expressions.<br>
<br>
And this is what the patch does now.<br>
<br>
I&#39;m however wondering whether to keep this split - why not to just trea=
t<br>
everything as expressions, and be done with it? A key just represents a<br>
Var expression, after all. And it would massively simplify a lot of code<br=
>
that now has to care about both keys and expressions.<br>
<br>
Of course, expressions are a bit more expensive, but I wonder how<br>
noticeable that would be.<br>
<br>
Opinions?<br>
<br>
<br>
ragards<br>
<br>
[1] <a href=3D"https://commitfest.postgresql.org/26/2320/" rel=3D"noreferre=
r" target=3D"_blank">https://commitfest.postgresql.org/26/2320/</a><br>
<br>
-- <br>
Tomas Vondra=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
<a href=3D"http://www.2ndQuadrant.com" rel=3D"noreferrer" target=3D"_blank"=
>http://www.2ndQuadrant.com</a><br>
PostgreSQL Development, 24x7 Support, Remote DBA, Training &amp; Services <=
br>
</blockquote></div></div>

--000000000000f48dc9059c15385e--