Subject: fdw: foreign partition and aggregate function

From: Jérémy Lal <[email-masked]>
Date: Sat, 9 May 2026 11:48:13 +0200

Hi,

Using PostgreSQL on Debian 18.3-1.pgdg13+1

I've setup a partitioned table, with local and foreign partitions like this
Clé de partition : LIST (part_id)
Partitions: foreign_parts_s1.mytable_1 FOR VALUES IN (1), FOREIGN,
            parts_s10.mytable_10 FOR VALUES IN (10), PARTITIONED,

and ran ANALYZE mytable;

Now when I query directly the foreign table, the plan is what I expect
SELECT count(*) FROM foreign_parts_s1.mytable_1 WHERE part_id = 1;
 Foreign Scan
   Output: (count(*))
   Relations: Aggregate on (foreign_parts_s1.mytable_1)
   Remote SQL: SELECT count(*) FROM parts_s1.mytable_1

and is somewhat as fast as the same query on the remote server.

However, when I query the parent table, the aggregate is not pushed down:
SELECT count(*) FROM mytable WHERE part_id = 1;
Aggregate
Output: count(*)
-> Foreign Scan on foreign_parts_s1.mytable_1 mytable
Remote SQL: SELECT NULL FROM parts_s1.mytable_1 WHERE ((part_id = 1))

and it reads all the rows, so it's not a viable query.

I tried with various parameters like
enable_partitionwise_aggregate
enable_partitionwise_join
also use_remote_estimate on the server options.

Is it the expected behavior ? Did I miss something ?

Jérémy

---

From: Ron Johnson <[email-masked]>
Date: Sat, 9 May 2026 22:51:15 -0400

>             parts_s10.mytable_10 FOR VALUES IN (10), PARTITIONED,

pg_stat_all_tables will tell you if remote parts_s1.mytable_1 was really
analyzed.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Jérémy Lal <[email-masked]>
Date: Tue, 12 May 2026 10:09:46 +0200

> pg_stat_all_tables will tell you if remote parts_s1.mytable_1 was really

Thanks, I searched into that direction.
pg_stat_all_tables doesn't have any stat regarding parts_s1.mytable_1:
    SELECT relname FROM pg_stat_all_tables WHERE relname LIKE '%mytable%';
only returns relname my_table, mytable_10

Anyway, analyze verbose reports nothing suspicious, with entry like
INFO:  analyzing "foreign_parts_s1.mytable_1"
INFO:  "mytable_1": table contains 6320 rows, 6320 rows in sample
INFO:  finished analyzing table "mydb.foreign_parts_s1.mytable_1"

mydb=# SELECT
schemaname, tablename, attname, inherited, n_distinct, most_common_vals
FROM pg_stats WHERE tablename = 'mytable' AND attname = 'part_id';
-[ RECORD 1
]-----+----------------------------------------------------------------------
schemaname        | public
tablename         | mytable
attname           | part_id
inherited         | t
n_distinct        | 1
most_common_vals  | {10}

most_common_vals only list local partition's list value.
That seems not okay.

To be honest, the partitions are themselves partitioned with another
column, so maybe it causes an issue.

Jérémy