MIME-Version: 1.0
References: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
In-Reply-To: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Thu, 3 Aug 2023 10:19:19 -0400
Message-ID: 
 <CAMkU=1x5m60yaz5sriL12LF=53C8PTZbGiOGtb0Z159nTDKSrg@mail.gmail.com>
Subject: Re: analyze foreign tables
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f29cb806020577cc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMkU%3D1x5m60yaz5sriL12LF%3D53C8PTZbGiOGtb0Z159nTDKSrg%40mail.gmail.com>
Precedence: bulk

--000000000000f29cb806020577cc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 1, 2023 at 9:47=E2=80=AFAM richard coleman <rcoleman.ascentgl@g=
mail.com>
wrote:

>
> use_remote_estimate isn't really a solution as it adds way too much
> overhead and processing time to every query run.
>

Maybe this is the thing which should be addressed.  Can you quantify what
you see here?  How much overhead is being added for each query?  Is this
principally processing time, or network latency?


Since these tables are being continuously analyzed in the database that
> hosts the data, is there some way that they statistics could be easily
> passed through the foreign server mechanism to the remote database that's
> calling the query?
>

Since FDW can cross version boundaries, it is hard to see how this would
work.  Maybe something could be done for the special case of where the
versions match. I think collations/encoding would be a problem, though.


> What I am hoping for is either:
>
> 2. add the ability to automatically run analyze on foreign tables just as
> they are currently run on local tables.
>

That wouldn't work because communication is always initiated on the wrong
side.  But it should be fairly easy to script something outside of the
database which would connect to both, and poll the "foreign"
pg_stat_all_tables.last_autovacuum and initiate a local ANALYZE for each
table which was recently autoanalyzed on the foreign side.

Cheers,

Jeff

--000000000000f29cb806020577cc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Aug 1, 2023 at 9:47=E2=80=AFAM ri=
chard coleman &lt;<a href=3D"mailto:rcoleman.ascentgl@gmail.com">rcoleman.a=
scentgl@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><br></div=
><div>use_remote_estimate isn&#39;t really a solution as it adds way too mu=
ch overhead and processing time to every query run.<br></div></div></blockq=
uote><div><br></div><div>Maybe this is the thing which should be addressed.=
=C2=A0 Can you=C2=A0quantify what you see here?=C2=A0 How much overhead is =
being added for each query?=C2=A0 Is this principally processing time, or n=
etwork latency?</div><div><br></div><div><br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div dir=3D"ltr"><div>Since these tables are bein=
g continuously analyzed in the database that hosts the data, is there some =
way that they statistics could be easily passed through the foreign server =
mechanism to the remote database that&#39;s calling the query?</div></div><=
/blockquote><div><br></div><div>Since FDW can cross version boundaries, it =
is hard to see how this would work.=C2=A0 Maybe something could be done for=
 the special case of where the versions match. I think collations/encoding =
would be a problem, though.</div><div><br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><div dir=3D"ltr"><div><br></div><div>What I am hopin=
g for is either:</div><div><br></div></div></blockquote><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>2. add the ability t=
o automatically run analyze on foreign tables just as they are currently ru=
n on local tables.</div></div></blockquote><div><br></div><div>That wouldn&=
#39;t work because communication is always initiated on the wrong side.=C2=
=A0 But it should be fairly easy to script something outside of the databas=
e which would connect to both, and poll the &quot;foreign&quot; pg_stat_all=
_tables.last_autovacuum and initiate a local ANALYZE for each table which w=
as recently autoanalyzed on the foreign side.=C2=A0</div><div><br></div><di=
v>Cheers,</div><div><br></div><div>Jeff</div></div></div>

--000000000000f29cb806020577cc--