8000 Allow setting statistics target for extended statistics · postgrespro/postgres@d06215d · GitHub
[go: up one dir, main page]
Skip to content

    • Commit d06215d

    Browse files
    tvondratvondra
    committed
    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
    1 parent bca6e64 commit d06215d

    File tree

    22 files changed

    +458
    -19
    lines changed

    22 files changed

    +458
    -19
    lines changed

    ‎doc/src/sgml/ref/alter_statistics.sgml

    Lines changed: 17 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -26,6 +26,7 @@ PostgreSQL documentation
    2626
    ALTER STATISTICS <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
    2727
    ALTER STATISTICS <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
    2828
    ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
    29+
    ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET STATISTICS <replaceable class="parameter">new_target</replaceable>
    2930
    </synopsis>
    3031
    </refsynopsisdiv>
    3132

    @@ -93,6 +94,22 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    9394
    </listitem>
    9495
    </varlistentry>
    9596

    97+
    <varlistentry>
    98+
    <term><replaceable class="parameter">new_target</replaceable></term>
    99+
    <listitem>
    100+
    <para>
    101+
    The statistic-gathering target for this statistics object for subsequent
    102+
    <xref linkend="sql-analyze"/> operations.
    103+
    The target can be set in the range 0 to 10000; alternatively, set it
    104+
    to -1 to revert to using the system default statistics
    105+
    target (<xref linkend="guc-default-statistics-target"/>).
    106+
    For more information on the use of statistics by the
    107+
    <productname>PostgreSQL</productname> query planner, refer to
    108+
    <xref linkend="planner-stats"/>.
    109+
    </para>
    110+
    </listitem>
    111+
    </varlistentry>
    112+
    96113
    </variablelist>
    97114
    </para>
    98115
    </refsect1>

    ‎src/backend/commands/analyze.c

    Lines changed: 12 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -307,7 +307,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
    307307
    VacAttrStats **vacattrstats;
    308308
    AnlIndexData *indexdata;
    309309
    int targrows,
    310-
    numrows;
    310+
    numrows,
    311+
    minrows;
    311312
    double totalrows,
    312313
    totaldeadrows;
    313314
    HeapTuple *rows;
    @@ -491,6 +492,16 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
    491492
    }
    492493
    }
    493494

    495+
    /*
    496+
    * Look at extended statistics objects too, as those may define custom
    497+
    * statistics target. So we may need to sample more rows and then build
    498+
    * the statistics with enough detail.
    499+
    */
    500+
    minrows = ComputeExtStatisticsRows(onerel, attr_cnt, vacattrstats);
    501+
    502+
    if (targrows < minrows)
    503+
    targrows = minrows;
    504+
    494505
    /*
    495506
    * Acquire the sample rows
    496507
    */

    ‎src/backend/commands/statscmds.c

    Lines changed: 108 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -14,13 +14,15 @@
    1414
    */
    1515
    #include "postgres.h"
    1616

    17+
    #include "access/heapam.h"
    1718
    #include "access/relation.h"
    1819
    #include "access/relscan.h"
    1920
    #include "access/table.h"
    2021
    #include "catalog/catalog.h"
    2122
    #include "catalog/dependency.h"
    2223
    #include "catalog/indexing.h"
    2324
    #include "catalog/namespace.h"
    25+
    #include "catalog/objectaccess.h"
    2426
    #include "catalog/pg_namespace.h"
    2527
    #include "catalog/pg_statistic_ext.h"
    2628
    #include "catalog/pg_statistic_ext_data.h"
    @@ -29,6 +31,7 @@
    2931
    #include "miscadmin.h"
    3032
    #include "statistics/statistics.h"
    3133
    #include "utils/builtins.h"
    34+
    #include "utils/fmgroids.h"
    3235
    #include "utils/inval.h"
    3336
    #include "utils/memutils.h"
    3437
    #include "utils/rel.h"
    @@ -336,6 +339,7 @@ CreateStatistics(CreateStatsStmt *stmt)
    336339
    values[Anum_pg_statistic_ext_stxrelid - 1] = ObjectIdGetDatum(relid);
    337340
    values[Anum_pg_statistic_ext_stxname - 1] = NameGetDatum(&stxname);
    338341
    values[Anum_pg_statistic_ext_stxnamespace - 1] = ObjectIdGetDatum(namespaceId);
    342+
    values[Anum_pg_statistic_ext_stxstattarget - 1] = Int32GetDatum(-1);
    339343
    values[Anum_pg_statistic_ext_stxowner - 1] = ObjectIdGetDatum(stxowner);
    340344
    values[Anum_pg_statistic_ext_stxkeys - 1] = PointerGetDatum(stxkeys);
    341345
    values[Anum_pg_statistic_ext_stxkind - 1] = PointerGetDatum(stxkind);
    @@ -414,6 +418,110 @@ CreateStatistics(CreateStatsStmt *stmt)
    414418
    return myself;
    415419
    }
    416420

    421+
    /*
    422+
    * ALTER STATISTICS
    423+
    */
    424+
    ObjectAddress
    425+
    AlterStatistics(AlterStatsStmt *stmt)
    426+
    {
    427+
    Relation rel;
    428+
    Oid stxoid;
    429+
    HeapTuple oldtup;
    430+
    HeapTuple newtup;
    431+
    Datum repl_val[Natts_pg_statistic_ext];
    432+
    bool repl_null[Natts_pg_statistic_ext];
    433+
    bool repl_repl[Natts_pg_statistic_ext];
    434+
    ObjectAddress address;
    435+
    int newtarget = stmt->stxstattarget;
    436+
    437+
    /* Limit statistics target to a sane range */
    438+
    if (newtarget < -1)
    439+
    {
    440+
    ereport(ERROR,
    441+
    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
    442+
    errmsg("statistics target %d is too low",
    443+
    newtarget)));
    444+
    }
    445+
    else if (newtarget > 10000)
    446+
    {
    447+
    newtarget = 10000;
    448+
    ereport(WARNING,
    449+
    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
    450+
    errmsg("lowering statistics target to %d",
    451+
    newtarget)));
    452+
    }
    453+
    454+
    /* lookup OID of the statistics object */
    455+
    stxoid = get_statistics_object_oid(stmt->defnames, stmt->missing_ok);
    456+
    457+
    /*
    458+
    * If we got here and the OID is not valid, it means the statistics
    459+
    * does not exist, but the command specified IF EXISTS. So report
    460+
    * this as a simple NOTICE and we're done.
    461+
    */
    462+
    if (!OidIsValid(stxoid))
    463+
    {
    464+
    char *schemaname;
    465+
    char *statname;
    466+
    467+
    Assert(stmt->missing_ok);
    468+
    469+
    DeconstructQualifiedName(stmt->defnames, &schemaname, &statname);
    470+
    471+
    if (schemaname)
    472+
    ereport(NOTICE,
    473+
    (errmsg("statistics object \"%s.%s\" does not exist, skipping",
    474+
    schemaname, statname)));
    475+
    else
    476+
    ereport(NOTICE,
    477+
    (errmsg("statistics object \"%s\" does not exist, skipping",
    478+
    statname)));
    479+
    480+
    return InvalidObjectAddress;
    481+
    }
    482+
    483+
    /* Search pg_statistic_ext */
    484+
    rel = table_open(StatisticExtRelationId, RowExclusiveLock);
    485+
    486+
    oldtup = SearchSysCache1(STATEXTOID, ObjectIdGetDatum(stxoid));
    487+
    488+
    /* Must be owner of the existing statistics object */
    489+
    if (!pg_statistics_object_ownercheck(stxoid, GetUserId()))
    490+
    aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_STATISTIC_EXT,
    491+
    NameListToString(stmt->defnames));
    492+
    493+
    /* Build new tuple. */
    494+
    memset(repl_val, 0, sizeof(repl_val));
    495+
    memset(repl_null, false, sizeof(repl_null));
    496+
    memset(repl_repl, false, sizeof(repl_repl));
    497+
    498+
    /* replace the stxstattarget column */
    499+
    repl_repl[Anum_pg_statistic_ext_stxstattarget - 1] = true;
    500+
    repl_val[Anum_pg_statistic_ext_stxstattarget - 1] = Int32GetDatum(newtarget);
    501+
    502+
    newtup = heap_modify_tuple(oldtup, RelationGetDescr(rel),
    503+
    repl_val, repl_null, repl_repl);
    504+
    505+
    /* Update system catalog. */
    506+
    CatalogTupleUpdate(rel, &newtup->t_self, newtup);
    507+
    508+
    InvokeObjectPostAlterHook(StatisticExtRelationId, stxoid, 0);
    509+
    510+
    ObjectAddressSet(address, StatisticExtRelationId, stxoid);
    511+
    512+
    /*
    513+
    * NOTE: because we only support altering the statistics target, not the
    514+
    * other fields, there is no need to update dependencies.
    515+
    */
    516+
    517+
    heap_freetuple(newtup);
    518+
    ReleaseSysCache(oldtup);
    519+
    520+
    table_close(rel, RowExclusiveLock);
    521+
    522+
    return address;
    523+
    }
    524+
    417525
    /*
    418526
    * Guts of statistics object deletion.
    419527
    */

    ‎src/backend/nodes/copyfuncs.c

    Lines changed: 15 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -3497,6 +3497,18 @@ _copyCreateStatsStmt(const CreateStatsStmt *from)
    34973497
    return newnode;
    34983498
    }
    34993499

    3500+
    static AlterStatsStmt *
    3501+
    _copyAlterStatsStmt(const AlterStatsStmt *from)
    3502+
    {
    3503+
    AlterStatsStmt *newnode = makeNode(AlterStatsStmt);
    3504+
    3505+
    COPY_NODE_FIELD(defnames);
    3506+
    COPY_SCALAR_FIELD(stxstattarget);
    3507+
    COPY_SCALAR_FIELD(missing_ok);
    3508+
    3509+
    return newnode;
    3510+
    }
    3511+
    35003512
    static CreateFunctionStmt *
    35013513
    _copyCreateFunctionStmt(const CreateFunctionStmt *from)
    35023514
    {
    @@ -5211,6 +5223,9 @@ copyObjectImpl(const void *from)
    52115223
    case T_CreateStatsStmt:
    52125224
    retval = _copyCreateStatsStmt(from);
    52135225
    break;
    5226+
    case T_AlterStatsStmt:
    5227+
    retval = _copyAlterStatsStmt(from);
    5228+
    break;
    52145229
    case T_CreateFunctionStmt:
    52155230
    retval = _copyCreateFunctionStmt(from);
    52165231
    break;

    ‎src/backend/nodes/equalfuncs.c

    Lines changed: 13 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1365,6 +1365,16 @@ _equalCreateStatsStmt(const CreateStatsStmt *a, const CreateStatsStmt *b)
    13651365
    return true;
    13661366
    }
    13671367

    1368+
    static bool
    1369+
    _equalAlterStatsStmt(const AlterStatsStmt *a, const AlterStatsStmt *b)
    1370+
    {
    1371+
    COMPARE_NODE_FIELD(defnames);
    1372+
    COMPARE_SCALAR_FIELD(stxstattarget);
    1373+
    COMPARE_SCALAR_FIELD(missing_ok);
    1374+
    1375+
    return true;
    1376+
    }
    1377+
    13681378
    static bool
    13691379
    _equalCreateFunctionStmt(const CreateFunctionStmt *a, const CreateFunctionStmt *b)
    13701380
    {
    @@ -3309,6 +3319,9 @@ equal(const void *a, const void *b)
    33093319
    case T_CreateStatsStmt:
    33103320
    retval = _equalCreateStatsStmt(a, b);
    33113321
    break;
    3322+
    case T_AlterStatsStmt:
    3323+
    retval = _equalAlterStatsStmt(a, b);
    3324+
    break;
    33123325
    case T_CreateFunctionStmt:
    33133326
    retval = _equalCreateFunctionStmt(a, b);
    33143327
    break;

    ‎src/backend/nodes/outfuncs.c

    Lines changed: 13 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -2668,6 +2668,16 @@ _outCreateStatsStmt(StringInfo str, const CreateStatsStmt *node)
    26682668
    WRITE_BOOL_FIELD(if_not_exists);
    26692669
    }
    26702670

    2671+
    static void
    2672+
    _outAlterStatsStmt(StringInfo str, const AlterStatsStmt *node)
    2673+
    {
    2674+
    WRITE_NODE_TYPE("ALTERSTATSSTMT");
    2675+
    2676+
    WRITE_NODE_FIELD(defnames);
    2677+
    WRITE_INT_FIELD(stxstattarget);
    2678+
    WRITE_BOOL_FIELD(missing_ok);
    2679+
    }
    2680+
    26712681
    static void
    26722682
    _outNotifyStmt(StringInfo str, const NotifyStmt *node)
    26732683
    {
    @@ -4130,6 +4140,9 @@ outNode(StringInfo str, const void *obj)
    41304140
    case T_CreateStatsStmt:
    41314141
    _outCreateStatsStmt(str, obj);
    41324142
    break;
    4143+
    case T_AlterStatsStmt:
    4144+ _outAlterStatsStmt(str, obj);
    4145+
    break;
    41334146
    case T_NotifyStmt:
    41344147
    _outNotifyStmt(str, obj);
    41354148
    break;

    ‎src/backend/parser/gram.y

    Lines changed: 30 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -252,7 +252,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
    252252
    AlterOperatorStmt AlterSeqStmt AlterSystemStmt AlterTableStmt
    253253
    AlterTblSpcStmt AlterExtensionStmt AlterExtensionContentsStmt AlterForeignTableStmt
    254254
    AlterCompositeTypeStmt AlterUserMappingStmt
    255-
    AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt
    255+
    AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt AlterStatsStmt
    256256
    AlterDefaultPrivilegesStmt DefACLAction
    257257
    AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt
    258258
    ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
    @@ -852,6 +852,7 @@ stmt :
    852852
    | AlterRoleSetStmt
    853853
    | AlterRoleStmt
    854854
    | AlterSubscriptionStmt
    855+
    | AlterStatsStmt
    855856
    | AlterTSConfigurationStmt
    856857
    | AlterTSDictionaryStmt
    857858
    | AlterUserMappingStmt
    @@ -3984,6 +3985,34 @@ CreateStatsStmt:
    39843985
    }
    39853986
    ;
    39863987

    3988+
    3989+
    /*****************************************************************************
    3990+
    *
    3991+
    * QUERY :
    3992+
    * ALTER STATISTICS [IF EXISTS] stats_name
    3993+
    * SET STATISTICS <SignedIconst>
    3994+
    *
    3995+
    *****************************************************************************/
    3996+
    3997+
    AlterStatsStmt:
    3998+
    ALTER STATISTICS any_name SET STATISTICS SignedIconst
    3999+
    {
    4000+
    AlterStatsStmt *n = makeNode(AlterStatsStmt);
    4001+
    n->defnames = $3;
    4002+
    n->missing_ok = false;
    4003+
    n->stxstattarget = $6;
    4004+
    $$ = (Node *)n;
    4005+
    }
    4006+
    | ALTER STATISTICS IF_P EXISTS any_name SET STATISTICS SignedIconst
    4007+
    {
    4008+
    AlterStatsStmt *n = makeNode(AlterStatsStmt);
    4009+
    n->defnames = $5;
    4010+
    n->missing_ok = true;
    4011+
    n->stxstattarget = $8;
    4012+
    $$ = (Node *)n;
    4013+
    }
    4014+
    ;
    4015+
    39874016
    /*****************************************************************************
    39884017
    *
    39894018
    * QUERY :

    0 commit comments

    Comments
     (0)
    0