2

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 ?

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.