Horton

Re: PoC/WIP: Extended statistics on expressions

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>, pgsql-hackers@postgresql.org
Date: 2021-01-17T00:23:39Z
Lists: pgsql-hackers

Attachments

On 1/17/21 12:22 AM, Justin Pryzby wrote:
> On Sat, Jan 16, 2021 at 05:48:43PM +0100, Tomas Vondra wrote:
>> +      <entry role="catalog_table_entry"><para role="column_definition">
>> +       <structfield>expr</structfield> <type>text</type>
>> +      </para>
>> +      <para>
>> +       Expression the extended statistics is defined on
>> +      </para></entry>
> 
> Expression the extended statistics ARE defined on
> Or maybe say "on which the extended statistics are defined"
> 

I'm pretty sure "is" is correct because "expression" is singular.

>> +  <para>
>> +   The <command>CREATE STATISTICS</command> command has two basic forms. The
>> +   simple variant allows to build statistics for a single expression, does
> 
> .. ALLOWS BUILDING statistics for a single expression, AND does (or BUT does)
> 
>> +   Expression statistics are per-expression and are similar to creating an
>> +   index on the expression, except that they avoid the overhead of the index.
> 
> Maybe say "overhead of index maintenance"
> 

Yeah, that sounds better.

>> +   All functions and operators used in a statistics definition must be
>> +   <quote>immutable</quote>, that is, their results must depend only on
>> +   their arguments and never on any outside influence (such as
>> +   the contents of another table or the current time).  This restriction
> 
> say "outside factor" or "external factor"
> 

In fact, we've removed the immutability restriction, so this paragraph 
should have been removed.

>> +   results of those expression, and uses default estimates as illustrated
>> +   by the first query.  The planner also does not realize the value of the
> 
> realize THAT
> 

OK, changed.

>> +   second column fully defines the value of the other column, because date
>> +   truncated to day still identifies the month. Then expression and
>> +   ndistinct statistics are built on those two columns:
> 
> I got an error doing this:
> 
> CREATE TABLE t AS SELECT generate_series(1,9) AS i;
> CREATE STATISTICS s ON (i+1) ,(i+1+0) FROM t;
> ANALYZE t;
> SELECT i+1 FROM t GROUP BY 1;
> ERROR:  corrupt MVNDistinct entry
> 

Thanks. There was a thinko in estimate_multivariate_ndistinct, resulting 
in mismatching the ndistinct coefficient items. The attached patch fixes 
that, but I've realized the way we pick the "best" statistics may need 
some improvements (I added an XXX comment about that).


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

  1. Disallow extended statistics on system columns

  2. Identify simple column references in extended statistics

  3. Don't print extra parens around expressions in extended stats

  4. Change position of field "transformed" in struct CreateStatsStmt.

  5. Add transformed flag to nodes/*funcs.c for CREATE STATISTICS

  6. Stabilize stats_ext test with other collations

  7. Extended statistics on expressions

  8. Reduce duration of stats_ext regression tests

  9. Allow composite types in catalog bootstrap

  10. Convert Typ from array to list in bootstrap

  11. Disallow CREATE STATISTICS on system catalogs