postgres_fdw: fix cumulative stats after imported foreign-table stats
Hi,
While testing "[28972b6fc] Add support for importing statistics from remote servers", I found a problem: when remote stats are imported, cumulative stats are not updated.
Here is a repro:
1. Setup: create a loopback server and a remote table
```
evantest=# create extension postgres_fdw;
CREATE EXTENSION
evantest=# CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'evantest');
CREATE SERVER
evantest=# CREATE USER MAPPING FOR CURRENT_USER SERVER loopback OPTIONS (user 'chaol');
CREATE USER MAPPING
evantest=# CREATE TABLE remote_repro (a int, b text);
CREATE TABLE
evantest=# INSERT INTO remote_repro SELECT g, CASE WHEN g % 2 = 0 THEN 'even' ELSE 'odd' END FROM generate_series(1, 10) g;
INSERT 0 10
evantest=# ANALYZE remote_repro;
ANALYZE
```
2. Create two foreign tables pointing to the same remote table, one with restore_stats ‘true'
```
evantest=# CREATE FOREIGN TABLE ft_sample_repro (a int, b text) SERVER loopback OPTIONS (table_name 'remote_repro');
CREATE FOREIGN TABLE
evantest=# CREATE FOREIGN TABLE ft_import_repro (a int, b text) SERVER loopback OPTIONS (table_name 'remote_repro', restore_stats 'true');
CREATE FOREIGN TABLE
```
3. Analyze the two foreign tables and check their cumulative stats
```
evantest=# SELECT pg_stat_reset_single_table_counters('ft_sample_repro'::regclass);
pg_stat_reset_single_table_counters
-------------------------------------
(1 row)
evantest=# SELECT pg_stat_reset_single_table_counters('ft_import_repro'::regclass);
pg_stat_reset_single_table_counters
-------------------------------------
(1 row)
evantest=# ANALYZE VERBOSE ft_sample_repro;
INFO: analyzing "public.ft_sample_repro"
INFO: "ft_sample_repro": table contains 10 rows, 10 rows in sample
INFO: finished analyzing table "evantest.public.ft_sample_repro"
avg read rate: 13.672 MB/s, avg write rate: 4.883 MB/s
buffer usage: 172 hits, 14 reads, 5 dirtied
WAL usage: 8 records, 4 full page images, 26625 bytes, 25656 full page image bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
ANALYZE
evantest=# ANALYZE VERBOSE ft_import_repro;
INFO: importing statistics for foreign table "public.ft_import_repro"
INFO: finished importing statistics for foreign table "public.ft_import_repro"
ANALYZE
evantest=# SELECT relname,
evantest-# pg_stat_get_live_tuples(oid) AS live_tuples,
evantest-# pg_stat_get_analyze_count(oid) AS analyze_count,
evantest-# pg_stat_get_last_analyze_time(oid) IS NOT NULL AS has_last_analyze
evantest-# FROM pg_class
evantest-# WHERE relname IN ('ft_sample_repro', 'ft_import_repro')
evantest-# ORDER BY relname;
relname | live_tuples | analyze_count | has_last_analyze
-----------------+-------------+---------------+------------------
ft_import_repro | 0 | 0 | f
ft_sample_repro | 10 | 1 | t
(2 rows)
```
As we can see, when analyzing ft_import_repro, stats were imported from remote, but ft_import_repro has no live_tuples, etc. cumulative stats.
I think the root cause is that, with 28972b6fc, when stats are imported successfully for a foreign table, do_analyze_rel() is skipped. But do_analyze_rel() is the only place that calls pgstat_report_analyze() to update cumulative stats.
To fix this, I think we need to add an output parameter to ImportForeignStatistics to pass out total live rows. AFAIK, the imported remote relation stats have no dead-tuple estimate, so analyze_rel() can pass 0 as the dead-tuple estimate when calling pgstat_report_analyze(). That gives us the needed data to call pgstat_report_analyze() after a successful import.
With the fix, rerunning the repro, ft_import_repro now has cumulative stats:
```
evantest=# ANALYZE VERBOSE ft_import_repro;
INFO: importing statistics for foreign table "public.ft_import_repro"
INFO: finished importing statistics for foreign table "public.ft_import_repro"
ANALYZE
evantest=#
evantest=# SELECT relname, pg_stat_get_live_tuples(oid) AS live_tuples, pg_stat_get_analyze_count(oid) AS analyze_count, pg_stat_get_last_analyze_time(oid) IS NOT NULL AS has_last_analyze FROM pg_class WHERE relname IN ('ft_sample_repro', 'ft_import_repro') ORDER BY relname;
relname | live_tuples | analyze_count | has_last_analyze
-----------------+-------------+---------------+------------------
ft_import_repro | 10 | 1 | t
ft_sample_repro | 10 | 1 | t
(2 rows)
```
See the attached patch for details.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
v1-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patchapplication/octet-stream; name=v1-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch; x-unix-mode=0644Download+59-13
Hi Chao,
On Fri, Jun 12, 2026 at 12:49 PM Chao Li <li.evan.chao@gmail.com> wrote:
While testing "[28972b6fc] Add support for importing statistics from remote servers", I found a problem: when remote stats are imported, cumulative stats are not updated.
Thanks for the report and patch!
Actually, I have also just noticed this problem in my post-commit
self-review. Will look into the patch.
Best regards,
Etsuro Fujita
On Jun 13, 2026, at 17:32, Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Hi Chao,
On Fri, Jun 12, 2026 at 12:49 PM Chao Li <li.evan.chao@gmail.com> wrote:
While testing "[28972b6fc] Add support for importing statistics from remote servers", I found a problem: when remote stats are imported, cumulative stats are not updated.
Thanks for the report and patch!
Actually, I have also just noticed this problem in my post-commit
self-review. Will look into the patch.Best regards,
Etsuro Fujita
Thanks for confirming. Then I think this issue deserves a place on the open items list, so I just added it.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Mon, Jun 15, 2026 at 4:37 PM Chao Li <li.evan.chao@gmail.com> wrote:
Then I think this issue deserves a place on the open items list, so I just added it.
Thanks for that!
Best regards,
Etsuro Fujita
Hi Etsuro,
On 15/06/2026 12:31, Etsuro Fujita wrote:
On Mon, Jun 15, 2026 at 4:37 PM Chao Li <li.evan.chao@gmail.com> wrote:
Then I think this issue deserves a place on the open items list, so I just added it.
Thanks for that!
Spotted this while going through the open items list. Have you had a
chance to look into this?
- Heikki
Hi Heikki,
On Wed, Jul 1, 2026 at 11:54 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
On 15/06/2026 12:31, Etsuro Fujita wrote:
On Mon, Jun 15, 2026 at 4:37 PM Chao Li <li.evan.chao@gmail.com> wrote:
Then I think this issue deserves a place on the open items list, so I just added it.
Thanks for that!
Spotted this while going through the open items list. Have you had a
chance to look into this?
Yes, I have. I'll provide my review comments by tomorrow at the latest.
Best regards,
Etsuro Fujita
Hi Chao,
On Fri, Jun 12, 2026 at 12:49 PM Chao Li <li.evan.chao@gmail.com> wrote:
I think the root cause is that, with 28972b6fc, when stats are imported successfully for a foreign table, do_analyze_rel() is skipped. But do_analyze_rel() is the only place that calls pgstat_report_analyze() to update cumulative stats.
To fix this, I think we need to add an output parameter to ImportForeignStatistics to pass out total live rows. AFAIK, the imported remote relation stats have no dead-tuple estimate, so analyze_rel() can pass 0 as the dead-tuple estimate when calling pgstat_report_analyze(). That gives us the needed data to call pgstat_report_analyze() after a successful import.
The root-cause analysis is correct, but I don't think that the fix is
the right way to go, because if we modified pgstat_report_analyze() to
report more ANALYZE stats, we would need to modify the
ImportForeignStatistics API as well, which isn't great. To avoid
that, how about calling pgstat_report_analyze() in
postgresImportForeignStatistics(), like the attached? (Actually, I
designed the API as something we entirely replace do_analyze_rel()
with.) I modified the test case as well.
Sorry for the delay.
Best regards,
Etsuro Fujita
Attachments:
Fix-ANALYZE-report-in-postgres-fdw-stat-import-efujita.patchapplication/octet-stream; name=Fix-ANALYZE-report-in-postgres-fdw-stat-import-efujita.patchDownload+67-1
On Jul 3, 2026, at 02:10, Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Hi Chao,
On Fri, Jun 12, 2026 at 12:49 PM Chao Li <li.evan.chao@gmail.com> wrote:
I think the root cause is that, with 28972b6fc, when stats are imported successfully for a foreign table, do_analyze_rel() is skipped. But do_analyze_rel() is the only place that calls pgstat_report_analyze() to update cumulative stats.
To fix this, I think we need to add an output parameter to ImportForeignStatistics to pass out total live rows. AFAIK, the imported remote relation stats have no dead-tuple estimate, so analyze_rel() can pass 0 as the dead-tuple estimate when calling pgstat_report_analyze(). That gives us the needed data to call pgstat_report_analyze() after a successful import.
The root-cause analysis is correct, but I don't think that the fix is
the right way to go, because if we modified pgstat_report_analyze() to
report more ANALYZE stats, we would need to modify the
ImportForeignStatistics API as well, which isn't great. To avoid
that, how about calling pgstat_report_analyze() in
postgresImportForeignStatistics(), like the attached? (Actually, I
designed the API as something we entirely replace do_analyze_rel()
with.) I modified the test case as well.Sorry for the delay.
Best regards,
Etsuro Fujita
<Fix-ANALYZE-report-in-postgres-fdw-stat-import-efujita.patch>
Hi Etsuro-san,
Thanks for your review and for proposing an alternative fix.
I thought postgres_fdw was where we interact with the remote server, while analyze_rel() is the code deciding that ANALYZE succeeded. Ideally, cumulative ANALYZE reporting would be driven from that level, not from an FDW callback implementation.
But I understand your concern about changing the ImportForeignStatistics API, so I’m fine with your proposal. I have integrated your changes into v2.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
v2-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patchapplication/octet-stream; name=v2-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch; x-unix-mode=0644Download+69-2
Hi Chao,
On Sat, Jul 4, 2026 at 10:24 PM Chao Li <li.evan.chao@gmail.com> wrote:
I thought postgres_fdw was where we interact with the remote server, while analyze_rel() is the code deciding that ANALYZE succeeded. Ideally, cumulative ANALYZE reporting would be driven from that level, not from an FDW callback implementation.
The division of labor would be arbitrary: as mentioned upthread, the
FDW callback is designed as a function corresponding to
do_analyze_rel(), which not only updates the stats system catalogs but
reports to pgstats, so I think it's appropriate for the callback to do
the report as well.
But I understand your concern about changing the ImportForeignStatistics API, so I’m fine with your proposal. I have integrated your changes into v2.
Ok, thanks for the integration!
@@ -230,7 +230,8 @@ analyze_rel(Oid relid, RangeVar *relation,
if (fdwroutine->ImportForeignStatistics != NULL &&
fdwroutine->ImportForeignStatistics(onerel, va_cols, elevel))
stats_imported = true;
- else
+
+ if (!stats_imported)Is this a leftover?
Best regards,
Etsuro Fujita
On Jul 5, 2026, at 21:36, Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Hi Chao,
On Sat, Jul 4, 2026 at 10:24 PM Chao Li <li.evan.chao@gmail.com> wrote:
I thought postgres_fdw was where we interact with the remote server, while analyze_rel() is the code deciding that ANALYZE succeeded. Ideally, cumulative ANALYZE reporting would be driven from that level, not from an FDW callback implementation.
The division of labor would be arbitrary: as mentioned upthread, the
FDW callback is designed as a function corresponding to
do_analyze_rel(), which not only updates the stats system catalogs but
reports to pgstats, so I think it's appropriate for the callback to do
the report as well.But I understand your concern about changing the ImportForeignStatistics API, so I’m fine with your proposal. I have integrated your changes into v2.
Ok, thanks for the integration!
@@ -230,7 +230,8 @@ analyze_rel(Oid relid, RangeVar *relation, if (fdwroutine->ImportForeignStatistics != NULL && fdwroutine->ImportForeignStatistics(onerel, va_cols, elevel)) stats_imported = true; - else + + if (!stats_imported)Is this a leftover?
Ah, yes, that’s a leftover. Reverted that in v3.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
v3-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patchapplication/octet-stream; name=v3-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch; x-unix-mode=0644Download+67-1
Hi Chao,
On Mon, Jul 6, 2026 at 11:52 PM Chao Li <li.evan.chao@gmail.com> wrote:
@@ -230,7 +230,8 @@ analyze_rel(Oid relid, RangeVar *relation, if (fdwroutine->ImportForeignStatistics != NULL && fdwroutine->ImportForeignStatistics(onerel, va_cols, elevel)) stats_imported = true; - else + + if (!stats_imported)Is this a leftover?
Ah, yes, that’s a leftover. Reverted that in v3.
Ok, thanks for updating the patch! Committed and back-patched.
Best regards,
Etsuro Fujita
On Jul 7, 2026, at 02:50, Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Hi Chao,
On Mon, Jul 6, 2026 at 11:52 PM Chao Li <li.evan.chao@gmail.com> wrote:
@@ -230,7 +230,8 @@ analyze_rel(Oid relid, RangeVar *relation, if (fdwroutine->ImportForeignStatistics != NULL && fdwroutine->ImportForeignStatistics(onerel, va_cols, elevel)) stats_imported = true; - else + + if (!stats_imported)Is this a leftover?
Ah, yes, that’s a leftover. Reverted that in v3.
Ok, thanks for updating the patch! Committed and back-patched.
Best regards,
Etsuro Fujita
Thanks for pushing.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/