Request a Demo
Home   >   mailing lists

Re: Import Statistics in postgres_fdw before resorting to sampling. - Mailing list pgsql-hackers

From Nurlan Tulemisov
Subject Re: Import Statistics in postgres_fdw before resorting to sampling.
Date
Msg-id CALCiY5Od-fJ=v2pF7FmwpwVxDqOZAViGg5htEBTE5ysRXXjovg@mail.gmail.com
Whole thread
In response to Re: Import Statistics in postgres_fdw before resorting to sampling.  (Corey Huinker <corey.huinker@gmail.com>)
Responses Re: Import Statistics in postgres_fdw before resorting to sampling.
List pgsql-hackers
Tree view
Hi 

I would like to follow up on the earlier discussion about remote statistics freshness.

Etsuro suggested checking whether remote statistics are sufficiently fresh before importing them, with a fallback to sampling otherwise [1]. Corey also discussed consulting remote pg_stat_all_tables when deciding whether remote ANALYZE is needed [2].

I would like to explore a simple freshness check before importing statistics, using the same modification threshold used for autoanalyze:

stale = n_mod_since_analyze >
        remote_analyze_threshold +
        remote_analyze_scale_factor * max(reltuples, 0)

All values would come from the remote server, with the threshold and scale factor taking the remote table's autovacuum settings into account, including per-table overrides.

If this condition is true, postgres_fdw would fallback to its existing sampling instead of importing the remote statistics. This would provide a practical criterion for rejecting potentially stale statistics, although it would not guarantee their accuracy when the condition is false.
One limitation to consider is that the statistics counters can be reset.

Does this approach make sense? Are either of you already working on, or planning, a similar check? I would be happy to coordinate.

[1](www.postgresql.org/message-id/CAPmGK16AQzGsEe6pD7m42AO1KRCuXQu%3DU_abXaAf3juCS5U%2BmA%40mail.gmail.com)

[2](www.postgresql.org/message-id/CADkLM%3DeWt0n8xpJkdNkeor7%3D%3DwUcnzYh%2BrSEaFKpdngJk0ZwJg%40mail.gmail.com)

On Wed, Sep 23, 2026 at 6:59 PM Corey Huinker <corey.huinker@gmail.com> wrote:
The problem is not limited to this special case.  Consider cases when
1) the remote table that has many rows are heavily updated after it
got analyzed, and then 2) postgres_fdw imports its stats before it
gets re-analyzed.  The stats postgres_fdw imports would be stale,
causing plan degradation.  I don't think we should enable this feature
by default until we guarantee stats freshness in some way.

So it seems like we have the following configurations desired by at least somebody:

0. Row Sampling Only
1. Fetch stats and fall back to row sampling.
2. Always analyze remote table (assuming it is a table that can hold stats), then fetch stats, and fall back if necessary.
3. Fetch stats, and if that turned up 0 attribute stats try an analyze, then try to refetch and if it still fails go to row sampling.

With the following interpretation of reltuples = 0:

a. The table is definitively empty, stop.
b. The table is missing stats and running an analyze is cheap (assuming remote analysis is even enabled)
c. if remote version >= 14 then a else b

I'm of the opinion that 3c is the best configuration for most tables, and you have advocated for 1a without an analyze option and 2a with one. Option 2 seems a bit heavy handed to me, but I could see checking the remote pg_stat_all_tables and making an analyze/no-analyze judgement call based on that, perhaps call that analyze_stale_vacuum_interval or something like that. That could be a neat feature for v20, and so whatever default we choose for fetch_stats, I ask that we choose values that keep our options open for all 4x3 configurations enumerated above.


--
Regards,
Nurlan

pgsql-hackers by date:

Previous
From: Bertrand Drouvot
Date:
Subject: Re: Persist slot invalidations before publishing them
Next
From: Antonin Houska
Date:
Subject: Re: REPACK (CONCURRENTLY) can silently lose updates when the toast table is rewritten