Message-ID: <d38e3d6b4287b9bc52a5236c184cb0a1df4cba6f.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: Tue, 01 Aug 2023 18:16:01 +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/d38e3d6b4287b9bc52a5236c184cb0a1df4cba6f.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 c=
ontaining
> the foreign schema.=C2=A0 Since we have certain large schema that we have=
 centrally
> located and share to all of our other database clusters, this really adds=
 up.
>=20
> use_remote_estimate isn't really a solution as it adds way too much overh=
ead
> and processing time to every query run.
>=20
> Since these tables are being continuously analyzed in the database that h=
osts
> the data, is there some way that they statistics could be easily passed t=
hrough
> the foreign server mechanism to the remote database that's calling the qu=
ery?
>=20
> Unless I'm missing something we can either:
> 1. manually run analyze on each foreign table in each database that point=
s to
>    the host table
> 2. set use_remote_estimate =3D true which will cause PostgreSQL to re-obt=
ain
>    statistics on a per query basis.
>=20
> What I am hoping for is either:
> 1. pass through the results of analyze from the source database to the on=
e
>    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.
>=20
> Of the two, #1 would seem to be the easiest and least wasteful of resourc=
es.

Unfortunately, both your wishes don't look feasible:

- Transferring table statistics would mean that PostgreSQL understands stat=
istics
  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 d=
ata
  has changed on the remote server.  How should we do that?

What I can imagine is that instead of reading the complete remote table dur=
ing
ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part.  That could be =
a
workable enhancement.

Yours,
Laurenz Albe