MIME-Version: 1.0
References: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
 <615ccd4a1de716c229cf125260896051b1989608.camel@cybertec.at>
In-Reply-To: <615ccd4a1de716c229cf125260896051b1989608.camel@cybertec.at>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Fri, 4 Aug 2023 10:48:00 -0400
Message-ID: 
 <CAMkU=1yu3C1bMHBoMovVMbMZ_WLnh3sdh7WxUcwUbiBWGvPsbQ@mail.gmail.com>
Subject: Re: analyze foreign tables
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: richard coleman <rcoleman.ascentgl@gmail.com>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006e4840060219fcd7"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMkU%3D1yu3C1bMHBoMovVMbMZ_WLnh3sdh7WxUcwUbiBWGvPsbQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Aug 3, 2023 at 8:37=E2=80=AFAM 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
> between 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.
>
> I just saw that PostgreSQL v16 uses remote sampling for ANALYZE on foreig=
n
> tables.
> This is governed by the option "analyze_sampling" on the foreign table or
> the foreign
> server, and the default value "auto" should be just what you need.
>
>
In addition to this new feature, analyzing foreign tables can also be
highly dependent on an old feature, fetch_size.  The default fetch_size is
really quite small and might be a bottleneck for ANALYZE.

Cheers,

Jeff

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Aug 3, 2023 at 8:37=E2=80=AFAM La=
urenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cyb=
ertec.at</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote cla=
ss=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, richa=
rd 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 PostgreSQL<br>
&gt; clusters, there is the=C2=A0use_remote_estimate foreign server option.=
<br>
&gt; <br>
&gt; In some of our multi terabyte databases, manually running analyze on a=
ll of the<br>
&gt; foreign tables can take more than a day.=C2=A0 This is per database co=
ntaining the<br>
&gt; foreign schema.=C2=A0 Since we have certain large schema that we have =
centrally located<br>
&gt; and share to all of our other database clusters, this really adds up.<=
br>
<br>
I just saw that PostgreSQL v16 uses remote sampling for ANALYZE on foreign =
tables.<br>
This is governed by the option &quot;analyze_sampling&quot; on the foreign =
table or the foreign<br>
server, and the default value &quot;auto&quot; should be just what you need=
.<br>
<br></blockquote><div><br></div><div>In addition to this=C2=A0new feature, =
analyzing foreign tables can also be highly dependent on an old feature, fe=
tch_size.=C2=A0 The default fetch_size is really quite small and might be a=
 bottleneck for ANALYZE.</div><div><br></div><div>Cheers,</div><div><br></d=
iv><div>Jeff</div></div></div>

--0000000000006e4840060219fcd7--