Message-ID: <615ccd4a1de716c229cf125260896051b1989608.camel@cybertec.at>
Subject: Re: analyze foreign tables
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: richard coleman <rcoleman.ascentgl@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Thu, 03 Aug 2023 14:37:11 +0200
In-Reply-To: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
References: 
	<CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/615ccd4a1de716c229cf125260896051b1989608.camel%40cybertec.at>
Precedence: bulk

On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> In PostgreSQL foreign tables are not automatically analyzed and analyze m=
ust be
> specifically called on each table.=C2=A0 In the case of sharing tables be=
tween PostgreSQL
> clusters, there is the=C2=A0use_remote_estimate foreign server option.
>=20
> In some of our multi terabyte databases, manually running analyze on all =
of the
> foreign tables can take more than a day.=C2=A0 This is per database conta=
ining the
> foreign schema.=C2=A0 Since we have certain large schema that we have cen=
trally 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 foreign =
tables.
This is governed by the option "analyze_sampling" on the foreign table or t=
he foreign
server, and the default value "auto" should be just what you need.

Yours,
Laurenz Albe