Re: planner support functions: handle GROUP BY estimates ?

On 11/17/20 5:18 PM, Justin Pryzby wrote:
> On Mon, Nov 16, 2020 at 06:24:41PM +0100, Tomas Vondra wrote:
>> On 1/15/20 12:44 AM, Tom Lane wrote:
>>> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>>>> On Tue, Jan 14, 2020 at 05:37:53PM -0500, Tom Lane wrote:
>>>>> I wonder just how messy it would be to add a column to pg_statistic_ext
>>>>> whose type is the composite type "pg_statistic", and drop the required
>>>>> data into that. We've not yet used any composite types in the system
>>>>> catalogs, AFAIR, but since pg_statistic_ext isn't a bootstrap catalog
>>>>> it seems like we might be able to get away with it.
>>>
>>> [ I meant pg_statistic_ext_data, obviously ]
>>>
>>>> I don't know, but feels a bit awkward to store this type of stats into
>>>> pg_statistic_ext, which was meant for multi-column stats. Maybe it'd
>>>> work fine, not sure.
>>
>> I've started looking at statistics on expressions too, mostly because it
>> seems the extended stats improvements (as discussed in [1]) need that.
>>
>> The "stash pg_statistic records into pg_statistics_ext_data" approach
>> seems simple, but it's not clear to me how to make it work, so I'd
>> appreciate some guidance.
>>
>>
>> 1) Considering we don't have any composite types in any catalog yet, and
>> naive attempts to just use something like
>>
>> pg_statistic stxdexprs[1];
>>
>> did not work. So I suppose this will require changes to genbki.pl, but
>> honestly, my Perl-fu is non-existent :-(
>
> In the attached, I didn't need to mess with perl.
>
>> 2) Won't it be an issue that pg_statistic contains pseudo-types? That
>> is, this does not work, for example:
>>
>> test=# create table t (a pg_statistic[]);
>> ERROR: column "stavalues1" has pseudo-type anyarray
>
> It works during initdb for the reasons that it's allowed for pg_statistic.
>

Oh, wow! I haven't expected a patch implementing this, that's great. I
owe you a beer or a drink of your choice.

Thanks!

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