Message-ID: <76596388-6fe6-0baf-351d-734458a46d76@enterprisedb.com>
Date: Thu, 2 Nov 2023 14:52:20 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.15.1
Subject: Re: Statistics Import and Export
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 pgsql-hackers@lists.postgresql.org
References: <CADkLM=cB0rF3p_FuWRTMSV0983ihTRpsH+OCpNyiqE7Wk0vUWA@mail.gmail.com>
 <CAExHW5uRNe856LPYH1eZFyeASv4Xs4-9d1Gqj+rzS+3StVGLxQ@mail.gmail.com>
 <CADkLM=c_iuAN19_94ikG5VeR_Z3LDkR6fKyeOz=ZKr44qweYjg@mail.gmail.com>
 <CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com>
 <f0930823-d44b-21df-d718-ee3c657d9087@enterprisedb.com>
 <CADkLM=cx-XM-PTscj+uDi24x3_vDpYMOjfHgw9CFt_ad5poc0A@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CADkLM=cx-XM-PTscj+uDi24x3_vDpYMOjfHgw9CFt_ad5poc0A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/76596388-6fe6-0baf-351d-734458a46d76%40enterprisedb.com>
Precedence: bulk

On 11/2/23 06:01, Corey Huinker wrote:
> 
> 
>     Maybe I just don't understand, but I'm pretty sure ANALYZE does not
>     derive index stats from column stats. It actually builds them from the
>     row sample.
> 
> 
> That is correct, my error.
>  
> 
> 
>     > * now support extended statistics except for MCV, which is currently
>     > serialized as an difficult-to-decompose bytea field.
> 
>     Doesn't pg_mcv_list_items() already do all the heavy work?
> 
> 
> Thanks! I'll look into that.
> 
> The comment below in mcv.c made me think there was no easy way to get
> output.
> 
> /*
>  * pg_mcv_list_out      - output routine for type pg_mcv_list.
>  *
>  * MCV lists are serialized into a bytea value, so we simply call byteaout()
>  * to serialize the value into text. But it'd be nice to serialize that into
>  * a meaningful representation (e.g. for inspection by people).
>  *
>  * XXX This should probably return something meaningful, similar to what
>  * pg_dependencies_out does. Not sure how to deal with the deduplicated
>  * values, though - do we want to expand that or not?
>  */
> 

Yeah, that was the simplest output function possible, it didn't seem
worth it to implement something more advanced. pg_mcv_list_items() is
more convenient for most needs, but it's quite far from the on-disk
representation.

That's actually a good question - how closely should the exported data
be to the on-disk format? I'd say we should keep it abstract, not tied
to the details of the on-disk format (which might easily change between
versions).

I'm a bit confused about the JSON schema used in pg_statistic_export
view, though. It simply serializes stakinds, stavalues, stanumbers into
arrays ... which works, but why not to use the JSON nesting? I mean,
there could be a nested document for histogram, MCV, ... with just the
correct fields.

  {
    ...
    histogram : { stavalues: [...] },
    mcv : { stavalues: [...], stanumbers: [...] },
    ...
  }

and so on. Also, what does TRIVIAL stand for?

regards

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