8000 Change CREATE STATISTICS syntax · postgrespro/postgres_cluster@bc08520 · GitHub
[go: up one dir, main page]
Skip to content

Commit bc08520

Browse files
alvherrealvherre
committed
Change CREATE STATISTICS syntax
Previously, we had the WITH clause in the middle of the command, where you'd specify both generic options as well as statistic types. Few people liked this, so this commit changes it to remove the WITH keyword from that clause and makes it accept statistic types only. (We currently don't have any generic options, but if we invent in the future, we will gain a new WITH clause, probably at the end of the command). Also, the column list is now specified without parens, which makes the whole command look more similar to a SELECT command. This change will let us expand the command to supporting expressions (not just columns names) as well as multiple tables and their join conditions. Tom added lots of code comments and fixed some parts of the CREATE STATISTICS reference page, too; more changes in this area are forthcoming. He also fixed a potential problem in the alter_generic regression test, reducing verbosity on a cascaded drop to avoid dependency on message ordering, as we do in other tests. Tom also closed a security bug: we documented that table ownership was required in order to create a statistics object on it, but didn't actually implement it. Implement tab-completion for statistics objects. This can stand some more improvement. Authors: Alvaro Herrera, with lots of cleanup by Tom Lane Discussion: https://postgr.es/m/20170420212426.ltvgyhnefvhixm6i@alvherre.pgsql
1 parent 46052d9 commit bc08520

File tree

21 files changed

+321
-286
lines changed
  • sql

    • 21 files changed

    +321
    -286
    lines changed

    ‎doc/src/sgml/perform.sgml

    Lines changed: 4 additions & 4 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1132,8 +1132,8 @@ WHERE tablename = 'road';
    11321132
    To inspect functional dependencies on a statistics
    11331133
    <literal>stts</literal>, you may do this:
    11341134
    <programlisting>
    1135-
    CREATE STATISTICS stts WITH (dependencies)
    1136-
    ON (zip, city) FROM zipcodes;
    1135+
    CREATE STATISTICS stts (dependencies)
    1136+
    ON zip, city FROM zipcodes;
    11371137
    ANALYZE zipcodes;
    11381138
    SELECT stxname, stxkeys, stxdependencies
    11391139
    FROM pg_statistic_ext
    @@ -1219,8 +1219,8 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10;
    12191219
    Continuing the above example, the n-distinct coefficients in a ZIP
    12201220
    code table may look like the following:
    12211221
    <programlisting>
    1222-
    CREATE STATISTICS stts2 WITH (ndistinct)
    1223-
    ON (zip, state, city) FROM zipcodes;
    1222+
    CREATE STATISTICS stts2 (ndistinct)
    1223+
    ON zip, state, city FROM zipcodes;
    12241224
    ANALYZE zipcodes;
    12251225
    SELECT stxkeys AS k, stxndistinct AS nd
    12261226
    FROM pg_statistic_ext

    ‎doc/src/sgml/planstats.sgml

    Lines changed: 2 additions & 2 deletions
    Original file line numberDiff line numberDiff line change
    @@ -526,7 +526,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
    526526
    multivariate statistics on the two columns:
    527527

    528528
    <programlisting>
    529-
    CREATE STATISTICS stts WITH (dependencies) ON (a, b) FROM t;
    529+
    CREATE STATISTICS stts (dependencies) ON a, b FROM t;
    530530
    ANALYZE t;
    531531
    EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
    532532
    QUERY PLAN
    @@ -569,7 +569,7 @@ EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
    569569
    calculation, the estimate is much improved:
    570570
    <programlisting>
    571571
    DROP STATISTICS stts;
    572-
    CREATE STATISTICS stts WITH (dependencies, ndistinct) ON (a, b) FROM t;
    572+
    CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
    573573
    ANALYZE t;
    574574
    EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
    575575
    QUERY PLAN

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

    Lines changed: 15 additions & 13 deletions
    Original file line numberDiff line numberDiff line change
    @@ -17,7 +17,7 @@ PostgreSQL documentation
    1717
    <refnamediv>
    1818
    <refname>ALTER STATISTICS</refname>
    1919
    <refpurpose>
    20-
    change the definition of a extended statistics
    20+
    change the definition of an extended statistics object
    2121
    </refpurpose>
    2222
    </refnamediv>
    2323

    @@ -34,19 +34,20 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    3434

    3535
    <para>
    3636
    <command>ALTER STATISTICS</command> changes the parameters of an existing
    37-
    extended statistics. Any parameters not specifically set in the
    37+
    extended statistics object. Any parameters not specifically set in the
    3838
    <command>ALTER STATISTICS</command> command retain their prior settings.
    3939
    </para>
    4040

    4141
    <para>
    42-
    You must own the statistics to use <command>ALTER STATISTICS</>.
    43-
    To change a statistics' schema, you must also have <literal>CREATE</>
    44-
    privilege on the new schema.
    42+
    You must own the statistics object to use <command>ALTER STATISTICS</>.
    43+
    To change a statistics object's schema, you must also
    44+
    have <literal>CREATE</> privilege on the new schema.
    4545
    To alter the owner, you must also be a direct or indirect member of the new
    4646
    owning role, and that role must have <literal>CREATE</literal> privilege on
    47-
    the statistics' schema. (These restrictions enforce that altering the owner
    48-
    doesn't do anything you couldn't do by dropping and recreating the statistics.
    49-
    However, a superuser can alter ownership of any statistics anyway.)
    47+
    the statistics object's schema. (These restrictions enforce that altering
    48+
    the owner doesn't do anything you couldn't do by dropping and recreating
    49+
    the statistics object. However, a superuser can alter ownership of any
    50+
    statistics object anyway.)
    5051
    </para>
    5152
    </refsect1>
    5253

    @@ -59,7 +60,8 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    5960
    <term><replaceable class="parameter">name</replaceable></term>
    6061
    <listitem>
    6162
    <para>
    62-
    The name (optionally schema-qualified) of the statistics to be altered.
    63+
    The name (optionally schema-qualified) of the statistics object to be
    64+
    altered.
    6365
    </para>
    6466
    </listitem>
    6567
    </varlistentry>
    @@ -68,7 +70,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    6870
    <term><replaceable class="PARAMETER">new_owner</replaceable></term>
    6971
    <listitem>
    7072
    <para>
    71-
    The user name of the new owner of the statistics.
    73+
    The user name of the new owner of the statistics object.
    7274
    </para>
    7375
    </listitem>
    7476
    </varlistentry>
    @@ -77,7 +79,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    7779
    <term><replaceable class="parameter">new_name</replaceable></term>
    7880
    <listitem>
    7981
    <para>
    80-
    The new name for the statistics.
    82+
    The new name for the statistics object.
    8183
    </para>
    8284
    </listitem>
    8385
    </varlistentry>
    @@ -86,7 +88,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    8688
    <term><replaceable class="parameter">new_schema</replaceable></term>
    8789
    <listitem>
    8890
    <para>
    89-
    The new schema for the statistics.
    91+
    The new schema for the statistics object.
    9092
    </para>
    9193
    </listitem>
    9294
    </varlistentry>
    @@ -99,7 +101,7 @@ ALTER STATISTICS <replaceable class="parameter">name</replaceable> SET SCHEMA <r
    99101
    <title>Compatibility</title>
    100102

    101103
    <para>
    102-
    There's no <command>ALTER STATISTICS</command> command in the SQL standard.
    104+
    There is no <command>ALTER STATISTICS</command> command in the SQL standard.
    103105
    </para>
    104106
    </refsect1>
    105107

    ‎doc/src/sgml/ref/create_statistics.sgml

    Lines changed: 34 additions & 51 deletions
    Original file line numberDiff line numberDiff line change
    @@ -22,8 +22,8 @@ PostgreSQL documentation
    2222
    <refsynopsisdiv>
    2323
    <synopsis>
    2424
    CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_name</replaceable>
    25-
    WITH ( <replaceable class="PARAMETER">option</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )
    26-
    ON ( <replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...])
    25+
    [ ( <replaceable class="PARAMETER">statistic_type</replaceable> [, ... ] ) ]
    26+
    ON <replaceable class="PARAMETER">column_name</replaceable>, <replaceable class="PARAMETER">column_name</replaceable> [, ...]
    2727
    FROM <replaceable class="PARAMETER">table_name</replaceable>
    2828
    </synopsis>
    2929

    @@ -34,17 +34,17 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
    3434

    3535
    <para>
    3636
    <command>CREATE STATISTICS</command> will create a new extended statistics
    37-
    object on the specified table, foreign table or materialized view.
    38-
    The statistics will be created in the current database and
    39-
    will be owned by the user issuing the command.
    37+
    object tracking data about the specified table, foreign table or
    38+
    materialized view. The statistics object will be created in the current
    39+
    database and will be owned by the user issuing the command.
    4040
    </para>
    4141

    4242
    <para>
    4343
    If a schema name is given (for example, <literal>CREATE STATISTICS
    44-
    myschema.mystat ...</>) then the statistics is created in the specified
    45-
    schema. Otherwise it is created in the current schema. The name of
    46-
    the statistics must be distinct from the name of any other statistics in the
    47-
    same schema.
    44+
    myschema.mystat ...</>) then the statistics object is created in the
    45+
    specified schema. Otherwise it is created in the current schema.
    46+
    The name of the statistics object must be distinct from the name of any
    47+
    other statistics object in the same schema.
    4848
    </para>
    4949
    </refsect1>
    5050

    @@ -57,10 +57,10 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
    5757
    <term><literal>IF NOT EXISTS</></term>
    5858
    <listitem>
    5959
    <para>
    60-
    Do not throw an error if a statistics with the same name already exists.
    61-
    A notice is issued in this case. Note that only the name of the
    62-
    statistics object is considered here. The definition of the statistics is
    63-
    not considered.
    60+
    Do not throw an error if a statistics object with the same name already
    61+
    exists. A notice is issued in this case. Note that only the name of
    62+
    the statistics object is considered here, not the details of its
    63+
    definition.
    6464
    </para>
    6565
    </listitem>
    6666
    </varlistentry>
    @@ -69,67 +69,45 @@ CREATE STATISTICS [ IF NOT EXISTS ] <replaceable class="PARAMETER">statistics_na
    6969
    <term><replaceable class="PARAMETER">statistics_name</replaceable></term>
    7070
    <listitem>
    7171
    <para>
    72-
    The name (optionally schema-qualified) of the statistics to be created.
    72+
    The name (optionally schema-qualified) of the statistics object to be
    73+
    created.
    7374
    </para>
    7475
    </listitem>
    7576
    </varlistentry>
    7677

    7778
    <varlistentry>
    78-
    <term><replaceable class="PARAMETER">column_name</replaceable></term>
    79+
    <term><replaceable class="PARAMETER">statistic_type</replaceable></term>
    7980
    <listitem>
    8081
    <para>
    81-
    The name of a column to be included in the statistics.
    82+
    A statistic type to be computed in this statistics object. Currently
    83+
    supported types are <literal>ndistinct</literal>, which enables
    84+
    n-distinct coefficient tracking,
    85+
    and <literal>dependencies</literal>, which enables functional
    86+
    dependencies.
    8287
    </para>
    8388
    </listitem>
    8489
    </varlistentry>
    8590

    8691
    <varlistentry>
    87-
    <term><replaceable class="PARAMETER">table_name</replaceable></term>
    88-
    <listitem>
    89-
    <para>
    90-
    The name (optionally schema-qualified) of the table the statistics should
    91-
    be created on.
    92-
    </para>
    93-
    </listitem>
    94-
    </varlistentry>
    95-
    96-
    </variablelist>
    97-
    98-
    <refsect2 id="SQL-CREATESTATISTICS-parameters">
    99-
    <title id="SQL-CREATESTATISTICS-parameters-title">Parameters</title>
    100-
    101-
    <indexterm zone="sql-createstatistics-parameters">
    102-
    <primary>statistics parameters</primary>
    103-
    </indexterm>
    104-
    105-
    <para>
    106-
    The <literal>WITH</> clause can specify <firstterm>options</>
    107-
    for the statistics. Available options are listed below.
    108-
    </para>
    109-
    110-
    <variablelist>
    111-
    112-
    <varlistentry>
    113-
    <term><literal>dependencies</> (<type>boolean</>)</term>
    92+
    <term><replaceable class="PARAMETER">column_name</replaceable></term>
    11493
    <listitem>
    11594
    <para>
    116-
    Enables functional dependencies for the statistics.
    95+
    The name of a table column to be included in the statistics object.
    11796
    </para>
    11897
    </listitem>
    11998
    </varlistentry>
    12099

    121100
    <varlistentry>
    122-
    <term><literal>ndistinct</> (<type>boolean</>)</term>
    101+
    <term><replaceable class="PARAMETER">table_name</replaceable></term>
    123102
    <listitem>
    124103
    <para>
    125-
    Enables ndistinct coefficients for the statistics.
    104+
    The name (optionally schema-qualified) of the table containing the
    105+
    column(s) the statistics are computed on.
    126106
    </para>
    127107
    </listitem>
    128108
    </varlistentry>
    129109

    130-
    </variablelist>
    131-
    132-
    </refsect2>
    110+
    </variablelist>
    133111
    </refsect1>
    134112

    135113
    <refsect1>
    @@ -158,7 +136,7 @@ CREATE TABLE t1 (
    158136
    INSERT INTO t1 SELECT i/100, i/500
    159137
    FROM generate_series(1,1000000) s(i);
    160138

    161-
    CREATE STATISTICS s1 WITH (dependencies) ON (a, b) FROM t1;
    139+
    CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;
    162140

    163141
    ANALYZE t1;
    164142

    @@ -168,6 +146,11 @@ EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
    168146
    -- invalid combination of values
    169147
    EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
    170148
    </programlisting>
    149+
    150+
    Without functional-dependency statistics, the planner would make the
    151+
    same estimate of the number of matching rows for these two queries.
    152+
    With such statistics, it is able to tell that one case has matches
    153+
    and the other does not.
    171154
    </para>
    172155

    173156
    </refsect1>
    @@ -176,7 +159,7 @@ EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 1);
    176159
    <title>Compatibility</title>
    177160

    178161
    <para>
    179-
    There's no <command>CREATE STATISTICS</command> command in the SQL standard.
    162+
    There is no <command>CREATE STATISTICS</command> command in the SQL standard.
    180163
    </para>
    181164
    </refsect1>
    182165

    ‎doc/src/sgml/ref/drop_statistics.sgml

    Lines changed: 8 additions & 8 deletions
    Original file line numberDiff line numberDiff line change
    @@ -29,9 +29,9 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
    2929
    <title>Description</title>
    3030

    3131
    <para>
    32-
    <command>DROP STATISTICS</command> removes statistics from the database.
    33-
    Only the statistics owner, the schema owner, and superuser can drop a
    34-
    statistics.
    32+
    <command>DROP STATISTICS</command> removes statistics object(s) from the
    33+
    database. Only the statistics object's owner, the schema owner, or a
    34+
    superuser can drop a statistics object.
    3535
    </para>
    3636

    3737
    </refsect1>
    @@ -44,8 +44,8 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
    4444
    <term><literal>IF EXISTS</literal></term>
    4545
    <listitem>
    4646
    <para>
    47-
    Do not throw an error if the statistics do not exist. A notice is
    48-
    issued in this case.
    47+
    Do not throw an error if the statistics object does not exist. A notice
    48+
    is issued in this case.
    4949
    </para>
    5050
    </listitem>
    5151
    </varlistentry>
    @@ -54,7 +54,7 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
    5454
    <term><replaceable class="PARAMETER">name</replaceable></term>
    5555
    <listitem>
    5656
    <para>
    57-
    The name (optionally schema-qualified) of the statistics to drop.
    57+
    The name (optionally schema-qualified) of the statistics object to drop.
    5858
    </para>
    5959
    </listitem>
    6060
    </varlistentry>
    @@ -66,7 +66,7 @@ DROP STATISTICS [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
    6666
    <title>Examples</title>
    6767

    6868
    <para>
    69-
    To destroy two statistics objects on different schemas, without failing
    69+
    To destroy two statistics objects in different schemas, without failing
    7070
    if they don't exist:
    7171

    7272
    <programlisting>
    @@ -82,7 +82,7 @@ DROP STATISTICS IF EXISTS
    8282
    <title>Compatibility</title>
    8383

    8484
    <para>
    85-
    There's no <command>DROP STATISTICS</command> command in the SQL standard.
    85+
    There is no <command>DROP STATISTICS</command> command in the SQL standard.
    8686
    </para>
    8787
    </refsect1>
    8888

    0 commit comments

    Comments
     (0)
    0