Re: Ideas about a better API for postgres_fdw remote estimates

On 9/4/20 6:23 PM, Ashutosh Bapat wrote:
> 
> 
> On Thu, 3 Sep 2020 at 10:44, Andrey V. Lepikhov 
> <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.ru>> wrote:
> 
>     On 8/31/20 6:19 PM, Ashutosh Bapat wrote:
>      > On Mon, Aug 31, 2020 at 3:36 PM Andrey V. Lepikhov
>      > <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.ru>> wrote:
>      >>
>      >> Thanks for this helpful feedback.
>      > I think the patch has some other problems like it works only for
>      > regular tables on foreign server but a foreign table can be pointing
>      > to any relation like a materialized view, partitioned table or a
>      > foreign table on the foreign server all of which have statistics
>      > associated with them. I didn't look closely but it does not consider
>      > that the foreign table may not have all the columns from the relation
>      > on the foreign server or may have different names. But I think those
>      > problems are kind of secondary. We have to agree on the design first.
>      >
>     In accordance with discussion i made some changes in the patch:
>     1. The extract statistic routine moved into the core.
> 
> 
> Bulk of the patch implements the statistics conversion to and fro json 
> format. I am still not sure whether we need all of that code here.
Yes, i'm sure we'll replace it with something.

Right now, i want to discuss format of statistics dump. Remind, that a 
statistics dump is needed not only for fdw, but it need for the pg_dump. 
And in the dump will be placed something like this:
'SELECT store_relation_statistics(rel, serialized_stat)'

my reasons for using JSON:
* it have conversion infrastructure like json_build_object()
* this is flexible readable format, that can be useful in text dumps of 
relations.

> Can we re-use pg_stats view? That is converting some of the OIDs to names. I 
> agree with anyarray but if that's a problem here it's also a problem for 
> pg_stats view, isn't it?
Right now, I don't know if it is possible to unambiguously convert the 
pg_stats information to a pg_statistic tuple.

> If we can reduce the stats handling code to a 
> minimum or use it for some other purpose as well e.g. pg_stats 
> enhancement, the code changes required will be far less compared to the 
> value that this patch provides.
+1

-- 
regards,
Andrey Lepikhov
Postgres Professional