MIME-Version: 1.0
References: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
 <d38e3d6b4287b9bc52a5236c184cb0a1df4cba6f.camel@cybertec.at>
In-Reply-To: <d38e3d6b4287b9bc52a5236c184cb0a1df4cba6f.camel@cybertec.at>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Tue, 1 Aug 2023 12:36:28 -0400
Message-ID: 
 <CAGA3vBspwtKvaFBXT4c78cdW+mgfXJ68MSDL3TiVdMxpZPp_VA@mail.gmail.com>
Subject: Re: analyze foreign tables
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bfd21e0601df264b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBspwtKvaFBXT4c78cdW%2BmgfXJ68MSDL3TiVdMxpZPp_VA%40mail.gmail.com>
Precedence: bulk

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

Laurenz,

Thanks for taking the time to respond.

Right now I'm stuck with cronning a script to manually run analyze on every
foreign table in every database, which in our case is most of them.

Would it be possible to transfer table statistics between the same version
of PostgreSQL, ex: source is pg15, target is pg15?

Otherwise, anything that can be done to speed this up would be very helpful=
.

Thanks again,
rik.


On Tue, Aug 1, 2023 at 12:16=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> > In PostgreSQL foreign tables are not automatically analyzed and analyze
> must be
> > specifically called on each table.  In the case of sharing tables betwe=
en
> > PostgreSQL clusters, there is the use_remote_estimate foreign server
> option.
> >
> > In some of our multi terabyte databases, manually running analyze on al=
l
> of
> > the foreign tables can take more than a day.  This is per database
> containing
> > the foreign schema.  Since we have certain large schema that we have
> centrally
> > located and share to all of our other database clusters, this really
> adds up.
> >
> > use_remote_estimate isn't really a solution as it adds way too much
> overhead
> > and processing time to every query run.
> >
> > 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?
> >
> > Unless I'm missing something we can either:
> > 1. manually run analyze on each foreign table in each database that
> points to
> >    the host table
> > 2. set use_remote_estimate =3D true which will cause PostgreSQL to
> re-obtain
> >    statistics on a per query basis.
> >
> > What I am hoping for is either:
> > 1. pass through the results of analyze from the source database to the
> one
> >    where the foreign query is being run
> > 2. add the ability to automatically run analyze on foreign tables just
> as they
> >    are currently run on local tables.
> >
> > Of the two, #1 would seem to be the easiest and least wasteful of
> resources.
>
> Unfortunately, both your wishes don't look feasible:
>
> - Transferring table statistics would mean that PostgreSQL understands
> statistics
>   from other server versions.  This is complicated, and we have decided
> not to
>   do this for pg_upgrade, so I don't think we'll try to do it here.
>
> - Autoanalyzing foreign tables would mean that we have some idea how much
> data
>   has changed on the remote server.  How should we do that?
>
> What I can imagine is that instead of reading the complete remote table
> during
> ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part.  That could b=
e
> a
> workable enhancement.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">Laurenz,=C2=A0<div><br></div><div>Thanks for taking the ti=
me to respond.</div><div><br></div><div>Right now I&#39;m stuck with cronni=
ng a script to manually run analyze on every foreign table in every databas=
e, which in our case is most of them.</div><div><br></div><div>Would it be =
possible to transfer table statistics between the same version of PostgreSQ=
L, ex: source is pg15, target is pg15?</div><div><br></div><div>Otherwise, =
anything that can be done to speed this up would be very helpful.</div><div=
><br></div><div>Thanks again,=C2=A0</div><div>rik.</div><div><br></div></di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On T=
ue, Aug 1, 2023 at 12:16=E2=80=AFPM Laurenz Albe &lt;<a href=3D"mailto:laur=
enz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">On Tue, 2023-08-01 at 09:47 -=
0400, richard coleman wrote:<br>
&gt; In PostgreSQL foreign tables are not automatically analyzed and analyz=
e must be<br>
&gt; specifically called on each table.=C2=A0 In the case of sharing tables=
 between<br>
&gt; PostgreSQL clusters, there is the=C2=A0use_remote_estimate foreign ser=
ver option.<br>
&gt; <br>
&gt; In some of our multi terabyte databases, manually running analyze on a=
ll of<br>
&gt; the foreign tables can take more than a day.=C2=A0 This is per databas=
e containing<br>
&gt; the foreign schema.=C2=A0 Since we have certain large schema that we h=
ave centrally<br>
&gt; located and share to all of our other database clusters, this really a=
dds up.<br>
&gt; <br>
&gt; use_remote_estimate isn&#39;t really a solution as it adds way too muc=
h overhead<br>
&gt; and processing time to every query run.<br>
&gt; <br>
&gt; Since these tables are being continuously analyzed in the database tha=
t hosts<br>
&gt; the data, is there some way that they statistics could be easily passe=
d through<br>
&gt; the foreign server mechanism to the remote database that&#39;s calling=
 the query?<br>
&gt; <br>
&gt; Unless I&#39;m missing something we can either:<br>
&gt; 1. manually run analyze on each foreign table in each database that po=
ints to<br>
&gt;=C2=A0 =C2=A0 the host table<br>
&gt; 2. set use_remote_estimate =3D true which will cause PostgreSQL to re-=
obtain<br>
&gt;=C2=A0 =C2=A0 statistics on a per query basis.<br>
&gt; <br>
&gt; What I am hoping for is either:<br>
&gt; 1. pass through the results of analyze from the source database to the=
 one<br>
&gt;=C2=A0 =C2=A0 where the foreign query is being run<br>
&gt; 2. add the ability to automatically run analyze on foreign tables just=
 as they<br>
&gt;=C2=A0 =C2=A0 are currently run on local tables.<br>
&gt; <br>
&gt; Of the two, #1 would seem to be the easiest and least wasteful of reso=
urces.<br>
<br>
Unfortunately, both your wishes don&#39;t look feasible:<br>
<br>
- Transferring table statistics would mean that PostgreSQL understands stat=
istics<br>
=C2=A0 from other server versions.=C2=A0 This is complicated, and we have d=
ecided not to<br>
=C2=A0 do this for pg_upgrade, so I don&#39;t think we&#39;ll try to do it =
here.<br>
<br>
- Autoanalyzing foreign tables would mean that we have some idea how much d=
ata<br>
=C2=A0 has changed on the remote server.=C2=A0 How should we do that?<br>
<br>
What I can imagine is that instead of reading the complete remote table dur=
ing<br>
ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part.=C2=A0 That coul=
d be a<br>
workable enhancement.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000bfd21e0601df264b--