public inbox for [email protected]
help / color / mirror / Atom feedpostgres_fdw: fix cumulative stats after imported foreign-table stats
12+ messages / 3 participants
[nested] [flat]
* postgres_fdw: fix cumulative stats after imported foreign-table stats
@ 2026-06-12 03:48 Chao Li <[email protected]>
2026-06-13 09:32 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-03 09:10 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Chao Li @ 2026-06-12 03:48 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Corey Huinker <[email protected]>
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:
[application/octet-stream] v1-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch (9.1K, ../../[email protected]/2-v1-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch)
download | inline diff:
From 5dc6c77c6ed511c1b68ec196537647899ee36c8d Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Fri, 12 Jun 2026 11:20:19 +0800
Subject: [PATCH v1] Fix ANALYZE reporting after imported foreign-table stats
Commit 28972b6fc added the ImportForeignStatistics FDW callback, which
lets ANALYZE import remote statistics instead of sampling a foreign table.
When that path succeeds, analyze_rel() skips do_analyze_rel(). That also
skips pgstat_report_analyze(), so cumulative ANALYZE stats such as
analyze_count, last_analyze_time, and live_tuples are not updated even
though ANALYZE succeeded.
Fix by extending the ImportForeignStatistics callback to return the
imported live-row estimate, and have analyze_rel() report ANALYZE to the
cumulative stats system after a successful import. For postgres_fdw, the
returned row estimate comes from the imported remote reltuples value.
Since imported relation stats do not include a dead-tuple estimate,
analyze_rel() reports zero dead tuples for this path.
Add regression coverage for postgres_fdw stats import to verify that a
successful imported ANALYZE updates live_tuples, analyze_count, and
last_analyze_time.
Author: Chao Li <[email protected]>
Reported-by:
Discussion: https://postgr.es/m/
---
.../postgres_fdw/expected/postgres_fdw.out | 20 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 11 ++++++++--
contrib/postgres_fdw/sql/postgres_fdw.sql | 5 +++++
doc/src/sgml/fdwhandler.sgml | 12 ++++++-----
src/backend/commands/analyze.c | 20 +++++++++++++++----
src/include/foreign/fdwapi.h | 3 ++-
6 files changed, 59 insertions(+), 12 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index e90289e4ab1..c16658966d7 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12888,9 +12888,29 @@ ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- no attribute statistics found for column "c2" of remote table "public.simport_table"
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('simport_ftable'::regclass),
+ pg_stat_get_analyze_count('simport_ftable'::regclass),
+ pg_stat_get_last_analyze_time('simport_ftable'::regclass) IS NOT NULL;
+ pg_stat_get_live_tuples | pg_stat_get_analyze_count | ?column?
+-------------------------+---------------------------+----------
+ 4 | 1 | t
+(1 row)
+
ANALYZE VERBOSE simport_ftable (c1); -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0a589f8db74..7562677fc1d 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -336,6 +336,7 @@ typedef struct
PGresult *rel;
PGresult *att;
int server_version_num;
+ double reltuples;
} RemoteStatsResults;
/* Column order in relation stats query */
@@ -584,7 +585,8 @@ static bool postgresAnalyzeForeignTable(Relation relation,
BlockNumber *totalpages);
static bool postgresImportForeignStatistics(Relation relation,
List *va_cols,
- int elevel);
+ int elevel,
+ double *totalrows);
static List *postgresImportForeignSchema(ImportForeignSchemaStmt *stmt,
Oid serverOid);
static void postgresGetForeignJoinPaths(PlannerInfo *root,
@@ -5588,7 +5590,8 @@ analyze_row_processor(PGresult *res, int row, PgFdwAnalyzeState *astate)
* Attempt to fetch/restore remote statistics instead of sampling.
*/
static bool
-postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
+postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel,
+ double *totalrows)
{
const char *schemaname = NULL;
const char *relname = NULL;
@@ -5665,9 +5668,12 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
attrcnt, remattrmap, &remstats);
if (ok)
+ {
+ *totalrows = remstats.reltuples;
ereport(elevel,
(errmsg("finished importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ }
PQclear(remstats.rel);
PQclear(remstats.att);
@@ -5770,6 +5776,7 @@ fetch_remote_statistics(Relation relation,
* to sampling.
*/
reltuples = strtod(PQgetvalue(relstats, 0, RELSTATS_RELTUPLES), NULL);
+ remstats->reltuples = reltuples;
if (((server_version_num < 140000) && (reltuples == 0)) ||
((server_version_num >= 140000) && (reltuples == -1)))
{
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index dfc58beb0d2..494b97c738e 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4546,7 +4546,12 @@ ANALYZE simport_ftable; -- should fail
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('simport_ftable'::regclass),
+ pg_stat_get_analyze_count('simport_ftable'::regclass),
+ pg_stat_get_last_analyze_time('simport_ftable'::regclass) IS NOT NULL;
ANALYZE VERBOSE simport_ftable (c1); -- should work
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 8685a078c52..207264529cf 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -1414,7 +1414,8 @@ AcquireSampleRowsFunc(Relation relation,
bool
ImportForeignStatistics(Relation relation,
List *va_cols,
- int elevel);
+ int elevel,
+ double *totalrows);
</programlisting>
This function is called before the <function>AnalyzeForeignTable</function>
@@ -1426,10 +1427,11 @@ ImportForeignStatistics(Relation relation,
describing the target foreign table. <literal>va_cols</literal>, if not
NIL, contains the columns specified in the <command>ANALYZE</command>
command. <literal>elevel</literal> contains a flag indicating a logging
- level to use.
- If the function imports the statistics successfully, it should return
- <literal>true</literal>. Otherwise, return <literal>false</literal>, in
- which case <function>AnalyzeForeignTable</function> callback function is
+ level to use. If the function imports the statistics successfully, it
+ should store the estimated number of live rows for the foreign table into
+ <literal>totalrows</literal> and return <literal>true</literal>.
+ Otherwise, return <literal>false</literal>, in which case
+ <function>AnalyzeForeignTable</function> callback function is
called on the foreign table to collect statistics locally, if supported.
</para>
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index f66e80b757c..d6625d4057e 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -227,10 +227,22 @@ analyze_rel(Oid relid, RangeVar *relation,
fdwroutine = GetFdwRoutineForRelation(onerel, false);
- if (fdwroutine->ImportForeignStatistics != NULL &&
- fdwroutine->ImportForeignStatistics(onerel, va_cols, elevel))
- stats_imported = true;
- else
+ if (fdwroutine->ImportForeignStatistics != NULL)
+ {
+ double imported_totalrows = 0;
+ TimestampTz import_starttime = GetCurrentTimestamp();
+
+ if (fdwroutine->ImportForeignStatistics(onerel, va_cols, elevel,
+ &imported_totalrows))
+ {
+ pgstat_report_analyze(onerel, imported_totalrows, 0,
+ (va_cols == NIL), import_starttime);
+
+ stats_imported = true;
+ }
+ }
+
+ if (!stats_imported)
{
bool ok = false;
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index abf59a0d8ad..bd38d5e6db7 100644
--- a/src/include/foreign/fdwapi.h
+++ b/src/include/foreign/fdwapi.h
@@ -159,7 +159,8 @@ typedef bool (*AnalyzeForeignTable_function) (Relation relation,
typedef bool (*ImportForeignStatistics_function) (Relation relation,
List *va_cols,
- int elevel);
+ int elevel,
+ double *totalrows);
typedef List *(*ImportForeignSchema_function) (ImportForeignSchemaStmt *stmt,
Oid serverOid);
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
@ 2026-06-13 09:32 ` Etsuro Fujita <[email protected]>
2026-06-15 07:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Etsuro Fujita @ 2026-06-13 09:32 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
Hi Chao,
On Fri, Jun 12, 2026 at 12:49 PM Chao Li <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-13 09:32 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
@ 2026-06-15 07:36 ` Chao Li <[email protected]>
2026-06-15 09:31 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Chao Li @ 2026-06-15 07:36 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
> On Jun 13, 2026, at 17:32, Etsuro Fujita <[email protected]> wrote:
>
> Hi Chao,
>
> On Fri, Jun 12, 2026 at 12:49 PM Chao Li <[email protected]> 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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-13 09:32 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-06-15 07:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
@ 2026-06-15 09:31 ` Etsuro Fujita <[email protected]>
2026-07-01 14:54 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Heikki Linnakangas <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Etsuro Fujita @ 2026-06-15 09:31 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
On Mon, Jun 15, 2026 at 4:37 PM Chao Li <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-13 09:32 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-06-15 07:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-15 09:31 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
@ 2026-07-01 14:54 ` Heikki Linnakangas <[email protected]>
2026-07-01 22:57 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2026-07-01 14:54 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>; Chao Li <[email protected]>
Hi Etsuro,
On 15/06/2026 12:31, Etsuro Fujita wrote:
> On Mon, Jun 15, 2026 at 4:37 PM Chao Li <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-13 09:32 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-06-15 07:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-15 09:31 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-01 14:54 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Heikki Linnakangas <[email protected]>
@ 2026-07-01 22:57 ` Etsuro Fujita <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Etsuro Fujita @ 2026-07-01 22:57 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>; Chao Li <[email protected]>
Hi Heikki,
On Wed, Jul 1, 2026 at 11:54 PM Heikki Linnakangas <[email protected]> wrote:
> On 15/06/2026 12:31, Etsuro Fujita wrote:
> > On Mon, Jun 15, 2026 at 4:37 PM Chao Li <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
@ 2026-07-03 09:10 ` Etsuro Fujita <[email protected]>
2026-07-04 13:23 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Etsuro Fujita @ 2026-07-03 09:10 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
Hi Chao,
On Fri, Jun 12, 2026 at 12:49 PM Chao Li <[email protected]> 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:
[application/octet-stream] Fix-ANALYZE-report-in-postgres-fdw-stat-import-efujita.patch (6.9K, ../../CAPmGK17uDi89_D16UHrcct5_JwbtY_ojFtkd5gXsvJNiVJDg5Q@mail.gmail.com/2-Fix-ANALYZE-report-in-postgres-fdw-stat-import-efujita.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0805c56cb1b..5ebae1cedc2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12973,9 +12973,29 @@ ALTER FOREIGN TABLE simport_ftable OPTIONS (ADD restore_stats 'true');
ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- remote table "public.simport_table" has no relation statistics to import
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
+ pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_analyze_count
+-------------------------+-------------------------+---------------------------
+ 0 | 0 | 1
+(1 row)
+
ALTER TABLE simport_table ALTER COLUMN c1 SET STATISTICS 0;
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS 0;
INSERT INTO simport_table VALUES (1, 'foo'), (1, 'foo'), (2, 'bar'), (2, 'bar');
@@ -12988,9 +13008,29 @@ ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- no attribute statistics found for column "c2" of remote table "public.simport_table"
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
+ pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_analyze_count
+-------------------------+-------------------------+---------------------------
+ 4 | 0 | 1
+(1 row)
+
ANALYZE VERBOSE simport_ftable (c1); -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6fa45773c30..0ea72a64ce5 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -41,6 +41,7 @@
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
#include "parser/parsetree.h"
+#include "pgstat.h"
#include "postgres_fdw.h"
#include "statistics/statistics.h"
#include "storage/latch.h"
@@ -52,6 +53,7 @@
#include "utils/rel.h"
#include "utils/sampling.h"
#include "utils/selfuncs.h"
+#include "utils/timestamp.h"
PG_MODULE_MAGIC_EXT(
.name = "postgres_fdw",
@@ -336,6 +338,8 @@ typedef struct
PGresult *rel;
PGresult *att;
int server_version_num;
+ double livetuples;
+ double deadtuples;
} RemoteStatsResults;
/* Column order in relation stats query */
@@ -5597,6 +5601,7 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
RemoteStatsResults remstats = {.rel = NULL, .att = NULL};
RemoteAttributeMapping *remattrmap = NULL;
int attrcnt = 0;
+ TimestampTz starttime = 0;
bool restore_stats = false;
bool ok = false;
ListCell *lc;
@@ -5656,6 +5661,8 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
(errmsg("importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ starttime = GetCurrentTimestamp();
+
ok = fetch_remote_statistics(relation, va_cols,
table, schemaname, relname,
&attrcnt, &remattrmap, &remstats);
@@ -5665,9 +5672,15 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
attrcnt, remattrmap, &remstats);
if (ok)
+ {
+ pgstat_report_analyze(relation,
+ remstats.livetuples, remstats.deadtuples,
+ (va_cols == NIL), starttime);
+
ereport(elevel,
(errmsg("finished importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ }
PQclear(remstats.rel);
PQclear(remstats.att);
@@ -5780,7 +5793,6 @@ fetch_remote_statistics(Relation relation,
goto fetch_cleanup;
}
-
if (reltuples > 0)
{
StringInfoData column_list;
@@ -5807,6 +5819,10 @@ fetch_remote_statistics(Relation relation,
}
}
+ /* We assume that we have no dead tuple. */
+ remstats->deadtuples = 0.0;
+ remstats->livetuples = reltuples;
+
ok = true;
fetch_cleanup:
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8162c5496bf..e868da00ace 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4587,7 +4587,12 @@ ANALYZE simport_ftable; -- should fail
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
ALTER TABLE simport_table ALTER COLUMN c1 SET STATISTICS 0;
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS 0;
@@ -4604,7 +4609,12 @@ ANALYZE simport_ftable; -- should fail
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable (c1); -- should work
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-03 09:10 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
@ 2026-07-04 13:23 ` Chao Li <[email protected]>
2026-07-06 04:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Chao Li @ 2026-07-04 13:23 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
> On Jul 3, 2026, at 02:10, Etsuro Fujita <[email protected]> wrote:
>
> Hi Chao,
>
> On Fri, Jun 12, 2026 at 12:49 PM Chao Li <[email protected]> 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:
[application/octet-stream] v2-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch (8.7K, ../../[email protected]/2-v2-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch)
download | inline diff:
From 2c2f0e6253e0f57e88793619f3e83babf186ef4f Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Fri, 12 Jun 2026 11:20:19 +0800
Subject: [PATCH v2] Fix ANALYZE reporting after imported foreign-table stats
Commit 28972b6fc added the ImportForeignStatistics FDW callback, which
lets ANALYZE import remote statistics instead of sampling a foreign table.
When that path succeeds, analyze_rel() skips do_analyze_rel(). That also
skips pgstat_report_analyze(), so cumulative ANALYZE stats such as
analyze_count, last_analyze_time, and live_tuples are not updated even
though ANALYZE succeeded.
Fix by having postgres_fdw report ANALYZE to the cumulative stats system
after a successful statistics import. The imported live-row estimate comes
from the remote reltuples value. Since imported relation stats do not
include a dead-tuple estimate, report zero dead tuples for this path.
Add regression coverage for postgres_fdw stats import to verify that a
successful imported ANALYZE updates live_tuples and analyze_count.
Author: Chao Li <[email protected]>
Reported-by: Chao Li <[email protected]>
Reviewed-by: Etsuro Fujita <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
.../postgres_fdw/expected/postgres_fdw.out | 40 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 17 ++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 10 +++++
src/backend/commands/analyze.c | 3 +-
4 files changed, 69 insertions(+), 1 deletion(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0805c56cb1b..5ebae1cedc2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12973,9 +12973,29 @@ ALTER FOREIGN TABLE simport_ftable OPTIONS (ADD restore_stats 'true');
ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- remote table "public.simport_table" has no relation statistics to import
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
+ pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_analyze_count
+-------------------------+-------------------------+---------------------------
+ 0 | 0 | 1
+(1 row)
+
ALTER TABLE simport_table ALTER COLUMN c1 SET STATISTICS 0;
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS 0;
INSERT INTO simport_table VALUES (1, 'foo'), (1, 'foo'), (2, 'bar'), (2, 'bar');
@@ -12988,9 +13008,29 @@ ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- no attribute statistics found for column "c2" of remote table "public.simport_table"
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
+ pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_analyze_count
+-------------------------+-------------------------+---------------------------
+ 4 | 0 | 1
+(1 row)
+
ANALYZE VERBOSE simport_ftable (c1); -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6fa45773c30..e5917eb7449 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -41,6 +41,7 @@
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
#include "parser/parsetree.h"
+#include "pgstat.h"
#include "postgres_fdw.h"
#include "statistics/statistics.h"
#include "storage/latch.h"
@@ -52,6 +53,7 @@
#include "utils/rel.h"
#include "utils/sampling.h"
#include "utils/selfuncs.h"
+#include "utils/timestamp.h"
PG_MODULE_MAGIC_EXT(
.name = "postgres_fdw",
@@ -336,6 +338,8 @@ typedef struct
PGresult *rel;
PGresult *att;
int server_version_num;
+ double livetuples;
+ double deadtuples;
} RemoteStatsResults;
/* Column order in relation stats query */
@@ -5597,6 +5601,7 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
RemoteStatsResults remstats = {.rel = NULL, .att = NULL};
RemoteAttributeMapping *remattrmap = NULL;
int attrcnt = 0;
+ TimestampTz starttime = 0;
bool restore_stats = false;
bool ok = false;
ListCell *lc;
@@ -5656,6 +5661,8 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
(errmsg("importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ starttime = GetCurrentTimestamp();
+
ok = fetch_remote_statistics(relation, va_cols,
table, schemaname, relname,
&attrcnt, &remattrmap, &remstats);
@@ -5665,9 +5672,15 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
attrcnt, remattrmap, &remstats);
if (ok)
+ {
+ pgstat_report_analyze(relation,
+ remstats.livetuples, remstats.deadtuples,
+ (va_cols == NIL), starttime);
+
ereport(elevel,
(errmsg("finished importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ }
PQclear(remstats.rel);
PQclear(remstats.att);
@@ -5807,6 +5820,10 @@ fetch_remote_statistics(Relation relation,
}
}
+ /* We assume that we have no dead tuple. */
+ remstats->deadtuples = 0.0;
+ remstats->livetuples = reltuples;
+
ok = true;
fetch_cleanup:
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8162c5496bf..e868da00ace 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4587,7 +4587,12 @@ ANALYZE simport_ftable; -- should fail
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
ALTER TABLE simport_table ALTER COLUMN c1 SET STATISTICS 0;
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS 0;
@@ -4604,7 +4609,12 @@ ANALYZE simport_ftable; -- should fail
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable (c1); -- should work
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index f66e80b757c..2edd88087f5 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -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)
{
bool ok = false;
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-03 09:10 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-04 13:23 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
@ 2026-07-06 04:36 ` Etsuro Fujita <[email protected]>
2026-07-06 14:51 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Etsuro Fujita @ 2026-07-06 04:36 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
Hi Chao,
On Sat, Jul 4, 2026 at 10:24 PM Chao Li <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-03 09:10 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-04 13:23 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-06 04:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
@ 2026-07-06 14:51 ` Chao Li <[email protected]>
2026-07-07 09:50 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Chao Li @ 2026-07-06 14:51 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
> On Jul 5, 2026, at 21:36, Etsuro Fujita <[email protected]> wrote:
>
> Hi Chao,
>
> On Sat, Jul 4, 2026 at 10:24 PM Chao Li <[email protected]> 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:
[application/octet-stream] v3-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch (8.2K, ../../[email protected]/2-v3-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch)
download | inline diff:
From 283c4acbe68520153f9c91b4d2fc19dea5ae3267 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Fri, 12 Jun 2026 11:20:19 +0800
Subject: [PATCH v3] Fix ANALYZE reporting after imported foreign-table stats
Commit 28972b6fc added the ImportForeignStatistics FDW callback, which
lets ANALYZE import remote statistics instead of sampling a foreign table.
When that path succeeds, analyze_rel() skips do_analyze_rel(). That also
skips pgstat_report_analyze(), so cumulative ANALYZE stats such as
analyze_count, last_analyze_time, and live_tuples are not updated even
though ANALYZE succeeded.
Fix by having postgres_fdw report ANALYZE to the cumulative stats system
after a successful statistics import. The imported live-row estimate comes
from the remote reltuples value. Since imported relation stats do not
include a dead-tuple estimate, report zero dead tuples for this path.
Add regression coverage for postgres_fdw stats import to verify that a
successful imported ANALYZE updates live_tuples and analyze_count.
Author: Chao Li <[email protected]>
Reported-by: Chao Li <[email protected]>
Reviewed-by: Etsuro Fujita <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
.../postgres_fdw/expected/postgres_fdw.out | 40 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 17 ++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 10 +++++
3 files changed, 67 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0805c56cb1b..5ebae1cedc2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12973,9 +12973,29 @@ ALTER FOREIGN TABLE simport_ftable OPTIONS (ADD restore_stats 'true');
ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- remote table "public.simport_table" has no relation statistics to import
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
+ pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_analyze_count
+-------------------------+-------------------------+---------------------------
+ 0 | 0 | 1
+(1 row)
+
ALTER TABLE simport_table ALTER COLUMN c1 SET STATISTICS 0;
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS 0;
INSERT INTO simport_table VALUES (1, 'foo'), (1, 'foo'), (2, 'bar'), (2, 'bar');
@@ -12988,9 +13008,29 @@ ANALYZE simport_ftable; -- should fail
WARNING: could not import statistics for foreign table "public.simport_ftable" --- no attribute statistics found for column "c2" of remote table "public.simport_table"
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
ANALYZE VERBOSE simport_ftable; -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
+ pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_analyze_count
+-------------------------+-------------------------+---------------------------
+ 4 | 0 | 1
+(1 row)
+
ANALYZE VERBOSE simport_ftable (c1); -- should work
INFO: importing statistics for foreign table "public.simport_ftable"
INFO: finished importing statistics for foreign table "public.simport_ftable"
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6fa45773c30..e5917eb7449 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -41,6 +41,7 @@
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
#include "parser/parsetree.h"
+#include "pgstat.h"
#include "postgres_fdw.h"
#include "statistics/statistics.h"
#include "storage/latch.h"
@@ -52,6 +53,7 @@
#include "utils/rel.h"
#include "utils/sampling.h"
#include "utils/selfuncs.h"
+#include "utils/timestamp.h"
PG_MODULE_MAGIC_EXT(
.name = "postgres_fdw",
@@ -336,6 +338,8 @@ typedef struct
PGresult *rel;
PGresult *att;
int server_version_num;
+ double livetuples;
+ double deadtuples;
} RemoteStatsResults;
/* Column order in relation stats query */
@@ -5597,6 +5601,7 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
RemoteStatsResults remstats = {.rel = NULL, .att = NULL};
RemoteAttributeMapping *remattrmap = NULL;
int attrcnt = 0;
+ TimestampTz starttime = 0;
bool restore_stats = false;
bool ok = false;
ListCell *lc;
@@ -5656,6 +5661,8 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
(errmsg("importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ starttime = GetCurrentTimestamp();
+
ok = fetch_remote_statistics(relation, va_cols,
table, schemaname, relname,
&attrcnt, &remattrmap, &remstats);
@@ -5665,9 +5672,15 @@ postgresImportForeignStatistics(Relation relation, List *va_cols, int elevel)
attrcnt, remattrmap, &remstats);
if (ok)
+ {
+ pgstat_report_analyze(relation,
+ remstats.livetuples, remstats.deadtuples,
+ (va_cols == NIL), starttime);
+
ereport(elevel,
(errmsg("finished importing statistics for foreign table \"%s.%s\"",
schemaname, relname)));
+ }
PQclear(remstats.rel);
PQclear(remstats.att);
@@ -5807,6 +5820,10 @@ fetch_remote_statistics(Relation relation,
}
}
+ /* We assume that we have no dead tuple. */
+ remstats->deadtuples = 0.0;
+ remstats->livetuples = reltuples;
+
ok = true;
fetch_cleanup:
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8162c5496bf..e868da00ace 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4587,7 +4587,12 @@ ANALYZE simport_ftable; -- should fail
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
ALTER TABLE simport_table ALTER COLUMN c1 SET STATISTICS 0;
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS 0;
@@ -4604,7 +4609,12 @@ ANALYZE simport_ftable; -- should fail
ALTER TABLE simport_table ALTER COLUMN c2 SET STATISTICS DEFAULT;
ANALYZE simport_table;
+SELECT pg_stat_reset_single_table_counters('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable; -- should work
+SELECT pg_stat_force_next_flush();
+SELECT pg_stat_get_live_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_dead_tuples('public.simport_ftable'::regclass),
+ pg_stat_get_analyze_count('public.simport_ftable'::regclass);
ANALYZE VERBOSE simport_ftable (c1); -- should work
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-03 09:10 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-04 13:23 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-06 04:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-06 14:51 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
@ 2026-07-07 09:50 ` Etsuro Fujita <[email protected]>
2026-07-07 14:22 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Etsuro Fujita @ 2026-07-07 09:50 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
Hi Chao,
On Mon, Jul 6, 2026 at 11:52 PM Chao Li <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: postgres_fdw: fix cumulative stats after imported foreign-table stats
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-03 09:10 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-04 13:23 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-06 04:36 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
2026-07-06 14:51 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-07-07 09:50 ` Re: postgres_fdw: fix cumulative stats after imported foreign-table stats Etsuro Fujita <[email protected]>
@ 2026-07-07 14:22 ` Chao Li <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Chao Li @ 2026-07-07 14:22 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Corey Huinker <[email protected]>
> On Jul 7, 2026, at 02:50, Etsuro Fujita <[email protected]> wrote:
>
> Hi Chao,
>
> On Mon, Jul 6, 2026 at 11:52 PM Chao Li <[email protected]> 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/
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-07-07 14:22 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-12 03:48 postgres_fdw: fix cumulative stats after imported foreign-table stats Chao Li <[email protected]>
2026-06-13 09:32 ` Etsuro Fujita <[email protected]>
2026-06-15 07:36 ` Chao Li <[email protected]>
2026-06-15 09:31 ` Etsuro Fujita <[email protected]>
2026-07-01 14:54 ` Heikki Linnakangas <[email protected]>
2026-07-01 22:57 ` Etsuro Fujita <[email protected]>
2026-07-03 09:10 ` Etsuro Fujita <[email protected]>
2026-07-04 13:23 ` Chao Li <[email protected]>
2026-07-06 04:36 ` Etsuro Fujita <[email protected]>
2026-07-06 14:51 ` Chao Li <[email protected]>
2026-07-07 09:50 ` Etsuro Fujita <[email protected]>
2026-07-07 14:22 ` Chao Li <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox