| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>, Corey Huinker <corey(dot)huinker(at)gmail(dot)com> |
| Subject: | postgres_fdw: fix cumulative stats after imported foreign-table stats |
| Date: | 2026-06-12 03:48:39 |
| Message-ID: | 6ED81190-B398-44C9-A1E9-8EFE4ED183AF@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Fix-ANALYZE-reporting-after-imported-foreign-tabl.patch | application/octet-stream | 9.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Amit Langote | 2026-06-12 04:02:44 | Re: Fast-path FK checks reject valid inserts for domain-typed FK columns |
| Previous Message | Tatsuo Ishii | 2026-06-12 03:40:50 | Re: Row pattern recognition |