pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedShould 'sum(mvf)' read 'sum(mcv)'...?
9+ messages / 4 participants
[nested] [flat]
* Should 'sum(mvf)' read 'sum(mcv)'...?
@ 2022-08-21 23:02 PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: PG Doc comments form @ 2022-08-21 23:02 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: eric.mutta@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/14/row-estimation-examples.html
Description:
About halfway down this page
https://www.postgresql.org/docs/current/row-estimation-examples.html we see
the following formula for calculating selectivity:
> selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
And just below the formula we see the explanatory sentence saying:
> That is, add up all the frequencies for the MCVs and subtract them from
one, ...
It appears the above sentence is referring to the "(1 - sum(mvf))" portion
of the formula, however I am not sure what "mvf" is referring to
there...shouldn't it be "(1 - sum(mcv))" in order to match what the
explanatory sentence is saying?
Many thanks,
Eric Mutta.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
@ 2022-08-22 07:48 ` Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 09:13 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
2022-08-22 12:58 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Julien Rouhaud @ 2022-08-22 07:48 UTC (permalink / raw)
To: eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
Hi,
On Sun, Aug 21, 2022 at 11:02:04PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/row-estimation-examples.html
> Description:
>
> About halfway down this page
> https://www.postgresql.org/docs/current/row-estimation-examples.html we see
> the following formula for calculating selectivity:
>
> > selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
>
> And just below the formula we see the explanatory sentence saying:
>
> > That is, add up all the frequencies for the MCVs and subtract them from
> one, ...
>
> It appears the above sentence is referring to the "(1 - sum(mvf))" portion
> of the formula, however I am not sure what "mvf" is referring to
> there...shouldn't it be "(1 - sum(mcv))" in order to match what the
> explanatory sentence is saying?
It should be mcf, ie. Most Common Frequencies. It looks like a very old typo
that survived until now.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
@ 2022-08-22 09:13 ` Daniel Gustafsson <daniel@yesql.se>
2022-08-22 10:08 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Daniel Gustafsson @ 2022-08-22 09:13 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
> On 22 Aug 2022, at 09:48, Julien Rouhaud <rjuju123@gmail.com> wrote:
> On Sun, Aug 21, 2022 at 11:02:04PM +0000, PG Doc comments form wrote:
>> It appears the above sentence is referring to the "(1 - sum(mvf))" portion
>> of the formula, however I am not sure what "mvf" is referring to
>> there...shouldn't it be "(1 - sum(mcv))" in order to match what the
>> explanatory sentence is saying?
>
> It should be mcf, ie. Most Common Frequencies. It looks like a very old typo
> that survived until now.
That seems plausible, but it does seem introduced on purpose in f5678e8e075 so
CC:ing Tom for a trip down memory lane.
Looking at this I noticed that we mark up MCV and MCF as acronyms but they
aren't defined in acronyms.sgml. ISTM it's a good idea to keep a 1:1 mapping
between markup and content, so we should probably do that as per the attached?
--
Daniel Gustafsson https://vmware.com/
Attachments:
[application/octet-stream] mcx_acronyms.diff (648B, ../../CE022387-1A12-43E8-A77A-0DD727577794@yesql.se/2-mcx_acronyms.diff)
download | inline diff:
diff --git a/doc/src/sgml/acronyms.sgml b/doc/src/sgml/acronyms.sgml
index 2df6559acc..72a45e5106 100644
--- a/doc/src/sgml/acronyms.sgml
+++ b/doc/src/sgml/acronyms.sgml
@@ -408,6 +408,24 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><acronym>MCF</acronym></term>
+ <listitem>
+ <para>
+ Most Common Frequencies
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><acronym>MCV</acronym></term>
+ <listitem>
+ <para>
+ Most Common Values
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><acronym>MITM</acronym></term>
<listitem>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 09:13 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
@ 2022-08-22 10:08 ` Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 10:26 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 9+ messages in thread
From: Julien Rouhaud @ 2022-08-22 10:08 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
Hi,
On Mon, Aug 22, 2022 at 11:13:38AM +0200, Daniel Gustafsson wrote:
> > On 22 Aug 2022, at 09:48, Julien Rouhaud <rjuju123@gmail.com> wrote:
> > On Sun, Aug 21, 2022 at 11:02:04PM +0000, PG Doc comments form wrote:
>
> >> It appears the above sentence is referring to the "(1 - sum(mvf))" portion
> >> of the formula, however I am not sure what "mvf" is referring to
> >> there...shouldn't it be "(1 - sum(mcv))" in order to match what the
> >> explanatory sentence is saying?
> >
> > It should be mcf, ie. Most Common Frequencies. It looks like a very old typo
> > that survived until now.
>
> That seems plausible, but it does seem introduced on purpose in f5678e8e075 so
> CC:ing Tom for a trip down memory lane.
That was actually introduced 2 years before in 234d50812c8 by Bruce.
> Looking at this I noticed that we mark up MCV and MCF as acronyms but they
> aren't defined in acronyms.sgml. ISTM it's a good idea to keep a 1:1 mapping
> between markup and content, so we should probably do that as per the attached?
Agreed, although MCF is only used in planstats.sgml and the acronym defined
locally.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 09:13 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
2022-08-22 10:08 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
@ 2022-08-22 10:26 ` Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 9+ messages in thread
From: Daniel Gustafsson @ 2022-08-22 10:26 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: eric.mutta@gmail.com, Pg Docs <pgsql-docs@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
> On 22 Aug 2022, at 12:08, Julien Rouhaud <rjuju123@gmail.com> wrote:
> That was actually introduced 2 years before in 234d50812c8 by Bruce.
Yes, I was unclear, I meant that the second use was by Tom (whom I also missed
to CC as I said I would so doing that now).
--
Daniel Gustafsson https://vmware.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
@ 2022-08-22 12:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 11:14 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2022-08-22 12:58 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Daniel Gustafsson <daniel@yesql.se>; eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
Julien Rouhaud <rjuju123@gmail.com> writes:
> On Sun, Aug 21, 2022 at 11:02:04PM +0000, PG Doc comments form wrote:
>> It appears the above sentence is referring to the "(1 - sum(mvf))" portion
>> of the formula, however I am not sure what "mvf" is referring to
>> there...shouldn't it be "(1 - sum(mcv))" in order to match what the
>> explanatory sentence is saying?
> It should be mcf, ie. Most Common Frequencies. It looks like a very old typo
> that survived until now.
I don't think it's a typo exactly, but an odd abbreviation for "Most
common Values' Frequencies". (Summing the MCVs themselves isn't
sensible; they might not even be numeric.)
I'd vote for replacing mvf in both places with something a bit more
spelled-out, perhaps "mcv_freqs".
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 12:58 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-12 11:14 ` Daniel Gustafsson <daniel@yesql.se>
2023-04-12 12:14 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Daniel Gustafsson @ 2023-04-12 11:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
> On 22 Aug 2022, at 14:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Julien Rouhaud <rjuju123@gmail.com> writes:
>> On Sun, Aug 21, 2022 at 11:02:04PM +0000, PG Doc comments form wrote:
>>> It appears the above sentence is referring to the "(1 - sum(mvf))" portion
>>> of the formula, however I am not sure what "mvf" is referring to
>>> there...shouldn't it be "(1 - sum(mcv))" in order to match what the
>>> explanatory sentence is saying?
>
>> It should be mcf, ie. Most Common Frequencies. It looks like a very old typo
>> that survived until now.
>
> I don't think it's a typo exactly, but an odd abbreviation for "Most
> common Values' Frequencies". (Summing the MCVs themselves isn't
> sensible; they might not even be numeric.)
>
> I'd vote for replacing mvf in both places with something a bit more
> spelled-out, perhaps "mcv_freqs".
I was inclined to spell it out as mcv_frequencies but we use xxx_freqs
elsewhere on the same page so keeping it consistent seems better. The attached
does this as well as adding mcf/mcv as acronyms as previously mentioned (since
they are both tagged as <acronym>).
--
Daniel Gustafsson
Attachments:
[application/octet-stream] mcf_mcv.diff (1.2K, ../../63973B1A-BFE6-46B4-A6E3-3B098ECAC351@yesql.se/2-mcf_mcv.diff)
download | inline diff:
diff --git a/doc/src/sgml/acronyms.sgml b/doc/src/sgml/acronyms.sgml
index 2df6559acc..72a45e5106 100644
--- a/doc/src/sgml/acronyms.sgml
+++ b/doc/src/sgml/acronyms.sgml
@@ -408,6 +408,24 @@
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><acronym>MCF</acronym></term>
+ <listitem>
+ <para>
+ Most Common Frequencies
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><acronym>MCV</acronym></term>
+ <listitem>
+ <para>
+ Most Common Values
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><acronym>MITM</acronym></term>
<listitem>
diff --git a/doc/src/sgml/planstats.sgml b/doc/src/sgml/planstats.sgml
index df85ea5eea..d2b84b301f 100644
--- a/doc/src/sgml/planstats.sgml
+++ b/doc/src/sgml/planstats.sgml
@@ -203,7 +203,7 @@ EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'xxx';
<acronym>MCV</acronym>s:
<programlisting>
-selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
+selectivity = (1 - sum(mcv_freqs))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 +
0.003 + 0.003 + 0.003 + 0.003))/(676 - 10)
= 0.0014559
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 12:58 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 11:14 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
@ 2023-04-12 12:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 14:22 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-04-12 12:14 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
Daniel Gustafsson <daniel@yesql.se> writes:
> I was inclined to spell it out as mcv_frequencies but we use xxx_freqs
> elsewhere on the same page so keeping it consistent seems better. The attached
> does this as well as adding mcf/mcv as acronyms as previously mentioned (since
> they are both tagged as <acronym>).
mcv_freqs looks good. I'd write the glossary entries as singular
(Most Common Frequency, Most Common Value) since our typical usage
is to pluralize them at the point of use ("MCVs"). Also, just
expanding the acronym doesn't seem that helpful. Maybe more like
MCF
Most Common Frequency, that is the frequency associated
with some Most Common Value
MCV
Most Common Value, one of the values appearing most often
within a particular table column
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Should 'sum(mvf)' read 'sum(mcv)'...?
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 12:58 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 11:14 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Daniel Gustafsson <daniel@yesql.se>
2023-04-12 12:14 ` Re: Should 'sum(mvf)' read 'sum(mcv)'...? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-12 14:22 ` Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 9+ messages in thread
From: Daniel Gustafsson @ 2023-04-12 14:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; eric.mutta@gmail.com; pgsql-docs@lists.postgresql.org
> On 12 Apr 2023, at 14:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Daniel Gustafsson <daniel@yesql.se> writes:
>> I was inclined to spell it out as mcv_frequencies but we use xxx_freqs
>> elsewhere on the same page so keeping it consistent seems better. The attached
>> does this as well as adding mcf/mcv as acronyms as previously mentioned (since
>> they are both tagged as <acronym>).
>
> mcv_freqs looks good. I'd write the glossary entries as singular
> (Most Common Frequency, Most Common Value) since our typical usage
> is to pluralize them at the point of use ("MCVs"). Also, just
> expanding the acronym doesn't seem that helpful. Maybe more like
Pushed with your suggested changes, thanks!
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-04-12 14:22 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-21 23:02 Should 'sum(mvf)' read 'sum(mcv)'...? PG Doc comments form <noreply@postgresql.org>
2022-08-22 07:48 ` Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 09:13 ` Daniel Gustafsson <daniel@yesql.se>
2022-08-22 10:08 ` Julien Rouhaud <rjuju123@gmail.com>
2022-08-22 10:26 ` Daniel Gustafsson <daniel@yesql.se>
2022-08-22 12:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 11:14 ` Daniel Gustafsson <daniel@yesql.se>
2023-04-12 12:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-12 14:22 ` Daniel Gustafsson <daniel@yesql.se>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox