• Tomas Vondra's avatar
    Extended statistics on expressions · a4d75c86
    Tomas Vondra authored
    Allow defining extended statistics on expressions, not just just on
    simple column references.  With this commit, expressions are supported
    by all existing extended statistics kinds, improving the same types of
    estimates. A simple example may look like this:
    
      CREATE TABLE t (a int);
      CREATE STATISTICS s ON mod(a,10), mod(a,20) FROM t;
      ANALYZE t;
    
    The collected statistics are useful e.g. to estimate queries with those
    expressions in WHERE or GROUP BY clauses:
    
      SELECT * FROM t WHERE mod(a,10) = 0 AND mod(a,20) = 0;
    
      SELECT 1 FROM t GROUP BY mod(a,10), mod(a,20);
    
    This introduces new internal statistics kind 'e' (expressions) which is
    built automatically when the statistics object definition includes any
    expressions. This represents single-expression statistics, as if there
    was an expression index (but without the index maintenance overhead).
    The statistics is stored in pg_statistics_ext_data as an array of
    composite types, which is possible thanks to 79f6a942.
    
    CREATE STATISTICS allows building statistics on a single expression, in
    which case in which case it's not possible to specify statistics kinds.
    
    A new system view pg_stats_ext_exprs can be used to display expression
    statistics, similarly to pg_stats and pg_stats_ext views.
    
    ALTER TABLE ... ALTER COLUMN ... TYPE now treats indexes the same way it
    treats indexes, i.e. it drops and recreates the statistics. This means
    all statistics are reset, and we no longer try to preserve at least the
    functional dependencies. This should not be a major issue in practice,
    as the functional dependencies actually rely on per-column statistics,
    which were always reset anyway.
    
    Author: Tomas Vondra
    Reviewed-by: Justin Pryzby, Dean Rasheed, Zhihong Yu
    Discussion: https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com
    a4d75c86
parse_utilcmd.c 131 KB