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: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-01-05T15:09:19Z
Lists: pgsql-hackers 

On 1/5/21 3:10 PM, Dean Rasheed wrote:
> On Tue, 5 Jan 2021 at 00:45, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 1/4/21 4:34 PM, Dean Rasheed wrote:
>>>
>>> * In src/bin/psql/describe.c, I think the \d output should also
>>> exclude the "expressions" stats kind and just list the other kinds (or
>>> have no kinds list at all, if there are no other kinds), to make it
>>> consistent with the CREATE STATISTICS syntax.
>>
>> Not sure I understand. Why would this make it consistent with CREATE
>> STATISTICS? Can you elaborate?
>>
> 
> This isn't absolutely essential, but I think it would be neater. For
> example, if I have a table with stats like this:
> 
> CREATE TABLE foo(a int, b int);
> CREATE STATISTICS foo_s_ab (mcv) ON a,b FROM foo;
> 
> then the \d output is as follows:
> 
> \d foo
>                  Table "public.foo"
>   Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>   a      | integer |           |          |
>   b      | integer |           |          |
> Statistics objects:
>      "public"."foo_s_ab" (mcv) ON a, b FROM foo
> 
> and the stats line matches the DDL used to create the stats. It could,
> for example, be copy-pasted and tweaked to create similar stats on
> another table, but even if that's not very likely, it's neat that it
> reflects how the stats were created.
> 
> OTOH, if there are expressions in the list, it produces something like this:
> 
>                  Table "public.foo"
>   Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>   a      | integer |           |          |
>   b      | integer |           |          |
> Statistics objects:
>      "public"."foo_s_ab" (mcv, expressions) ON a, b, ((a * b)) FROM foo
> 
> which no longer matches the DDL used, and isn't part of an accepted
> syntax, so seems a bit inconsistent.
> 
> In general, if we're making the "expressions" kind an internal
> implementation detail that just gets built automatically when needed,
> then I think we should hide it from this sort of output, so the list
> of kinds matches the list that the user used when the stats were
> created.
> 

Hmm, I see. You're probably right it's not necessary to show this, given 
the modified handling of expression stats (which makes them an internal 
detail, not exposed to users). I'll tweak this.

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