Answers: <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>
  
Answers: CC BY-SA 4.0
Answers: 2020-04-02T18:59:25.937Z
Answers: 2020-04-02T18:59:25.937Z
Answers: Yasen
Body: <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>
  
Tags: <postgresql><performance>
Title: get value of default_statistics_target on postrgesql
UserDisplayName: hvannia
ThreadText: # 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](https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/guc.c#L2092)
  
  > ```
  > {
  >       {"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](https://dba.stackexchange.com/questions/200136/postgresql-what-the-default-statistics-target-value-really-means#comment450743_200177), you can read a [paper](http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53.1734&rep=rep1&type=pdf) about another magic number and check it in the [source code](https://github.com/postgres/postgres/blob/REL_11_STABLE/src/backend/commands/analyze.c#L1832):
  
  > ```
  > /*
  >    * 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;
  >   }
  >
  > ```
  
  
  ---
  
  