Thread: Selectivity estimation for inet operators

Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
New version of the selectivity estimation patch attached. I am adding
it to CommitFest 2014-06. Previous version of it reviewed by
Andreas Karlson on the previous CommitFest with the GiST support patch.
The new version includes join selectivity estimation.

Join selectivity is calculated in 4 steps:

* matching first MCV to second MCV
* searching first MCV in the second histogram
* searching second MCV in the first histogram
* searching boundaries of the first histogram in the second histogram

Comparing the lists with each other slows down the function when
statistics set to higher values. To avoid this problem I only use
log(n) values of the lists. It is the first log(n) value for MCV,
evenly separated values for histograms. In my tests, this optimization
does not affect the planning time when statistics = 100, but does
affect accuracy of the estimation. I can send the version without
this optimization, if slow down with larger statistics is not a problem
which should be solved on the selectivity estimation function.

I also attach the script I was using for testing and I left log statements
in the networkjoinsel() function to make testing easier. These statements
should be removed before commit.

Attachment

Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
I wanted to check the patch last time and found a bug effecting
MVC vs MVC part of the join selectivity. Fixed version attached.

Emre Hasegeli <emre@hasegeli.com>:
> Comparing the lists with each other slows down the function when
> statistics set to higher values. To avoid this problem I only use
> log(n) values of the lists. It is the first log(n) value for MCV,
> evenly separated values for histograms. In my tests, this optimization
> does not affect the planning time when statistics = 100, but does
> affect accuracy of the estimation. I can send the version without
> this optimization, if slow down with larger statistics is not a problem
> which should be solved on the selectivity estimation function.

Also, I changed this from log(n) to sqrt(n). It seems much better
now.

I try to explain the reason to processes some of the values with more
comments. I hope it is understandable.

Attachment

Re: Selectivity estimation for inet operators

From
Dilip kumar
Date:
On, 15 May 2014 14:04 Emre Hasegeli Wrote, 

> 
> * matching first MCV to second MCV
> * searching first MCV in the second histogram
> * searching second MCV in the first histogram
> * searching boundaries of the first histogram in the second histogram
> 
> Comparing the lists with each other slows down the function when
> statistics set to higher values. To avoid this problem I only use
> log(n) values of the lists. It is the first log(n) value for MCV,
> evenly separated values for histograms. In my tests, this optimization
> does not affect the planning time when statistics = 100, but does
> affect accuracy of the estimation. I can send the version without this
> optimization, if slow down with larger statistics is not a problem
> which should be solved on the selectivity estimation function.
>

I have started reviewing this patch, so far I have done basic reviews and some testing/debugging.

1. Patch applied to git head.
2. Basic testing works fine.

I have one query,

In inet_his_inclusion_selec function, 
When the constant matches only the right side of the bucket, and if it’s a last bucket then it's never considered as
partialmatch candidate.
 
In my opinion, if it's not a last bucket then for next bucket it will become left boundary and this will be treated as
partialmatch so no problem, but in-case of last bucket it can give wrong selectivity.
 

Can't we consider it as partial bucket match if it is last bucket ?

Apart from that there is one spell check you can correct
-- in inet_his_inclusion_selec comments
histogram boundies  -> histogram boundaries :)

Thanks & Regards,
Dilip Kumar





Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
Thank you for looking at it.

> In inet_his_inclusion_selec function,
> When the constant matches only the right side of the bucket, and if it’s a last bucket then it's never considered as
partialmatch candidate. 
> In my opinion, if it's not a last bucket then for next bucket it will become left boundary and this will be treated
aspartial match so no problem, but in-case of last bucket it can give wrong selectivity. 
>
> Can't we consider it as partial bucket match if it is last bucket ?

Actually, in that case, the ratio for one value in the column is used.
I clarified the comment about it.  I do not think it is common enough
case to make the function more complicated.

> Apart from that there is one spell check you can correct
> -- in inet_his_inclusion_selec comments
> histogram boundies  -> histogram boundaries :)

I fixed it.  New version attached.  The debug log statements are also
removed.

Attachment

Re: Selectivity estimation for inet operators

From
Dilip kumar
Date:
On 06 July 2014 20:33, Emre Hasegeli Wrote,
> 
> > Apart from that there is one spell check you can correct
> > -- in inet_his_inclusion_selec comments histogram boundies  ->
> > histogram boundaries :)
> 
> I fixed it.  New version attached.  The debug log statements are also
> removed.

I have done with the review, patch seems fine to me

I have one last comment, after clarifying this I can move it to "ready for committer".
1. In networkjoinsel, For avoiding the case of huge statistics, only some of the values from mcv and histograms are
used(calculated using SQRT).
 
-- But in my opinion, if histograms and mcv both are exist then its fine, but if only mcv's are there in that case, we
canmatch complete MCV, it will give better accuracy.  In other function like eqjoinsel also its matching complete MCV.
 



Thanks & Regards,
Dilip Kumar

Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> I have one last comment, after clarifying this I can move it to "ready for committer".
> 1. In networkjoinsel, For avoiding the case of huge statistics, only some of the values from mcv and histograms are
used(calculated using SQRT). 
> -- But in my opinion, if histograms and mcv both are exist then its fine, but if only mcv's are there in that case,
wecan match complete MCV, it will give better accuracy. 
>    In other function like eqjoinsel also its matching complete MCV.

I was not sure of reducing statistics, at all.  I could not find any
other selectivity estimation function which does this.  After testing
it some more, I reached the conclusion that it would be better to
only reduce the values of the outer loop on histogram match.  Now it
matches complete MCV lists to each other.  I also switched back to
log2() from sqrt() to make the outer list smaller.

I rethink your previous advice to threat histogram bucket partially
matched when the constant matches the last boundary, and changed it
that way.  It is better than using the selectivity for only one value.
Removing this part also make the function more simple.  The new
version of the patch attached.

While looking at it I find some other small problems and fixed them.
I also realized that I forgot to support other join types than inner
join.  Currently, the default estimation is used for anti joins.
I think the patch will need more than trivial amount of change to
support anti joins.  I can work on it later.  While doing it, outer
join selectivity estimation can also be improved.  I think the patch
is better than nothing in its current state.

Attachment

Re: Selectivity estimation for inet operators

From
Dilip kumar
Date:
On 12 July 2014 23:25, Emre Hasegeli Wrote,

> > I have one last comment, after clarifying this I can move it to
> "ready for committer".
> > 1. In networkjoinsel, For avoiding the case of huge statistics, only
> some of the values from mcv and histograms are used (calculated using
> SQRT).
> > -- But in my opinion, if histograms and mcv both are exist then its
> fine, but if only mcv's are there in that case, we can match complete
> MCV, it will give better accuracy.
> >    In other function like eqjoinsel also its matching complete MCV.
> 
> I was not sure of reducing statistics, at all.  I could not find any
> other selectivity estimation function which does this.  After testing
> it some more, I reached the conclusion that it would be better to only
> reduce the values of the outer loop on histogram match.  Now it matches
> complete MCV lists to each other.  I also switched back to
> log2() from sqrt() to make the outer list smaller.

OK

> 
> I rethink your previous advice to threat histogram bucket partially
> matched when the constant matches the last boundary, and changed it
> that way.  It is better than using the selectivity for only one value.
> Removing this part also make the function more simple.  The new version
> of the patch attached.
This seems good to me.

> 
> While looking at it I find some other small problems and fixed them.
> I also realized that I forgot to support other join types than inner
> join.  Currently, the default estimation is used for anti joins.
> I think the patch will need more than trivial amount of change to
> support anti joins.  I can work on it later.  While doing it, outer
> join selectivity estimation can also be improved.  I think the patch is
> better than nothing in its current state.

I agree with you that we can support other join type and anti join later,
If others don’t have any objection in doing other parts later I will mark as "Ready For Committer".

Regards,
Dilip




Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> I agree with you that we can support other join type and anti join later,
> If others don’t have any objection in doing other parts later I will mark as "Ready For Committer".

I updated the patch to cover semi and anti joins with eqjoinsel_semi().
I think it is better than returning a constant.  The new version
attached with the new version of the test script.  Can you please
look at it again and mark it as "ready for committer" if it seems okay
to you?

Attachment

Re: Selectivity estimation for inet operators

From
Heikki Linnakangas
Date:
On 08/26/2014 12:44 PM, Emre Hasegeli wrote:
>> I agree with you that we can support other join type and anti join later,
>> If others don’t have any objection in doing other parts later I will mark as "Ready For Committer".
>
> I updated the patch to cover semi and anti joins with eqjoinsel_semi().
> I think it is better than returning a constant.  The new version
> attached with the new version of the test script.  Can you please
> look at it again and mark it as "ready for committer" if it seems okay
> to you?

I took a quick look at this. Some questions:

* Isn't "X >> Y" equivalent to "network_scan_first(X) < Y AND 
network_scan_last(X) > Y"? Or at least close enough for selectivity 
estimation purposes? Pardon my ignorance - I'm not too familiar with the 
inet datatype - but how about just calling scalarineqsel for both bounds?

* inet_mcv_join_selec() is O(n^2) where n is the number of entries in 
the MCV lists. With the max statistics target of 10000, a worst case 
query on my laptop took about 15 seconds to plan. Maybe that's 
acceptable, but you went through some trouble to make planning of MCV vs 
histogram faster, by the log2 method to compare only some values, so I 
wonder why you didn't do the same for the MCV vs MCV case?

* A few typos: lenght -> length.

- Heikki




Re: Selectivity estimation for inet operators

From
Tom Lane
Date:
Emre Hasegeli <emre@hasegeli.com> writes:
> I updated the patch to cover semi and anti joins with eqjoinsel_semi().
> I think it is better than returning a constant.

What you did there is utterly unacceptable from a modularity standpoint;
and considering that the values will be nowhere near right, the argument
that "it's better than returning a constant" seems pretty weak.  I think
you should just take that out again.
        regards, tom lane



Re: Selectivity estimation for inet operators

From
Tom Lane
Date:
Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> * inet_mcv_join_selec() is O(n^2) where n is the number of entries in 
> the MCV lists. With the max statistics target of 10000, a worst case 
> query on my laptop took about 15 seconds to plan. Maybe that's 
> acceptable, but you went through some trouble to make planning of MCV vs 
> histogram faster, by the log2 method to compare only some values, so I 
> wonder why you didn't do the same for the MCV vs MCV case?

Actually, what I think needs to be asked is the opposite question: why is
the other code ignoring some of the statistical data?  If the user asked
us to collect a lot of stats detail it seems reasonable that he's
expecting us to use it to get more accurate estimates.  It's for sure
not obvious why these estimators should take shortcuts that are not being
taken in the much-longer-established code for scalar comparison estimates.

I'm not exactly convinced that the math adds up in this logic, either.
The way in which it combines results from looking at the MCV lists and
at the histograms seems pretty arbitrary.
        regards, tom lane



Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> * Isn't "X >> Y" equivalent to "network_scan_first(X) < Y AND
> network_scan_last(X) > Y"? Or at least close enough for selectivity
> estimation purposes? Pardon my ignorance - I'm not too familiar with the
> inet datatype - but how about just calling scalarineqsel for both bounds?

Actually, "X >> Y" is equivalent to

    network_scan_first(X) <= network_host(Y) AND
    network_scan_last(X) >= network_host(Y) AND
    network_masklen(X) < network_masklen(X)

but we do not have statistics for neither network_scan_last(X)
nor network_masklen(X).  I tried to find a solution based on
the implementation of the operators.

> * inet_mcv_join_selec() is O(n^2) where n is the number of entries in the
> MCV lists. With the max statistics target of 10000, a worst case query on
> my laptop took about 15 seconds to plan. Maybe that's acceptable, but you
> went through some trouble to make planning of MCV vs histogram faster, by
> the log2 method to compare only some values, so I wonder why you didn't do
> the same for the MCV vs MCV case?

It was like that in the previous versions.  It was causing worse
estimation, but I was trying to reduce both sides of the lists.  It
works slightly better when only the left hand side of the list is
reduced.  Attached version works like that.

> * A few typos: lenght -> length.

Fixed.

Thank you for looking at it.

Attachment

Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> > * inet_mcv_join_selec() is O(n^2) where n is the number of entries in
> > the MCV lists. With the max statistics target of 10000, a worst case
> > query on my laptop took about 15 seconds to plan. Maybe that's
> > acceptable, but you went through some trouble to make planning of MCV vs
> > histogram faster, by the log2 method to compare only some values, so I
> > wonder why you didn't do the same for the MCV vs MCV case?
>
> Actually, what I think needs to be asked is the opposite question: why is
> the other code ignoring some of the statistical data?  If the user asked
> us to collect a lot of stats detail it seems reasonable that he's
> expecting us to use it to get more accurate estimates.  It's for sure
> not obvious why these estimators should take shortcuts that are not being
> taken in the much-longer-established code for scalar comparison estimates.

It will still use more statistical data, when statistics_target is
higher.  It was not sure that the user wants to spent O(n^2) amount
of time based on statistics_target.  Attached version is without
this optimization.  Estimates are better without it, but planning
takes more time.

> I'm not exactly convinced that the math adds up in this logic, either.
> The way in which it combines results from looking at the MCV lists and
> at the histograms seems pretty arbitrary.

I taught the product of the join will be

    (left_mcv + left_histogram) * (right_mcv + right_histogram) * selectivity

and tried to calculate it as in the following:

    (left_mcv * right_mcv * selectivity) +
    (right_mcv * left_histogram * selectivity) +
    (left_mcv * right_histogram * selectivity) +
    (left_histogram * right_histogram * selectivity)

where left_histogram is

    1.0 - left_nullfrac - left_mcv

I fixed calculation for the MCV vs histogram part.  The estimates of
inner join are very close to the actual rows with statistics_target = 1000.
I think the calculation should be right.

Attachment

Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> What you did there is utterly unacceptable from a modularity standpoint;
> and considering that the values will be nowhere near right, the argument
> that "it's better than returning a constant" seems pretty weak.  I think
> you should just take that out again.

I will try to come up with a better, data type specific implementation
in a week.



Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> > > I updated the patch to cover semi and anti joins with eqjoinsel_semi().
> > > I think it is better than returning a constant.
> >
> > What you did there is utterly unacceptable from a modularity standpoint;
> > and considering that the values will be nowhere near right, the argument
> > that "it's better than returning a constant" seems pretty weak.  I think
> > you should just take that out again.
>
> I will try to come up with a better, data type specific implementation
> in a week.

New version with semi join estimation function attached.

Attachment

Re: Selectivity estimation for inet operators

From
"Brightwell, Adam"
Date:
New version with semi join estimation function attached.

I have performed the following initial review:

- Patch format. -- submitted as unified, but not sure it makes it any easier to read than context format.
- Apply to current master (77e65bf).  -- success (though, I do get "Stripping trailing CR's from patch;" notification)
- check-world -- success
- Whitespace - were the whitespace changes in pg_operator.h necessary?

As for implementation, I'll leave that to those with a better understanding of the purpose/expectations of the modified functions.

-Adam

--

Re: Selectivity estimation for inet operators

From
Heikki Linnakangas
Date:
On 09/07/2014 07:09 PM, Emre Hasegeli wrote:
>>>> I updated the patch to cover semi and anti joins with eqjoinsel_semi().
>>>> I think it is better than returning a constant.
>>>
>>> What you did there is utterly unacceptable from a modularity standpoint;
>>> and considering that the values will be nowhere near right, the argument
>>> that "it's better than returning a constant" seems pretty weak.  I think
>>> you should just take that out again.
>>
>> I will try to come up with a better, data type specific implementation
>> in a week.
>
> New version with semi join estimation function attached.

Thanks. Overall, my impression of this patch is that it works very well. 
But damned if I understood *how* it works :-). There's a lot of 
statistics involved, and it's not easy to see why something is 
multiplied by something else. I'm adding comments as I read through it.

I've gotten to the inet_semi_join_selec function:

> /*
>  * Inet semi join selectivity estimation.
>  */
> static Selectivity
> inet_semi_join_selec(bool mcv2_exists, Datum *mcv2_values, int mcv2_nvalues,
>                      bool his2_exists, Datum *his2_values, int his2_nvalues,
>                      double his2_weight, Datum *constvalue,
>                      FmgrInfo *proc, short opr_order)
> {
>     if (mcv2_exists)
>     {
>         int            i;
>
>         for (i = 0; i < mcv2_nvalues; i++)
>         {
>             if (DatumGetBool(FunctionCall2Coll(proc, DEFAULT_COLLATION_OID,
>                                                *constvalue, mcv2_values[i])))
>                 return 1.0;
>         }
>     }
>
>     /* Do not bother if histogram weight is smaller than 0.1. */
>     if (his2_exists && his2_weight > 0.1)
>     {
>         Selectivity    his_selec;
>
>         his_selec = inet_his_inclusion_selec(his2_values, his2_nvalues,
>                                              constvalue, opr_order);
>
>         if (his_selec > 0)
>             return Min(1.0, his2_weight * his_selec);
>     }
>
>     return 0.0;
> }

This desperately needs comment at the top of the function explaining 
what it does. Let me try to explain what I think it does:

This function calculates the probability that there is at least one row 
in table B, which satisfies the "constant op column" qual. The constant 
is passed as argument, and for table B, the MCV list and histogram is 
provided. his2_weight is the total number of rows in B that are covered 
by the histogram. For example, if the table has 1000 rows, and 10% of 
the rows in the table are in the MCV, and another 10% are NULLs, 
his_weight would be 800.

First, we check if the constant matches any of the most common values. 
If it does, return 1.0, because then there is surely a match.

Next, we use the histogram to estimate the number of rows in the table 
that matches the qual. If it amounts to more than 1 row, we return 1.0. 
If it's between 0.0 and 1.0 rows, we return that number as the probability.


Now, I think that last step is wrong. Firstly, the "Do not bother if 
histogram weight is smaller than 0.1" rule seems bogus. The his2_weight 
is the total number of rows represented by the histogram, so surely it 
can't be less than 1. It can't really be less than the statistics 
target. Unless maybe if the histogram was collected when the table was 
large, but it has since shrunk to contain only a few rows, but that 
seems like a very bizarre corner case. At least it needs more comments 
explaining what the test is all about, but I think we should just always 
use the histogram (if it's available).

Secondly, if we estimate that there is on average 1.0 matching row in 
the table, it does not follow that the probability that at least one row 
matches is 1.0. Assuming a gaussian distribution with mean 1.0, the 
probability that at least one row matches is 0.5. Assuming a gaussian 
distribution here isn't quite right - I guess a Poisson distribution 
would be more accurate - but it sure doesn't seem right as it is.

The error isn't very big, and perhaps you don't run into that very 
often, so I'm not sure what the best way to fix that would be. My 
statistics skills are a bit rusty, but I think the appropriate way would 
be to apply the Poisson distribution, with the estimated number of 
matched rows as the mean. The probability of at least one match would be 
the cumulative distribution function at k=1. It sounds like overkill, if 
this is case occurs only rarely. But then again, perhaps it's not all 
that rare.

That said, I can't immediately find a test case where that error would 
matter. I tried this:

create table inettbl1 (a inet);
insert into inettbl1 select '10.0.0.' || (g % 255) from 
generate_series(1, 10) g;
analyze inettbl1;
explain analyze select count(*) from inettbl1 where a >>= ANY (SELECT a 
from inettbl1);

The estimate for that is pretty accurate, 833 rows estimated vs 1000 
actual, with the current patch. I'm afraid if we fixed 
inet_semi_join_selec the way I suggest, the estimate would be smaller, 
i.e. more wrong. Is there something else in the estimates that 
accidentally compensates for this currently?

Thoughts?

- Heikki




Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> Thanks. Overall, my impression of this patch is that it works very
> well. But damned if I understood *how* it works :-). There's a lot
> of statistics involved, and it's not easy to see why something is
> multiplied by something else. I'm adding comments as I read through
> it.

Thank you for looking at it.  I tried to add more comments to
the multiplications.  New version attached.  It also fixes a bug
caused by wrong operator order used on histogram to histogram
selectivity estimation for inner join.

> I've gotten to the inet_semi_join_selec function:
>
> > [function]
>
> This desperately needs comment at the top of the function explaining
> what it does. Let me try to explain what I think it does:
>
> [explanation]

I used your explanation on the new version.

> Now, I think that last step is wrong. Firstly, the "Do not bother if
> histogram weight is smaller than 0.1" rule seems bogus. The
> his2_weight is the total number of rows represented by the
> histogram, so surely it can't be less than 1. It can't really be
> less than the statistics target. Unless maybe if the histogram was
> collected when the table was large, but it has since shrunk to
> contain only a few rows, but that seems like a very bizarre corner
> case. At least it needs more comments explaining what the test is
> all about, but I think we should just always use the histogram (if
> it's available).

It was an unnecessary check.  I put an assert instead of it.

> Secondly, if we estimate that there is on average 1.0 matching row
> in the table, it does not follow that the probability that at least
> one row matches is 1.0. Assuming a gaussian distribution with mean
> 1.0, the probability that at least one row matches is 0.5. Assuming
> a gaussian distribution here isn't quite right - I guess a Poisson
> distribution would be more accurate - but it sure doesn't seem right
> as it is.
>
> The error isn't very big, and perhaps you don't run into that very
> often, so I'm not sure what the best way to fix that would be. My
> statistics skills are a bit rusty, but I think the appropriate way
> would be to apply the Poisson distribution, with the estimated
> number of matched rows as the mean. The probability of at least one
> match would be the cumulative distribution function at k=1. It
> sounds like overkill, if this is case occurs only rarely. But then
> again, perhaps it's not all that rare.

A function of his_weight and his_selec could be a better option
than just multiplying them.  I am not sure about the function or
it worths the trouble.  Join selectivity estimation function for
equality doesn't even bother to look at the histograms.  Others
only return constant values.

> That said, I can't immediately find a test case where that error
> would matter. I tried this:
>
> create table inettbl1 (a inet);
> insert into inettbl1 select '10.0.0.' || (g % 255) from
> generate_series(1, 10) g;
> analyze inettbl1;
> explain analyze select count(*) from inettbl1 where a >>= ANY
> (SELECT a from inettbl1);
>
> The estimate for that is pretty accurate, 833 rows estimated vs 1000
> actual, with the current patch. I'm afraid if we fixed
> inet_semi_join_selec the way I suggest, the estimate would be
> smaller, i.e. more wrong. Is there something else in the estimates
> that accidentally compensates for this currently?

The partial bucket match on inet_his_inclusion_selec() causes low
estimates.  Which also effects non join estimation but not as much as
it effects join estimations.  If that works more correctly, semi
join estimation can be higher than it should be.

network_selfuncs.c:602:
>            /* Partial bucket match. */
>
>            left_divider = inet_his_match_divider(left, query, opr_order);
>            right_divider = inet_his_match_divider(right, query, opr_order);
>
>            if (left_divider >= 0 || right_divider >= 0)
>                match += 1.0 / pow(2, Max(left_divider, right_divider));

I think this calculation can benefit from a statistical function
more than the semi join.  Using the different bit count as power
of two is the best I could find.  It works quite well on most of
the cases.

Attachment

Re: Selectivity estimation for inet operators

From
Tom Lane
Date:
Emre Hasegeli <emre@hasegeli.com> writes:
>> Thanks. Overall, my impression of this patch is that it works very
>> well. But damned if I understood *how* it works :-). There's a lot
>> of statistics involved, and it's not easy to see why something is
>> multiplied by something else. I'm adding comments as I read through
>> it.

> Thank you for looking at it.  I tried to add more comments to
> the multiplications.  New version attached.  It also fixes a bug
> caused by wrong operator order used on histogram to histogram
> selectivity estimation for inner join.

I spent a fair chunk of the weekend hacking on this patch to make
it more understandable and fix up a lot of what seemed to me pretty
clear arithmetic errors in the "upper layers" of the patch.  However,
I couldn't quite convince myself to commit it, because the business
around estimation for partial histogram-bucket matches still doesn't
make any sense to me.  Specifically this:

            /* Partial bucket match. */
            left_divider = inet_hist_match_divider(left, query, opr_codenum);
            right_divider = inet_hist_match_divider(right, query, opr_codenum);

            if (left_divider >= 0 || right_divider >= 0)
                match += 1.0 / pow(2.0, Max(left_divider, right_divider));

Now unless I'm missing something pretty basic about the divider
function, it returns larger numbers for inputs that are "further away"
from each other (ie, have more not-in-common significant address bits).
So the above calculation seems exactly backwards to me: if one endpoint
of a bucket is "close" to the query, or even an exact match, and the
other endpoint is further away, we completely ignore the close/exact
match and assign a bucket match fraction based only on the further-away
endpoint.  Isn't that exactly backwards?

I experimented with logic like this:

            if (left_divider >= 0 && right_divider >= 0)
                match += 1.0 / pow(2.0, Min(left_divider, right_divider));
            else if (left_divider >= 0 || right_divider >= 0)
                match += 1.0 / pow(2.0, Max(left_divider, right_divider));

ie, consider the closer endpoint if both are valid.  But that didn't seem
to work a whole lot better.  I think really we need to consider both
endpoints not just one to the exclusion of the other.

I'm also not exactly convinced by the divider function itself,
specifically about the decision to fail and return -1 if the masklen
comparison comes out wrong.  This effectively causes the masklen to be
the most significant part of the value (after the IP family), which seems
totally wrong.  ISTM we ought to consider the number of leading bits in
common as the primary indicator of "how far apart" a query and a
histogram endpoint are.

Even if the above aspects of the code are really completely right, the
comments fail to explain why.  I spent a lot of time on the comments,
but so far as these points are concerned they still only explain what
is being done and not why it's a useful calculation to make.

Anyway, attached is my updated version of the patch.  (I did commit the
added #define in pg_operator.h, so that the patch can be independent of
that file in future.)  I've marked this "waiting on author" in the CF app.

            regards, tom lane

diff --git a/src/backend/utils/adt/network_selfuncs.c b/src/backend/utils/adt/network_selfuncs.c
index d0d806f..f854847 100644
*** a/src/backend/utils/adt/network_selfuncs.c
--- b/src/backend/utils/adt/network_selfuncs.c
***************
*** 3,9 ****
   * network_selfuncs.c
   *      Functions for selectivity estimation of inet/cidr operators
   *
!  * Currently these are just stubs, but we hope to do better soon.
   *
   * Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group
   * Portions Copyright (c) 1994, Regents of the University of California
--- 3,11 ----
   * network_selfuncs.c
   *      Functions for selectivity estimation of inet/cidr operators
   *
!  * This module provides estimators for the subnet inclusion and overlap
!  * operators.  Estimates are based on null fraction, most common values,
!  * and histogram of inet/cidr columns.
   *
   * Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group
   * Portions Copyright (c) 1994, Regents of the University of California
***************
*** 16,32 ****
   */
  #include "postgres.h"

  #include "utils/inet.h"


  Datum
  networksel(PG_FUNCTION_ARGS)
  {
!     PG_RETURN_FLOAT8(0.001);
  }

  Datum
  networkjoinsel(PG_FUNCTION_ARGS)
  {
!     PG_RETURN_FLOAT8(0.001);
  }
--- 18,949 ----
   */
  #include "postgres.h"

+ #include <math.h>
+
+ #include "access/htup_details.h"
+ #include "catalog/pg_operator.h"
+ #include "catalog/pg_statistic.h"
  #include "utils/inet.h"
+ #include "utils/lsyscache.h"
+ #include "utils/selfuncs.h"
+
+
+ /* Default selectivity for the inet overlap operator */
+ #define DEFAULT_OVERLAP_SEL 0.01

+ /* Default selectivity for the various inclusion operators */
+ #define DEFAULT_INCLUSION_SEL 0.005
+
+ /* Default selectivity for specified operator */
+ #define DEFAULT_SEL(operator) \
+     ((operator) == OID_INET_OVERLAP_OP ? \
+      DEFAULT_OVERLAP_SEL : DEFAULT_INCLUSION_SEL)
+
+ static Selectivity networkjoinsel_inner(Oid operator,
+                      VariableStatData *vardata1, VariableStatData *vardata2);
+ static Selectivity networkjoinsel_semi(Oid operator,
+                     VariableStatData *vardata1, VariableStatData *vardata2);
+ static Selectivity mcv_population(float4 *mcv_numbers, int mcv_nvalues);
+ static Selectivity inet_hist_value_sel(Datum *values, int nvalues,
+                     Datum constvalue, int opr_codenum);
+ static Selectivity inet_mcv_join_sel(Datum *mcv1_values,
+                   float4 *mcv1_numbers, int mcv1_nvalues, Datum *mcv2_values,
+                   float4 *mcv2_numbers, int mcv2_nvalues, Oid operator);
+ static Selectivity inet_mcv_hist_sel(Datum *mcv_values, float4 *mcv_numbers,
+                   int mcv_nvalues, Datum *hist_values, int hist_nvalues,
+                   int opr_codenum);
+ static Selectivity inet_hist_inclusion_join_sel(Datum *hist1_values,
+                              int hist1_nvalues,
+                              Datum *hist2_values, int hist2_nvalues,
+                              int opr_codenum);
+ static Selectivity inet_semi_join_sel(Datum lhs_value,
+                    bool mcv_exists, Datum *mcv_values, int mcv_nvalues,
+                    bool hist_exists, Datum *hist_values, int hist_nvalues,
+                    double hist_weight,
+                    FmgrInfo *proc, int opr_codenum);
+ static int    inet_opr_codenum(Oid operator);
+ static int    inet_inclusion_cmp(inet *left, inet *right, int opr_codenum);
+ static int inet_masklen_inclusion_cmp(inet *left, inet *right,
+                            int opr_codenum);
+ static int inet_hist_match_divider(inet *boundary, inet *query,
+                         int opr_codenum);

+ /*
+  * Selectivity estimation for the subnet inclusion/overlap operators
+  */
  Datum
  networksel(PG_FUNCTION_ARGS)
  {
!     PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
!     Oid            operator = PG_GETARG_OID(1);
!     List       *args = (List *) PG_GETARG_POINTER(2);
!     int            varRelid = PG_GETARG_INT32(3);
!     VariableStatData vardata;
!     Node       *other;
!     bool        varonleft;
!     Selectivity selec,
!                 mcv_selec,
!                 non_mcv_selec;
!     Datum        constvalue,
!                *hist_values;
!     int            hist_nvalues;
!     Form_pg_statistic stats;
!     double        sumcommon,
!                 nullfrac;
!     FmgrInfo    proc;
!
!     /*
!      * If expression is not (variable op something) or (something op
!      * variable), then punt and return a default estimate.
!      */
!     if (!get_restriction_variable(root, args, varRelid,
!                                   &vardata, &other, &varonleft))
!         PG_RETURN_FLOAT8(DEFAULT_SEL(operator));
!
!     /*
!      * Can't do anything useful if the something is not a constant, either.
!      */
!     if (!IsA(other, Const))
!     {
!         ReleaseVariableStats(vardata);
!         PG_RETURN_FLOAT8(DEFAULT_SEL(operator));
!     }
!
!     /* All of the operators handled here are strict. */
!     if (((Const *) other)->constisnull)
!     {
!         ReleaseVariableStats(vardata);
!         PG_RETURN_FLOAT8(0.0);
!     }
!     constvalue = ((Const *) other)->constvalue;
!
!     /* Otherwise, we need stats in order to produce a non-default estimate. */
!     if (!HeapTupleIsValid(vardata.statsTuple))
!     {
!         ReleaseVariableStats(vardata);
!         PG_RETURN_FLOAT8(DEFAULT_SEL(operator));
!     }
!
!     stats = (Form_pg_statistic) GETSTRUCT(vardata.statsTuple);
!     nullfrac = stats->stanullfrac;
!
!     /*
!      * If we have most-common-values info, add up the fractions of the MCV
!      * entries that satisfy MCV OP CONST.  These fractions contribute directly
!      * to the result selectivity.  Also add up the total fraction represented
!      * by MCV entries.
!      */
!     fmgr_info(get_opcode(operator), &proc);
!     mcv_selec = mcv_selectivity(&vardata, &proc, constvalue, varonleft,
!                                 &sumcommon);
!
!     /*
!      * If we have a histogram, use it to estimate the proportion of the
!      * non-MCV population that satisfies the clause.  If we don't, apply the
!      * default selectivity to that population.
!      */
!     if (get_attstatsslot(vardata.statsTuple,
!                          vardata.atttype, vardata.atttypmod,
!                          STATISTIC_KIND_HISTOGRAM, InvalidOid,
!                          NULL,
!                          &hist_values, &hist_nvalues,
!                          NULL, NULL))
!     {
!         int            opr_codenum = inet_opr_codenum(operator);
!
!         /* Commute if needed, so we can consider histogram to be on the left */
!         if (!varonleft)
!             opr_codenum = -opr_codenum;
!         non_mcv_selec = inet_hist_value_sel(hist_values, hist_nvalues,
!                                             constvalue, opr_codenum);
!
!         free_attstatsslot(vardata.atttype, hist_values, hist_nvalues, NULL, 0);
!     }
!     else
!         non_mcv_selec = DEFAULT_SEL(operator);
!
!     /* Combine selectivities for MCV and non-MCV populations */
!     selec = mcv_selec + (1.0 - nullfrac - sumcommon) * non_mcv_selec;
!
!     /* Result should be in range, but make sure... */
!     CLAMP_PROBABILITY(selec);
!
!     ReleaseVariableStats(vardata);
!
!     PG_RETURN_FLOAT8(selec);
  }

+ /*
+  * Join selectivity estimation for the subnet inclusion/overlap operators
+  *
+  * This function has the same structure as eqjoinsel() in selfuncs.c.
+  */
  Datum
  networkjoinsel(PG_FUNCTION_ARGS)
  {
!     PlannerInfo *root = (PlannerInfo *) PG_GETARG_POINTER(0);
!     Oid            operator = PG_GETARG_OID(1);
!     List       *args = (List *) PG_GETARG_POINTER(2);
! #ifdef NOT_USED
!     JoinType    jointype = (JoinType) PG_GETARG_INT16(3);
! #endif
!     SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) PG_GETARG_POINTER(4);
!     double        selec;
!     VariableStatData vardata1;
!     VariableStatData vardata2;
!     bool        join_is_reversed;
!
!     get_join_variables(root, args, sjinfo,
!                        &vardata1, &vardata2, &join_is_reversed);
!
!     switch (sjinfo->jointype)
!     {
!         case JOIN_INNER:
!         case JOIN_LEFT:
!         case JOIN_FULL:
!
!             /*
!              * Selectivity for left/full join is not exactly the same as inner
!              * join, but we neglect the difference, as eqjoinsel does.
!              */
!             selec = networkjoinsel_inner(operator, &vardata1, &vardata2);
!             break;
!         case JOIN_SEMI:
!         case JOIN_ANTI:
!             /* Here, it's important that we pass the outer var on the left. */
!             if (!join_is_reversed)
!                 selec = networkjoinsel_semi(operator, &vardata1, &vardata2);
!             else
!                 selec = networkjoinsel_semi(get_commutator(operator),
!                                             &vardata2, &vardata1);
!             break;
!         default:
!             /* other values not expected here */
!             elog(ERROR, "unrecognized join type: %d",
!                  (int) sjinfo->jointype);
!             selec = 0;            /* keep compiler quiet */
!             break;
!     }
!
!     ReleaseVariableStats(vardata1);
!     ReleaseVariableStats(vardata2);
!
!     CLAMP_PROBABILITY(selec);
!
!     PG_RETURN_FLOAT8((float8) selec);
! }
!
! /*
!  * Inner join selectivity estimation for subnet inclusion/overlap operators
!  *
!  * Calculates MCV vs MCV, MCV vs histogram and histogram vs histogram
!  * selectivity for join using the subnet inclusion operators.  Unlike the
!  * join selectivity function for the equality operator, eqjoinsel_inner(),
!  * one to one matching of the values is not enough.  Network inclusion
!  * operators are likely to match many to many, so we must check all pairs.
!  * (Note: it might be possible to exploit understanding of the histogram's
!  * btree ordering to reduce the work needed, but we don't currently try.)
!  * Also, MCV vs histogram selectivity is not neglected as in eqjoinsel_inner().
!  */
! static Selectivity
! networkjoinsel_inner(Oid operator,
!                      VariableStatData *vardata1, VariableStatData *vardata2)
! {
!     Form_pg_statistic stats;
!     double        nullfrac1 = 0.0,
!                 nullfrac2 = 0.0;
!     Selectivity selec = 0.0,
!                 sumcommon1 = 0.0,
!                 sumcommon2 = 0.0;
!     bool        mcv1_exists = false,
!                 mcv2_exists = false,
!                 hist1_exists = false,
!                 hist2_exists = false;
!     int            opr_codenum;
!     int            mcv1_nvalues,
!                 mcv2_nvalues,
!                 mcv1_nnumbers,
!                 mcv2_nnumbers,
!                 hist1_nvalues,
!                 hist2_nvalues;
!     Datum       *mcv1_values,
!                *mcv2_values,
!                *hist1_values,
!                *hist2_values;
!     float4       *mcv1_numbers,
!                *mcv2_numbers;
!
!     if (HeapTupleIsValid(vardata1->statsTuple))
!     {
!         stats = (Form_pg_statistic) GETSTRUCT(vardata1->statsTuple);
!         nullfrac1 = stats->stanullfrac;
!
!         mcv1_exists = get_attstatsslot(vardata1->statsTuple,
!                                        vardata1->atttype, vardata1->atttypmod,
!                                        STATISTIC_KIND_MCV, InvalidOid,
!                                        NULL,
!                                        &mcv1_values, &mcv1_nvalues,
!                                        &mcv1_numbers, &mcv1_nnumbers);
!         hist1_exists = get_attstatsslot(vardata1->statsTuple,
!                                       vardata1->atttype, vardata1->atttypmod,
!                                         STATISTIC_KIND_HISTOGRAM, InvalidOid,
!                                         NULL,
!                                         &hist1_values, &hist1_nvalues,
!                                         NULL, NULL);
!         if (mcv1_exists)
!             sumcommon1 = mcv_population(mcv1_numbers, mcv1_nnumbers);
!     }
!
!     if (HeapTupleIsValid(vardata2->statsTuple))
!     {
!         stats = (Form_pg_statistic) GETSTRUCT(vardata2->statsTuple);
!         nullfrac2 = stats->stanullfrac;
!
!         mcv2_exists = get_attstatsslot(vardata2->statsTuple,
!                                        vardata2->atttype, vardata2->atttypmod,
!                                        STATISTIC_KIND_MCV, InvalidOid,
!                                        NULL,
!                                        &mcv2_values, &mcv2_nvalues,
!                                        &mcv2_numbers, &mcv2_nnumbers);
!         hist2_exists = get_attstatsslot(vardata2->statsTuple,
!                                       vardata2->atttype, vardata2->atttypmod,
!                                         STATISTIC_KIND_HISTOGRAM, InvalidOid,
!                                         NULL,
!                                         &hist2_values, &hist2_nvalues,
!                                         NULL, NULL);
!         if (mcv2_exists)
!             sumcommon2 = mcv_population(mcv2_numbers, mcv2_nnumbers);
!     }
!
!     opr_codenum = inet_opr_codenum(operator);
!
!     /*
!      * Calculate selectivity for MCV vs MCV matches.
!      */
!     if (mcv1_exists && mcv2_exists)
!         selec += inet_mcv_join_sel(mcv1_values, mcv1_numbers, mcv1_nvalues,
!                                    mcv2_values, mcv2_numbers, mcv2_nvalues,
!                                    operator);
!
!     /*
!      * Add in selectivities for MCV vs histogram matches, scaling according to
!      * the fractions of the populations represented by the histograms. Note
!      * that the second case needs to commute the operator.
!      */
!     if (mcv1_exists && hist2_exists)
!         selec += (1.0 - nullfrac2 - sumcommon2) *
!             inet_mcv_hist_sel(mcv1_values, mcv1_numbers, mcv1_nvalues,
!                               hist2_values, hist2_nvalues,
!                               opr_codenum);
!     if (mcv2_exists && hist1_exists)
!         selec += (1.0 - nullfrac1 - sumcommon1) *
!             inet_mcv_hist_sel(mcv2_values, mcv2_numbers, mcv2_nvalues,
!                               hist1_values, hist1_nvalues,
!                               -opr_codenum);
!
!     /*
!      * Add in selectivity for histogram vs histogram matches, again scaling
!      * appropriately.
!      */
!     if (hist1_exists && hist2_exists)
!         selec += (1.0 - nullfrac1 - sumcommon1) *
!             (1.0 - nullfrac2 - sumcommon2) *
!             inet_hist_inclusion_join_sel(hist1_values, hist1_nvalues,
!                                          hist2_values, hist2_nvalues,
!                                          opr_codenum);
!
!     /*
!      * If useful statistics are not available then use the default estimate.
!      * We can apply null fractions if known, though.
!      */
!     if ((!mcv1_exists && !hist1_exists) || (!mcv2_exists && !hist2_exists))
!         selec = (1.0 - nullfrac1) * (1.0 - nullfrac2) * DEFAULT_SEL(operator);
!
!     /* Release stats. */
!     if (mcv1_exists)
!         free_attstatsslot(vardata1->atttype, mcv1_values, mcv1_nvalues,
!                           mcv1_numbers, mcv1_nnumbers);
!     if (mcv2_exists)
!         free_attstatsslot(vardata2->atttype, mcv2_values, mcv2_nvalues,
!                           mcv2_numbers, mcv2_nnumbers);
!     if (hist1_exists)
!         free_attstatsslot(vardata1->atttype, hist1_values, hist1_nvalues,
!                           NULL, 0);
!     if (hist2_exists)
!         free_attstatsslot(vardata2->atttype, hist2_values, hist2_nvalues,
!                           NULL, 0);
!
!     return selec;
! }
!
! /*
!  * Semi join selectivity estimation for subnet inclusion/overlap operators
!  *
!  * Calculates MCV vs MCV, MCV vs histogram, histogram vs MCV, and histogram vs
!  * histogram selectivity for semi/anti join cases.
!  */
! static Selectivity
! networkjoinsel_semi(Oid operator,
!                     VariableStatData *vardata1, VariableStatData *vardata2)
! {
!     Form_pg_statistic stats;
!     Selectivity selec = 0.0,
!                 sumcommon1 = 0.0,
!                 sumcommon2 = 0.0;
!     double        nullfrac1 = 0.0,
!                 nullfrac2 = 0.0,
!                 hist2_weight = 0.0;
!     bool        mcv1_exists = false,
!                 mcv2_exists = false,
!                 hist1_exists = false,
!                 hist2_exists = false;
!     int            opr_codenum;
!     FmgrInfo    proc;
!     int            i,
!                 mcv1_nvalues,
!                 mcv2_nvalues,
!                 mcv1_nnumbers,
!                 mcv2_nnumbers,
!                 hist1_nvalues,
!                 hist2_nvalues;
!     Datum       *mcv1_values,
!                *mcv2_values,
!                *hist1_values,
!                *hist2_values;
!     float4       *mcv1_numbers,
!                *mcv2_numbers;
!
!     if (HeapTupleIsValid(vardata1->statsTuple))
!     {
!         stats = (Form_pg_statistic) GETSTRUCT(vardata1->statsTuple);
!         nullfrac1 = stats->stanullfrac;
!
!         mcv1_exists = get_attstatsslot(vardata1->statsTuple,
!                                        vardata1->atttype, vardata1->atttypmod,
!                                        STATISTIC_KIND_MCV, InvalidOid,
!                                        NULL,
!                                        &mcv1_values, &mcv1_nvalues,
!                                        &mcv1_numbers, &mcv1_nnumbers);
!         hist1_exists = get_attstatsslot(vardata1->statsTuple,
!                                       vardata1->atttype, vardata1->atttypmod,
!                                         STATISTIC_KIND_HISTOGRAM, InvalidOid,
!                                         NULL,
!                                         &hist1_values, &hist1_nvalues,
!                                         NULL, NULL);
!         if (mcv1_exists)
!             sumcommon1 = mcv_population(mcv1_numbers, mcv1_nnumbers);
!     }
!
!     if (HeapTupleIsValid(vardata2->statsTuple))
!     {
!         stats = (Form_pg_statistic) GETSTRUCT(vardata2->statsTuple);
!         nullfrac2 = stats->stanullfrac;
!
!         mcv2_exists = get_attstatsslot(vardata2->statsTuple,
!                                        vardata2->atttype, vardata2->atttypmod,
!                                        STATISTIC_KIND_MCV, InvalidOid,
!                                        NULL,
!                                        &mcv2_values, &mcv2_nvalues,
!                                        &mcv2_numbers, &mcv2_nnumbers);
!         hist2_exists = get_attstatsslot(vardata2->statsTuple,
!                                       vardata2->atttype, vardata2->atttypmod,
!                                         STATISTIC_KIND_HISTOGRAM, InvalidOid,
!                                         NULL,
!                                         &hist2_values, &hist2_nvalues,
!                                         NULL, NULL);
!         if (mcv2_exists)
!             sumcommon2 = mcv_population(mcv2_numbers, mcv2_nnumbers);
!     }
!
!     opr_codenum = inet_opr_codenum(operator);
!     fmgr_info(get_opcode(operator), &proc);
!
!     /* Estimate number of input rows represented by RHS histogram. */
!     if (hist2_exists && vardata2->rel)
!         hist2_weight = (1.0 - nullfrac2 - sumcommon2) * vardata2->rel->rows;
!
!     /*
!      * Consider each element of the LHS MCV list, matching it to whatever RHS
!      * stats we have.  Scale according to the known frequency of the MCV.
!      */
!     if (mcv1_exists && (mcv2_exists || hist2_exists))
!     {
!         for (i = 0; i < mcv1_nvalues; i++)
!         {
!             selec += mcv1_numbers[i] *
!                 inet_semi_join_sel(mcv1_values[i],
!                                    mcv2_exists, mcv2_values, mcv2_nvalues,
!                                    hist2_exists, hist2_values, hist2_nvalues,
!                                    hist2_weight,
!                                    &proc, opr_codenum);
!         }
!     }
!
!     /*
!      * Consider each element of the LHS histogram, except for the first and
!      * last elements, which we exclude on the grounds that they're outliers
!      * and thus not very representative.  Scale on the assumption that each
!      * such histogram element represents an equal share of the LHS histogram
!      * population (which is a bit bogus, because the members of its bucket may
!      * not all act the same with respect to the join clause, but it's hard to
!      * do better).
!      */
!     if (hist1_exists && hist1_nvalues > 2 && (mcv2_exists || hist2_exists))
!     {
!         double        hist_selec_sum = 0.0;
!
!         for (i = 1; i < hist1_nvalues - 1; i++)
!         {
!             hist_selec_sum +=
!                 inet_semi_join_sel(hist1_values[i],
!                                    mcv2_exists, mcv2_values, mcv2_nvalues,
!                                    hist2_exists, hist2_values, hist2_nvalues,
!                                    hist2_weight,
!                                    &proc, opr_codenum);
!         }
!
!         selec += (1.0 - nullfrac1 - sumcommon1) *
!             hist_selec_sum / (hist1_nvalues - 2);
!     }
!
!     /*
!      * If useful statistics are not available then use the default estimate.
!      * We can apply null fractions if known, though.
!      */
!     if ((!mcv1_exists && !hist1_exists) || (!mcv2_exists && !hist2_exists))
!         selec = (1.0 - nullfrac1) * (1.0 - nullfrac2) * DEFAULT_SEL(operator);
!
!     /* Release stats. */
!     if (mcv1_exists)
!         free_attstatsslot(vardata1->atttype, mcv1_values, mcv1_nvalues,
!                           mcv1_numbers, mcv1_nnumbers);
!     if (mcv2_exists)
!         free_attstatsslot(vardata2->atttype, mcv2_values, mcv2_nvalues,
!                           mcv2_numbers, mcv2_nnumbers);
!     if (hist1_exists)
!         free_attstatsslot(vardata1->atttype, hist1_values, hist1_nvalues,
!                           NULL, 0);
!     if (hist2_exists)
!         free_attstatsslot(vardata2->atttype, hist2_values, hist2_nvalues,
!                           NULL, 0);
!
!     return selec;
! }
!
! /*
!  * Compute the fraction of a relation's population that is represented
!  * by the MCV list.
!  */
! static Selectivity
! mcv_population(float4 *mcv_numbers, int mcv_nvalues)
! {
!     Selectivity sumcommon = 0.0;
!     int            i;
!
!     for (i = 0; i < mcv_nvalues; i++)
!     {
!         sumcommon += mcv_numbers[i];
!     }
!
!     return sumcommon;
! }
!
! /*
!  * Inet histogram vs single value selectivity estimation
!  *
!  * Estimate the fraction of the histogram population that satisfies
!  * "value OPR CONST".  (The result needs to be scaled to reflect the
!  * proportion of the total population represented by the histogram.)
!  *
!  * The histogram is originally for the inet btree comparison operators.
!  * Only the common bits of the network part and the length of the network part
!  * (masklen) are interesting for the subnet inclusion operators.  Fortunately,
!  * btree comparison treats the network part as the major sort key.  Even so,
!  * the length of the network part would not really be significant in the
!  * histogram.  This would lead to big mistakes for data sets with uneven
!  * masklen distribution.  To reduce this problem, comparisons with the left
!  * and the right sides of the buckets are used together.
!  *
!  * Histogram bucket matches are calculated in two forms.  If the constant
!  * matches both bucket endpoints the bucket is considered as fully matched.
!  * The second form is to match the bucket partially; we recognize this when
!  * the constant matches just one endpoint, or the two endpoints fall on
!  * opposite sides of the constant.  (Note that when the constant matches an
!  * interior histogram element, it gets credit for partial matches to the
!  * buckets on both sides, while a match to a histogram endpoint gets credit
!  * for only one partial match.  This is desirable.)
!  *
!  * For a partial match, we try to calculate dividers for both of the
!  * boundaries.  If the address family of a boundary value does not match the
!  * constant or comparison of the length of the network parts is not correct
!  * for the operator, the divider for that boundary will not be taken into
!  * account.  If both of the dividers are valid, the greater one will be used
!  * to minimize the mistake in buckets that have disparate masklens.
!  *
!  * The divider in the partial bucket match is imagined as the distance
!  * between the decisive bits and the common bits of the addresses.  It will
!  * be used as a power of two as it is the natural scale for the IP network
!  * inclusion.  This partial bucket match divider calculation is an empirical
!  * formula and subject to change with more experiment.
!  *
!  * For partial match in buckets that have different address families on the
!  * left and right sides, only the boundary with the same address family is
!  * taken into consideration.  This can cause more mistakes for these buckets
!  * if the masklens of their boundaries are also disparate.  But this can only
!  * happen in one bucket, since only two address families exist.  It seems a
!  * better option than not considering these buckets at all.
!  */
! static Selectivity
! inet_hist_value_sel(Datum *values, int nvalues, Datum constvalue,
!                     int opr_codenum)
! {
!     Selectivity match = 0.0;
!     inet       *query,
!                *left,
!                *right;
!     int            i;
!     int            left_order,
!                 right_order,
!                 left_divider,
!                 right_divider;
!
!     /* guard against zero-divide below */
!     if (nvalues <= 1)
!         return 0.0;
!
!     query = DatumGetInetPP(constvalue);
!
!     /* "left" is the left boundary value of the current bucket ... */
!     left = DatumGetInetPP(values[0]);
!     left_order = inet_inclusion_cmp(left, query, opr_codenum);
!
!     for (i = 1; i < nvalues; i++)
!     {
!         /* ... and "right" is the right boundary value */
!         right = DatumGetInetPP(values[i]);
!         right_order = inet_inclusion_cmp(right, query, opr_codenum);
!
!         if (left_order == 0 && right_order == 0)
!         {
!             /* The whole bucket matches, since both endpoints do. */
!             match += 1.0;
!         }
!         else if ((left_order <= 0 && right_order >= 0) ||
!                  (left_order >= 0 && right_order <= 0))
!         {
!             /* Partial bucket match. */
!             left_divider = inet_hist_match_divider(left, query, opr_codenum);
!             right_divider = inet_hist_match_divider(right, query, opr_codenum);
!
!             if (left_divider >= 0 || right_divider >= 0)
!                 match += 1.0 / pow(2.0, Max(left_divider, right_divider));
!         }
!
!         /* Shift the variables. */
!         left = right;
!         left_order = right_order;
!     }
!
!     /* There are nvalues - 1 buckets. */
!     return match / (nvalues - 1);
! }
!
! /*
!  * Inet MCV vs MCV join selectivity estimation
!  *
!  * We simply add up the fractions of the populations that satisfy the clause.
!  * The result is exact and does not need to be scaled further.
!  */
! static Selectivity
! inet_mcv_join_sel(Datum *mcv1_values, float4 *mcv1_numbers, int mcv1_nvalues,
!                   Datum *mcv2_values, float4 *mcv2_numbers, int mcv2_nvalues,
!                   Oid operator)
! {
!     Selectivity selec = 0.0;
!     FmgrInfo    proc;
!     int            i,
!                 j;
!
!     fmgr_info(get_opcode(operator), &proc);
!
!     for (i = 0; i < mcv1_nvalues; i++)
!     {
!         for (j = 0; j < mcv2_nvalues; j++)
!             if (DatumGetBool(FunctionCall2(&proc,
!                                            mcv1_values[i],
!                                            mcv2_values[j])))
!                 selec += mcv1_numbers[i] * mcv2_numbers[j];
!     }
!     return selec;
! }
!
! /*
!  * Inet MCV vs histogram join selectivity estimation
!  *
!  * For each MCV on the lefthand side, estimate the fraction of the righthand's
!  * histogram population that satisfies the join clause, and add those up,
!  * scaling by the MCV's frequency.  The result still needs to be scaled
!  * according to the fraction of the righthand's population represented by
!  * the histogram.
!  */
! static Selectivity
! inet_mcv_hist_sel(Datum *mcv_values, float4 *mcv_numbers, int mcv_nvalues,
!                   Datum *hist_values, int hist_nvalues,
!                   int opr_codenum)
! {
!     Selectivity selec = 0.0;
!     int            i;
!
!     /*
!      * We'll call inet_hist_value_selec with the histogram on the left, so we
!      * must commute the operator.
!      */
!     opr_codenum = -opr_codenum;
!
!     for (i = 0; i < mcv_nvalues; i++)
!     {
!         selec += mcv_numbers[i] *
!             inet_hist_value_sel(hist_values, hist_nvalues, mcv_values[i],
!                                 opr_codenum);
!     }
!     return selec;
! }
!
! /*
!  * Inet histogram vs histogram join selectivity estimation
!  *
!  * Here, we take all values listed in the second histogram (except for the
!  * first and last elements, which are excluded on the grounds of possibly
!  * not being very representative) and treat them as a uniform sample of
!  * the non-MCV population for that relation.  For each one, we apply
!  * inet_hist_value_selec to see what fraction of the first histogram
!  * it matches.
!  *
!  * We could alternatively do this the other way around using the operator's
!  * commutator.  XXX would it be worthwhile to do it both ways and take the
!  * average?  That would at least avoid non-commutative estimation results.
!  */
! static Selectivity
! inet_hist_inclusion_join_sel(Datum *hist1_values, int hist1_nvalues,
!                              Datum *hist2_values, int hist2_nvalues,
!                              int opr_codenum)
! {
!     float        match = 0.0;
!     int            i;
!
!     if (hist2_nvalues <= 2)
!         return 0.0;                /* no interior histogram elements */
!
!     for (i = 1; i < hist2_nvalues - 1; i++)
!         match += inet_hist_value_sel(hist1_values, hist1_nvalues,
!                                      hist2_values[i], opr_codenum);
!
!     return match / (hist2_nvalues - 2);
! }
!
! /*
!  * Inet semi join selectivity estimation for one value
!  *
!  * The function calculates the probability that there is at least one row
!  * in the RHS table that satisfies the "lhs_value op column" condition.
!  * It is used in semi join estimation to check a sample from the left hand
!  * side table.
!  *
!  * The MCV and histogram from the right hand side table should be provided as
!  * arguments with the lhs_value from the left hand side table for the join.
!  * hist_weight is the total number of rows represented by the histogram.
!  * For example, if the table has 1000 rows, and 10% of the rows are in the MCV
!  * list, and another 10% are NULLs, hist_weight would be 800.
!  *
!  * First, the lhs_value will be matched to the most common values.  If it
!  * matches any of them, 1.0 will be returned, because then there is surely
!  * a match.
!  *
!  * Otherwise, the histogram will be used to estimate the number of rows in
!  * the second table that match the condition.  If the estimate is greater
!  * than 1.0, 1.0 will be returned, because it means there is a greater chance
!  * that the lhs_value will match more than one row in the table.  If it is
!  * between 0.0 and 1.0, it will be returned as the probability.
!  */
! static Selectivity
! inet_semi_join_sel(Datum lhs_value,
!                    bool mcv_exists, Datum *mcv_values, int mcv_nvalues,
!                    bool hist_exists, Datum *hist_values, int hist_nvalues,
!                    double hist_weight,
!                    FmgrInfo *proc, int opr_codenum)
! {
!     if (mcv_exists)
!     {
!         int            i;
!
!         for (i = 0; i < mcv_nvalues; i++)
!         {
!             if (DatumGetBool(FunctionCall2(proc,
!                                            lhs_value,
!                                            mcv_values[i])))
!                 return 1.0;
!         }
!     }
!
!     if (hist_exists && hist_weight > 0)
!     {
!         Selectivity hist_selec;
!
!         /* Commute operator, since we're passing lhs_value on the right */
!         hist_selec = inet_hist_value_sel(hist_values, hist_nvalues,
!                                          lhs_value, -opr_codenum);
!
!         if (hist_selec > 0)
!             return Min(1.0, hist_weight * hist_selec);
!     }
!
!     return 0.0;
! }
!
! /*
!  * Assign useful code numbers for the subnet inclusion/overlap operators
!  *
!  * Only inet_masklen_inclusion_cmp() and inet_hist_match_divider() depend
!  * on the exact codes assigned here; but many other places in this file
!  * know that they can negate a code to obtain the code for the commutator
!  * operator.
!  */
! static int
! inet_opr_codenum(Oid operator)
! {
!     switch (operator)
!     {
!         case OID_INET_SUP_OP:
!             return -2;
!         case OID_INET_SUPEQ_OP:
!             return -1;
!         case OID_INET_OVERLAP_OP:
!             return 0;
!         case OID_INET_SUBEQ_OP:
!             return 1;
!         case OID_INET_SUB_OP:
!             return 2;
!         default:
!             elog(ERROR, "unrecognized operator %u for inet selectivity",
!                  operator);
!     }
!     return 0;                    /* unreached, but keep compiler quiet */
! }
!
! /*
!  * Comparison function for the subnet inclusion/overlap operators
!  *
!  * If the comparison is okay for the specified inclusion operator, the return
!  * value will be 0.  Otherwise the return value will be less than or greater
!  * than 0 as appropriate for the operator.
!  *
!  * Comparison is compatible with the basic comparison function for the inet
!  * type.  See network_cmp_internal() in network.c for the original.  Basic
!  * comparison operators are implemented with the network_cmp_internal
!  * function.  It is possible to implement the subnet inclusion operators with
!  * this function.
!  *
!  * Comparison is first on the common bits of the network part, then on the
!  * length of the network part (masklen) as in the network_cmp_internal()
!  * function.  Only the first part is in this function.  The second part is
!  * separated to another function for reusability.  The difference between the
!  * second part and the original network_cmp_internal() is that the inclusion
!  * operator is considered while comparing the lengths of the network parts.
!  * See the inet_masklen_inclusion_cmp() function below.
!  */
! static int
! inet_inclusion_cmp(inet *left, inet *right, int opr_codenum)
! {
!     if (ip_family(left) == ip_family(right))
!     {
!         int            order;
!
!         order = bitncmp(ip_addr(left), ip_addr(right),
!                         Min(ip_bits(left), ip_bits(right)));
!
!         if (order != 0)
!             return order;
!
!         return inet_masklen_inclusion_cmp(left, right, opr_codenum);
!     }
!
!     return ip_family(left) - ip_family(right);
! }
!
! /*
!  * Masklen comparison function for the subnet inclusion/overlap operators
!  *
!  * Compares the lengths of the network parts of the inputs.  If the comparison
!  * is okay for the specified inclusion operator, the return value will be 0.
!  * Otherwise the return value will be less than or greater than 0 as
!  * appropriate for the operator.
!  */
! static int
! inet_masklen_inclusion_cmp(inet *left, inet *right, int opr_codenum)
! {
!     int            order;
!
!     order = (int) ip_bits(left) - (int) ip_bits(right);
!
!     /*
!      * Return 0 if the operator would accept this combination of masklens.
!      * Note that opr_codenum zero (overlaps) will accept all cases.
!      */
!     if ((order > 0 && opr_codenum >= 0) ||
!         (order == 0 && opr_codenum >= -1 && opr_codenum <= 1) ||
!         (order < 0 && opr_codenum <= 0))
!         return 0;
!
!     /*
!      * Otherwise, return a negative value for sup/supeq (notionally, the RHS
!      * needs to have a larger masklen than it has, which would make it sort
!      * later), or a positive value for sub/subeq (vice versa).
!      */
!     return opr_codenum;
! }
!
! /*
!  * Inet histogram partial match divider calculation
!  *
!  * First the families and the lengths of the network parts are compared using
!  * the subnet inclusion operator.  If those are acceptable for the operator,
!  * the divider will be calculated using the masklens and the common bits of
!  * the addresses.  -1 will be returned if it cannot be calculated.
!  *
!  * See commentary for inet_hist_value_sel() for some rationale for this.
!  */
! static int
! inet_hist_match_divider(inet *boundary, inet *query, int opr_codenum)
! {
!     if (ip_family(boundary) == ip_family(query) &&
!         inet_masklen_inclusion_cmp(boundary, query, opr_codenum) == 0)
!     {
!         int            min_bits,
!                     decisive_bits;
!
!         min_bits = Min(ip_bits(boundary), ip_bits(query));
!
!         /*
!          * Set decisive_bits to the masklen of the one that should contain the
!          * other according to the operator.
!          */
!         if (opr_codenum < 0)
!             decisive_bits = ip_bits(boundary);
!         else if (opr_codenum > 0)
!             decisive_bits = ip_bits(query);
!         else
!             decisive_bits = min_bits;
!
!         /*
!          * Now return the number of non-common decisive bits.  (This will be
!          * zero if the boundary and query in fact match, else positive.)
!          */
!         if (min_bits > 0)
!             return decisive_bits - bitncommon(ip_addr(boundary),
!                                               ip_addr(query),
!                                               min_bits);
!         return decisive_bits;
!     }
!
!     return -1;
  }

Re: Selectivity estimation for inet operators

From
Tom Lane
Date:
I wrote:
> I spent a fair chunk of the weekend hacking on this patch to make
> it more understandable and fix up a lot of what seemed to me pretty
> clear arithmetic errors in the "upper layers" of the patch.  However,
> I couldn't quite convince myself to commit it, because the business
> around estimation for partial histogram-bucket matches still doesn't
> make any sense to me.

Actually, there's a second large problem with this patch: blindly
iterating through all combinations of MCV and histogram entries makes the
runtime O(N^2) in the statistics target.  I made up some test data (by
scanning my mail logs) and observed the following planning times, as
reported by EXPLAIN ANALYZE:

explain analyze select * from relays r1, relays r2 where r1.ip = r2.ip;
explain analyze select * from relays r1, relays r2 where r1.ip && r2.ip;

stats target        eqjoinsel    networkjoinsel

100            0.27 ms        1.85 ms
1000            2.56 ms        167.2 ms
10000            56.6 ms        13987.1 ms

I don't think it's necessary for network selectivity to be quite as
fast as eqjoinsel, but I doubt we can tolerate 14 seconds planning
time for a query that might need just milliseconds to execute :-(

It seemed to me that it might be possible to reduce the runtime by
exploiting knowledge about the ordering of the histograms, but
I don't have time to pursue that right now.
        regards, tom lane



Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> I spent a fair chunk of the weekend hacking on this patch to make
> it more understandable and fix up a lot of what seemed to me pretty
> clear arithmetic errors in the "upper layers" of the patch.  However,
> I couldn't quite convince myself to commit it, because the business
> around estimation for partial histogram-bucket matches still doesn't
> make any sense to me.  Specifically this:
>
>             /* Partial bucket match. */
>             left_divider = inet_hist_match_divider(left, query, opr_codenum);
>             right_divider = inet_hist_match_divider(right, query, opr_codenum);
>
>             if (left_divider >= 0 || right_divider >= 0)
>                 match += 1.0 / pow(2.0, Max(left_divider, right_divider));
>
> Now unless I'm missing something pretty basic about the divider
> function, it returns larger numbers for inputs that are "further away"
> from each other (ie, have more not-in-common significant address bits).
> So the above calculation seems exactly backwards to me: if one endpoint
> of a bucket is "close" to the query, or even an exact match, and the
> other endpoint is further away, we completely ignore the close/exact
> match and assign a bucket match fraction based only on the further-away
> endpoint.  Isn't that exactly backwards?

You are right that partial bucket match calculation isn't fair on
some circumstances.

> I experimented with logic like this:
>
>             if (left_divider >= 0 && right_divider >= 0)
>                 match += 1.0 / pow(2.0, Min(left_divider, right_divider));
>             else if (left_divider >= 0 || right_divider >= 0)
>                 match += 1.0 / pow(2.0, Max(left_divider, right_divider));
>
> ie, consider the closer endpoint if both are valid.  But that didn't seem
> to work a whole lot better.  I think really we need to consider both
> endpoints not just one to the exclusion of the other.

I have tried many combinations like this.  Including arithmetic,
geometric, logarithmic mean functions.  I could not get good results
with any of them, so I left it in a basic form.

Max() works pretty well most of the time, because if the query matches
the bucket generally it is close to both of the endpoints.  By using
Max(), we are actually crediting the ones which are close to the both
of the endpoints.  

> I'm also not exactly convinced by the divider function itself,
> specifically about the decision to fail and return -1 if the masklen
> comparison comes out wrong.  This effectively causes the masklen to be
> the most significant part of the value (after the IP family), which seems
> totally wrong.  ISTM we ought to consider the number of leading bits in
> common as the primary indicator of "how far apart" a query and a
> histogram endpoint are.

The partial match calculation with Max() is especially unfair on
the buckets where more significant bits change.  For example 63/8 and
64/8.  Returning -1 instead of a high divider, forces it to use
the divider for the other endpoint.  We consider the number of leading
bits in common as the primary indicator, just for the other endpoint.

I have also experimented with the count of the common bits of
the endpoints of the bucket for better partial match calculation.
I could not find out a meaningful equation with it.

> Even if the above aspects of the code are really completely right, the
> comments fail to explain why.  I spent a lot of time on the comments,
> but so far as these points are concerned they still only explain what
> is being done and not why it's a useful calculation to make.

I couldn't write better comments because I don't have strong arguments
about it.  We can say that we don't try to make use of the both of
the endpoints, because we don't know how to combine them.  We only use
the one with matching family and masklen, and when both of them match
we use the distant one to be on the safer side.

Thank you for looking at it.  Comments look much better now.



Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
> Actually, there's a second large problem with this patch: blindly
> iterating through all combinations of MCV and histogram entries makes the
> runtime O(N^2) in the statistics target.  I made up some test data (by
> scanning my mail logs) and observed the following planning times, as
> reported by EXPLAIN ANALYZE:
>
> explain analyze select * from relays r1, relays r2 where r1.ip = r2.ip;
> explain analyze select * from relays r1, relays r2 where r1.ip && r2.ip;
>
> stats target            eqjoinsel       networkjoinsel
>
> 100                     0.27 ms         1.85 ms
> 1000                    2.56 ms         167.2 ms
> 10000                   56.6 ms         13987.1 ms
>
> I don't think it's necessary for network selectivity to be quite as
> fast as eqjoinsel, but I doubt we can tolerate 14 seconds planning
> time for a query that might need just milliseconds to execute :-(
>
> It seemed to me that it might be possible to reduce the runtime by
> exploiting knowledge about the ordering of the histograms, but
> I don't have time to pursue that right now.

I make it break the loop when we passed the last possible match. Patch
attached.  It reduces the runtime almost 50% with large histograms.

We can also make it use only some elements of the MCV and histogram
for join estimation.  I have experimented with reducing the right and
the left hand side of the lists on the previous versions.  I remember
it was better to reduce only the left hand side.  I think it would be
enough to use log(n) elements of the right hand side MCV and histogram.
I can make the change, if you think selectivity estimation function
is the right place for this optimization.

Attachment

Re: Selectivity estimation for inet operators

From
Michael Paquier
Date:
On Wed, Dec 3, 2014 at 6:14 AM, Emre Hasegeli <emre@hasegeli.com> wrote:
>> Actually, there's a second large problem with this patch: blindly
>> iterating through all combinations of MCV and histogram entries makes the
>> runtime O(N^2) in the statistics target.  I made up some test data (by
>> scanning my mail logs) and observed the following planning times, as
>> reported by EXPLAIN ANALYZE:
>>
>> explain analyze select * from relays r1, relays r2 where r1.ip = r2.ip;
>> explain analyze select * from relays r1, relays r2 where r1.ip && r2.ip;
>>
>> stats target            eqjoinsel       networkjoinsel
>>
>> 100                     0.27 ms         1.85 ms
>> 1000                    2.56 ms         167.2 ms
>> 10000                   56.6 ms         13987.1 ms
>>
>> I don't think it's necessary for network selectivity to be quite as
>> fast as eqjoinsel, but I doubt we can tolerate 14 seconds planning
>> time for a query that might need just milliseconds to execute :-(
>>
>> It seemed to me that it might be possible to reduce the runtime by
>> exploiting knowledge about the ordering of the histograms, but
>> I don't have time to pursue that right now.
>
> I make it break the loop when we passed the last possible match. Patch
> attached.  It reduces the runtime almost 50% with large histograms.
>
> We can also make it use only some elements of the MCV and histogram
> for join estimation.  I have experimented with reducing the right and
> the left hand side of the lists on the previous versions.  I remember
> it was better to reduce only the left hand side.  I think it would be
> enough to use log(n) elements of the right hand side MCV and histogram.
> I can make the change, if you think selectivity estimation function
> is the right place for this optimization.
Marking as "Returned with feedback" as more work needs to be done.
-- 
Michael



Re: Selectivity estimation for inet operators

From
Emre Hasegeli
Date:
New version of the patch attached with the optimization to break the
loop before looking at all of the histogram values.  I can reduce
join selectivity estimation runtime by reducing the values of the
left hand side or both of the sides, if there is interest.

> > Even if the above aspects of the code are really completely right, the
> > comments fail to explain why.  I spent a lot of time on the comments,
> > but so far as these points are concerned they still only explain what
> > is being done and not why it's a useful calculation to make.
>
> I couldn't write better comments because I don't have strong arguments
> about it.  We can say that we don't try to make use of the both of
> the endpoints, because we don't know how to combine them.  We only use
> the one with matching family and masklen, and when both of them match
> we use the distant one to be on the safer side.

I added two more sentences to explain the calculation.

Attachment

Re: Selectivity estimation for inet operators

From
Tom Lane
Date:
Emre Hasegeli <emre@hasegeli.com> writes:
> [ inet-selfuncs-v14.patch ]

After further reflection I concluded that the best way to deal with the
O(N^2) runtime problem for the join selectivity function was to set a
limit on the number of statistics values we'd consider, as was discussed
awhile back IIRC.  We can easily consider just the first N values of the
MCV arrays, since those are sorted by decreasing frequency anyway.  For
the histogram arrays, taking every K'th value seems like the thing to do.

I made the limit be 1024 elements as that seemed to give an acceptable
maximum runtime (a couple hundred msec on my machine).  We could probably
reduce that if anyone feels the max runtime needs to be less.

I had to drop the idea of breaking out of the histogram loop early as that
didn't play nicely with the decimation logic, unfortunately.

Anyway, pushed.  Thanks for your perseverance on this!
        regards, tom lane