Re: analyze foreign tables

On Thu, Aug 3, 2023 at 8:37 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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 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