Subject: Re: PoC/WIP: Extended statistics on expressions
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <ad7891d2-e90c-b446-9fe2-7419143847d7@enterprisedb.com>
 <3fbbd68b-0b52-cb5a-d468-7e7280c33e6c@enterprisedb.com>
 <4654037b-8df8-77fb-2981-67be93aebdf1@enterprisedb.com>
 <20201123022604.GD24052@telsasoft.com>
 <e5beb5d5-28b4-7aad-3a16-9a6abf57f897@enterprisedb.com>
 <6455e0ad-2a2e-bdb0-e1f8-fd4983d1c8aa@enterprisedb.com>
 <087086de-1384-8374-4efc-2e7915071d5c@enterprisedb.com>
 <CAEZATCU9uPo7JYdx4k0-ufXXZH8t7itodibUwCva+s+AvAKcnw@mail.gmail.com>
 <b2995773-c9a1-5d3b-fc90-4f3ab189be11@enterprisedb.com>
 <CAEZATCWmQOFUqo4GXPhGsYGrbhYq0J4ZMz8B-LZXptu7S2y2GQ@mail.gmail.com>
 <4a2918bb-49b0-ccd5-3af7-f7795941a6ad@enterprisedb.com>
 <CAEZATCUiy-Vtj9RfVrTMCQpfVO_VaCD9Dk4jBj0j_eEFEBS1ow@mail.gmail.com>
 <958870c8-65e0-31b1-4591-b0b10e807dd9@enterprisedb.com>
 <CAEZATCXAPrrOKwEsyZKQ4uzzJQWBCt6QAvOcgqRGdWwT1zb+rQ@mail.gmail.com>
 <329203a4-7cad-a0e3-d5a8-ce70872c8156@enterprisedb.com>
 <CAEZATCWGYooC_Tzt8sQo7760ZEsguj0JGoMLhkANrDwJ9CY20Q@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <2567ae7e-e1dd-48b5-054d-ed6ff81c0335@enterprisedb.com>
Date: Tue, 5 Jan 2021 16:09:19 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAEZATCWGYooC_Tzt8sQo7760ZEsguj0JGoMLhkANrDwJ9CY20Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


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