Allow setting statistics target for extended statistics

Enterprise / PostgreSQL - Tomas Vondra [postgresql.org] - 10 September 2019 22:25 EDT

When building statistics, we need to decide how many rows to sample and how accurate the resulting statistics should be. Until now, it was not possible to explicitly define statistics target for extended statistics objects, the value was always computed from the per-attribute targets with a fallback to the system-wide default statistics target.

That's a bit inconvenient, as it ties together the statistics target set for per-column and extended statistics. In some cases it may be useful to require larger sample / higher accuracy for extended statics (or the other way around), but with this approach that's not possible.

So this commit introduces a new command, allowing to specify statistics target for individual extended statistics objects, overriding the value derived from per-attribute targets (and the system default).

ALTER STATISTICS stat_name SET STATISTICS target_value;

When determining statistics target for an extended statistics object we first look at this explicitly set value. When this value is -1, we fall back to the old formula, looking at the per-attribute targets first and then the system default. This means the behavior is backwards compatible with older PostgreSQL releases.

Author: Tomas Vondra Discussion: https://postgr.es/m/20190618213357.vli3i23vpkset2xd@development

d06215d03b Allow setting statistics target for extended statistics
doc/src/sgml/ref/alter_statistics.sgml | 17 +++
src/backend/commands/analyze.c | 13 +-
src/backend/commands/statscmds.c | 108 ++++++++++++++++
src/backend/nodes/copyfuncs.c | 15 +++
src/backend/nodes/equalfuncs.c | 13 ++
src/backend/nodes/outfuncs.c | 13 ++
src/backend/parser/gram.y | 31 ++++-
src/backend/statistics/extended_stats.c | 150 ++++++++++++++++++++++-
src/backend/statistics/mcv.c | 15 +--
src/backend/tcop/utility.c | 12 ++
src/bin/pg_dump/pg_dump.c | 30 ++++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 11 ++
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_statistic_ext.h | 1 +
src/include/commands/defrem.h | 1 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 12 ++
src/include/statistics/extended_stats_internal.h | 2 +-
src/include/statistics/statistics.h | 2 +
src/test/regress/expected/stats_ext.out | 17 +++
src/test/regress/sql/stats_ext.sql | 10 ++
22 files changed, 458 insertions(+), 19 deletions(-)

Upstream: git.postgresql.org


  • Share