MIME-Version: 1.0
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Tue, 1 Aug 2023 09:47:33 -0400
Message-ID: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
Subject: analyze foreign tables
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a1a8040601dcca8d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q%40mail.gmail.com>
Precedence: bulk

--000000000000a1a8040601dcca8d
Content-Type: text/plain; charset="UTF-8"

Hello all,

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 all 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 = 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.

Thanks,
rik.

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

<div dir=3D"ltr">Hello all,=C2=A0<div><br><div>In PostgreSQL foreign tables=
 are not automatically analyzed and analyze must be specifically called on =
each table.=C2=A0 In the case of sharing tables between PostgreSQL clusters=
, there is the=C2=A0use_remote_estimate foreign server option.</div></div><=
div><br></div><div>In some of our multi terabyte databases, manually runnin=
g analyze on all of the foreign tables can take more than a day.=C2=A0 This=
 is per database containing 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.</div><div><br></div><div>use_remote=
_estimate isn&#39;t really a solution as it adds way too much overhead and =
processing time to every query run.<br></div><div><br></div><div>Since thes=
e tables are being continuously analyzed in the database that hosts the dat=
a, is there some way that they statistics could be easily passed through th=
e foreign server mechanism to the remote database that&#39;s calling the qu=
ery?</div><div><br></div><div>Unless I&#39;m missing something we can eithe=
r:<br>1. manually run analyze on each foreign table in each database that p=
oints to the host table</div><div>2. set use_remote_estimate =3D true which=
 will cause PostgreSQL to re-obtain statistics on a per query basis.</div><=
div><br></div><div>What I am hoping for is either:</div><div>1. pass throug=
h the results of analyze from the source database to the one where the fore=
ign query is being run</div><div>2. add the ability to automatically run an=
alyze on foreign tables just as they are currently run on local tables.</di=
v><div><br></div><div>Of the two, #1 would seem to be the easiest and least=
 wasteful of resources.</div><div><br></div><div>Thanks,=C2=A0</div><div>ri=
k.</div><div><br></div><div><br></div></div>

--000000000000a1a8040601dcca8d--