pgsql: Allow setting statistics target for extended statistics

Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

pgsql: Allow setting statistics target for extended statistics

Tomas Vondra-5
Allow setting statistics target for extended statistics

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
Reviewed-by: Kirk Jamison, Dean Rasheed

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/d06215d03b50c264a0f31e335b895ee1b6753e68

Modified Files
--------------
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(-)

Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Allow setting statistics target for extended statistics

Andres Freund
Hi,

On 2019-09-10 22:26:41 +0000, Tomas Vondra wrote:
> Allow setting statistics target for extended statistics

This appears to have missed a catversion bump. Clearly necessary as the
schema of pg_statistic_ext changed.

- Andres