<p>Actually, there are two questions:</p>

<ul>
<li>Where default(100) value is declared?</li>
<li>How can I see <code>Stats target</code> value before applying <code>SET default_statistics_target=1000</code></li>
</ul>

<h2>Where default(100) value is declared?</h2>

<p>You can see it in the <a href="https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/guc.c#L2092" rel="nofollow noreferrer">source code</a> </p>

<blockquote>
<pre><code>{
      {"default_statistics_target", PGC_USERSET, QUERY_TUNING_OTHER,
          gettext_noop("Sets the default statistics target."),
          gettext_noop("This applies to table columns that have not had a "
                       "column-specific target set via ALTER TABLE SET STATISTICS.")
      },
      &amp;default_statistics_target,
      100, 1, 10000,
      NULL, NULL, NULL
  },
</code></pre>
</blockquote>

<h2>How can I see <code>Stats target</code> value before applying <code>SET default_statistics_target=1000</code> ?</h2>

<p>Let's suppose that column <code>bar</code> of table <code>foo</code> has  <code>attstattarget=500</code>:</p>

<pre><code>alter table foo alter column bar set statistics 500;
</code></pre>

<p>So, there are two ways to show <code>attstattarget</code></p>

<ul>
<li>SQL_query:</li>
</ul>

<pre><code>SELECT attrelid::regclass, attname, attstattarget FROM pg_attribute WHERE attstattarget &gt; 0 order by attstattarget desc;

 attrelid | attname | attstattarget
----------+---------+---------------
 foo      | bar     |           500
</code></pre>

<ul>
<li><code>\d+</code> command:</li>
</ul>

<pre><code>\d+ foo

                                                            Table "public.foo"
          Column           |            Type             |                  Modifiers                   | Storage  | Stats target | Description
---------------------------+-----------------------------+----------------------------------------------+----------+--------------+-------------
 bar                       | varchar                     | not null                                     | plain    | 500          |
</code></pre>

<p>** Note**: <code>Postgres</code> doesn't show <code>stats target</code> until it distinct from default value.</p>

<h2><code>300</code> - yet another magic number</h2>

<p>Thx to <a href="https://dba.stackexchange.com/questions/200136/postgresql-what-the-default-statistics-target-value-really-means#comment450743_200177">this comment</a>, you can read a <a href="http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53.1734&amp;rep=rep1&amp;type=pdf" rel="nofollow noreferrer">paper</a> about another magic number and check it in the <a href="https://github.com/postgres/postgres/blob/REL_11_STABLE/src/backend/commands/analyze.c#L1832" rel="nofollow noreferrer">source code</a>:</p>

<blockquote>
<pre><code>/*
   * Determine which standard statistics algorithm to use
   */
  if (OidIsValid(eqopr) &amp;&amp; OidIsValid(ltopr))
  {
      /* Seems to be a scalar datatype */
      stats-&gt;compute_stats = compute_scalar_stats;
      /*--------------------
       * The following choice of minrows is based on the paper
       * "Random sampling for histogram construction: how much is enough?"
       * by Surajit Chaudhuri, Rajeev Motwani and Vivek Narasayya, in
       * Proceedings of ACM SIGMOD International Conference on Management
       * of Data, 1998, Pages 436-447.  Their Corollary 1 to Theorem 5
       * says that for table size n, histogram size k, maximum relative
       * error in bin size f, and error probability gamma, the minimum
       * random sample size is
       *      r = 4 * k * ln(2*n/gamma) / f^2
       * Taking f = 0.5, gamma = 0.01, n = 10^6 rows, we obtain
       *      r = 305.82 * k
       * Note that because of the log function, the dependence on n is
       * quite weak; even at n = 10^12, a 300*k sample gives &lt;= 0.66
       * bin size error with probability 0.99.  So there's no real need to
       * scale for n, which is a good thing because we don't necessarily
       * know it at this point.
       *--------------------
       */
      stats-&gt;minrows = 300 * attr-&gt;attstattarget;
  }
  else if (OidIsValid(eqopr))
  {
      /* We can still recognize distinct values */
      stats-&gt;compute_stats = compute_distinct_stats;
      /* Might as well use the same minrows as above */
      stats-&gt;minrows = 300 * attr-&gt;attstattarget;
  }
  else
  {
      /* Can't do much but the trivial stuff */
      stats-&gt;compute_stats = compute_trivial_stats;
      /* Might as well use the same minrows as above */
      stats-&gt;minrows = 300 * attr-&gt;attstattarget;
  }
</code></pre>
</blockquote>

CC BY-SA 4.0
2020-04-02T18:59:25.937Z
2020-04-02T18:59:25.937Z
Yasen
<p>I want to see the value of <code>default_statistics_target</code> on <code>postgresql</code> before running </p>

<pre><code>SET default_statistics_target=1000
</code></pre>

<p>Its most likely the default (100) but would like to see it. </p>

<p>Where it can  be found ?</p>

<postgresql><performance>
get value of default_statistics_target on postrgesql
hvannia
# get value of default_statistics_target on postrgesql

(Asked by: username_0 on 2020-03-30)

I want to see the value of default_statistics_target on postgresql before running 
SET default_statistics_target=1000

Its most likely the default (100) but would like to see it. 
Where it can  be found ?

**Tags:** postgresql, performance


---

## 1 answers

### Answer by username_1 on 2020-04-02. Score: 2

Actually, there are two questions:

Where default(100) value is declared?
How can I see Stats target value before applying SET default_statistics_target=1000

Where default(100) value is declared?
You can see it in the source code 

{
      {"default_statistics_target", PGC_USERSET, QUERY_TUNING_OTHER,
          gettext_noop("Sets the default statistics target."),
          gettext_noop("This applies to table columns that have not had a "
                       "column-specific target set via ALTER TABLE SET STATISTICS.")
      },
      &default_statistics_target,
      100, 1, 10000,
      NULL, NULL, NULL
  },


How can I see Stats target value before applying SET default_statistics_target=1000 ?
Let's suppose that column bar of table foo has  attstattarget=500:
alter table foo alter column bar set statistics 500;

So, there are two ways to show attstattarget

SQL_query:

SELECT attrelid::regclass, attname, attstattarget FROM pg_attribute WHERE attstattarget > 0 order by attstattarget desc;

 attrelid | attname | attstattarget
----------+---------+---------------
 foo      | bar     |           500


\d+ command:

\d+ foo

                                                            Table "public.foo"
          Column           |            Type             |                  Modifiers                   | Storage  | Stats target | Description
---------------------------+-----------------------------+----------------------------------------------+----------+--------------+-------------
 bar                       | varchar                     | not null                                     | plain    | 500          |

** Note**: Postgres doesn't show stats target until it distinct from default value.
300 - yet another magic number
Thx to this comment, you can read a paper about another magic number and check it in the source code:

/*
   * Determine which standard statistics algorithm to use
   */
  if (OidIsValid(eqopr) && OidIsValid(ltopr))
  {
      /* Seems to be a scalar datatype */
      stats->compute_stats = compute_scalar_stats;
      /*--------------------
       * The following choice of minrows is based on the paper
       * "Random sampling for histogram construction: how much is enough?"
       * by Surajit Chaudhuri, Rajeev Motwani and Vivek Narasayya, in
       * Proceedings of ACM SIGMOD International Conference on Management
       * of Data, 1998, Pages 436-447.  Their Corollary 1 to Theorem 5
       * says that for table size n, histogram size k, maximum relative
       * error in bin size f, and error probability gamma, the minimum
       * random sample size is
       *      r = 4 * k * ln(2*n/gamma) / f^2
       * Taking f = 0.5, gamma = 0.01, n = 10^6 rows, we obtain
       *      r = 305.82 * k
       * Note that because of the log function, the dependence on n is
       * quite weak; even at n = 10^12, a 300*k sample gives <= 0.66
       * bin size error with probability 0.99.  So there's no real need to
       * scale for n, which is a good thing because we don't necessarily
       * know it at this point.
       *--------------------
       */
      stats->minrows = 300 * attr->attstattarget;
  }
  else if (OidIsValid(eqopr))
  {
      /* We can still recognize distinct values */
      stats->compute_stats = compute_distinct_stats;
      /* Might as well use the same minrows as above */
      stats->minrows = 300 * attr->attstattarget;
  }
  else
  {
      /* Can't do much but the trivial stuff */
      stats->compute_stats = compute_trivial_stats;
      /* Might as well use the same minrows as above */
      stats->minrows = 300 * attr->attstattarget;
  }


---

