Re: Import Statistics in postgres_fdw before resorting to sampling. - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: Import Statistics in postgres_fdw before resorting to sampling. |
| Date | |
| Msg-id | CADkLM=fPeNK-jf63LFcwhD=ZxH8Yp4OsuOQcBqj5y1GcKrXuxg@mail.gmail.com Whole thread |
| In response to | Re: Import Statistics in postgres_fdw before resorting to sampling. (Nurlan Tulemisov <nurlan.tulemisov@gmail.com>) |
| Responses |
Re: Import Statistics in postgres_fdw before resorting to sampling.
|
| List | pgsql-hackers |
On Wed, Sep 23, 2026 at 12:15 PM Nurlan Tulemisov <nurlan.tulemisov@gmail.com> wrote:
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].
There was also an option for initiating an ANALYZE on the remote system if the current stats failed in some way, then re-trying the import,and only then falling back to sampling...though there's likely few scenarios in which an ANALYZE worked but the table sample does.
Checking the freshness via pg_stat_all_tables is attractive because it is cheap, but what constitutes "fresh" and how would we configure that? Is it a time interval beyond which stats are judged too old? Wouldn't such a thing cause false positives if the remote table is never updated? I think that there's considerable discussion to be had as to what such a feature would look like, and the implementation of it will be pretty small by comparison. Obviously those discussions have been on pause with all of the v19 issues.
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.
So, basically you're proposing that we use the criteria of "the remote server knows it should analyze this table, but hasn't yet for some reason"? That's simple enough, but I wonder if that information wouldn't better be used to defer the current local autoanalyze, using the thinking that we're likely to get better stats after the remote catches up.
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.
I'm happy to resume efforts on this for v20+, but I want to be very sure that we have consensus about what the right course of action is. This feature has had several loops where progress was made toward a design and implementation, but failed to get the attention of other parties who then had objections to the chosen design or implementation. Additionally, I think that it makes sense to see how this feature is used in the wild, and let that guide additional feature direction.
pgsql-hackers by date: