patternsqlMinor
get value of default_statistics_target on postrgesql
Viewed 0 times
default_statistics_targetgetpostrgesqlvalue
Problem
I want to see the value of
Its most likely the default (100) but would like to see it.
Where it can be found ?
default_statistics_target on postgresql before running SET default_statistics_target=1000Its most likely the default (100) but would like to see it.
Where it can be found ?
Solution
Actually, there are two questions:
Where default(100) value is declared?
You can see it in the source code
How can I see
Let's suppose that column
So, there are two ways to show
Note:
Thx to this comment, you can read a paper about another magic number and check it in the source code:
- Where default(100) value is declared?
- How can I see
Stats targetvalue before applyingSET 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 numberThx 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 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;
}Code Snippets
{
{"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
},alter table foo alter column bar set statistics 500;SELECT attrelid::regclass, attname, attstattarget FROM pg_attribute WHERE attstattarget > 0 order by attstattarget desc;
attrelid | attname | attstattarget
----------+---------+---------------
foo | bar | 500\d+ foo
Table "public.foo"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------------+-----------------------------+----------------------------------------------+----------+--------------+-------------
bar | varchar | not null | plain | 500 |/*
* 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;
}Context
StackExchange DevOps Q#11196, answer score: 2
Revisions (0)
No revisions yet.