get value of default_statistics_target on postrgesql

Question

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 ?

Answer 1

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;
  }