On Thu, Aug 3, 2023 at 8:37 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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 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.
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 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
