Subject: incorrect information in documentation

From: PG Doc comments form <[email-masked]>
Date: Mon, 09 Aug 2021 12:53:00 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/row-estimation-examples.html
Description:

Hello, on page
https://www.postgresql.org/docs/current/row-estimation-examples.html - there
is a example:
selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1,
1/num_distinct2)
            = (1 - 0) * (1 - 0) / max(10000, 10000)
            = 0.0001
in the first string  " * min" and in the second " / max" 
as I understand it isn't correct.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 9 Aug 2021 09:06:16 -0700

> as I understand it isn't correct.

Division is just multiplication by the reciprocal so while the presentation
here is inconsistent it is correct.  Likewise, the larger a number the
smaller its reciprocal, so the change from min to max also works.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 9 Aug 2021 09:20:53 -0700

On Mon, Aug 9, 2021 at 9:06 AM David G. Johnston <[email-masked]>
wrote:

> On Mon, Aug 9, 2021 at 8:02 AM PG Doc comments form <
> [email-masked]> wrote:
>
>> Hello, on page
> Division is just multiplication by the reciprocal so while the
> presentation here is inconsistent it is correct.  Likewise, the larger a
> number the smaller its reciprocal, so the change from min to max also
> works.
>
>
FWIW this used to be presented with the calculation and formula in sync,
but the original had a simple typo in the calculation.  When the typo got
fixed back in December of 2007 [1] the author of the patch simplified the
calculation at the same time.  I suggest we update the formula line to
match the calculation presented.

David J.

1.
https://github.com/postgres/postgres/commit/f5678e8e07563e34ae4dc832546977d13edcd665

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 9 Aug 2021 14:05:50 -0400

On Mon, Aug  9, 2021 at 09:20:53AM -0700, David G. Johnston wrote:
>     On Mon, Aug 9, 2021 at 8:02 AM PG Doc comments form <[email-masked]
>     > wrote:
> 
>         Hello, on page
> FWIW this used to be presented with the calculation and formula in sync, but
> the original had a simple typo in the calculation.  When the typo got fixed
> back in December of 2007 [1] the author of the patch simplified the calculation
> at the same time.  I suggest we update the formula line to match the
> calculation presented.

Nice, can you provide a patch please?

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 9 Aug 2021 20:40:20 -0700

> Nice, can you provide a patch please?
Change the line:

to be:

selectivity = (1 - null_frac1) * (1 - null_frac2) / max(num_distinct1,
num_distinct2)

The wording already talks about "divide by max".

Though:

"so we use an algorithm that relies only on the number of distinct values
for both relations together with their null fractions:"

maybe adds a parenthetical note:

"so we use an algorithm that relies only on the number of distinct values
(the row count estimate for the whole table, not the -1 in the column
statistics) for both relations together with their null fractions:"

Just note I haven't tried to absorb that whole page, let alone the
implementation, and am not all that familiar with this part of PostgreSQL.
Its seems right, though, in isolation.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 12 Apr 2022 14:36:47 -0700

>> Nice, can you provide a patch please?
Concretely, as attached and inline.

David J.

commit 73fa486a855d75d74a1a695bb350bfbfe27c7751
Author: David G. Johnston <[email-masked]>
Date:   Tue Apr 12 21:23:53 2022 +0000

    doc: make unique non-null join selectivity example match the prose

    The description of the computation for the unique, non-null,
    join selectivity describes a division by the maximum of two values,
    while the example shows a multiplication by their reciprocal.  While
    equivalent the max phrasing is easier to understand; which seems
    more important here than precisely adhering to the formula use
    in the code (for which either variant is still an approximation).

    While both num_distinct and num_rows are equal for a unique column
    both the concept and formula use row count (10,000) and the
    field num_distinct has already been set to mean the specific value
    present in the pg_stats table (i.e, -1), so use num_rows here.

diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml
index 78053d7c49..f72bc4b274 100644
--- a/doc/src/sgml/planstats.sgml
+++ b/doc/src/sgml/planstats.sgml
@@ -391,18 +391,20 @@ tablename  | null_frac | n_distinct | most_common_vals
 </programlisting>

    In this case there is no <acronym>MCV</acronym> information for
-   <structfield>unique2</structfield> because all the values appear to be
-   unique, so we use an algorithm that relies only on the number of
-   distinct values for both relations together with their null fractions:
+   <structname>unique2</structname> and all the values appear to be
+   unique (n_distinct = -1), so we use an algorithm that relies on the row
+   count estimates for both relations (num_rows, not shown, but "tenk")
+   together with the column null fractions (zero for both):

 <programlisting>
-selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1,
1/num_distinct2)
+selectivity = (1 - null_frac1) * (1 - null_frac2) / max(num_rows1,
num_rows2)
             = (1 - 0) * (1 - 0) / max(10000, 10000)
             = 0.0001
 </programlisting>

    This is, subtract the null fraction from one for each of the relations,
-   and divide by the maximum of the numbers of distinct values.
+   and divide by the row count of the larger relation (this value does get
+   scaled in the non-unique case).
    The number of rows
    that the join is likely to emit is calculated as the cardinality of the
    Cartesian product of the two inputs, multiplied by the