Horton

Thread

  1. postgres_fdw: fix cumulative stats after imported foreign-table stats

    Chao Li <li.evan.chao@gmail.com> — 2026-06-12T03:48:39Z

    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/

  2. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-13T09:32:33Z

    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

  3. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Chao Li <li.evan.chao@gmail.com> — 2026-06-15T07:36:32Z

    
> 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/

  4. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-06-15T09:31:02Z

    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

  5. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Heikki Linnakangas <hlinnaka@iki.fi> — 2026-07-01T14:54:55Z

    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

  6. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-07-01T22:57:51Z

    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

  7. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-07-03T09:10:36Z

    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

  8. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Chao Li <li.evan.chao@gmail.com> — 2026-07-04T13:23:45Z

    
> 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/

  9. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-07-06T04:36:08Z

    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

  10. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Chao Li <li.evan.chao@gmail.com> — 2026-07-06T14:51:36Z

    
> 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/

  11. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2026-07-07T09:50:41Z

    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

  12. Re: postgres_fdw: fix cumulative stats after imported foreign-table stats

    Chao Li <li.evan.chao@gmail.com> — 2026-07-07T14:22:45Z

    
> 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/