fdw: foreign partition and aggregate function
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
On Sat, May 9, 2026 at 5:49 AM Jérémy Lal <kapouer@melix.org> wrote:
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;
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!
Le dim. 10 mai 2026 à 04:51, Ron Johnson <ronljohnsonjr@gmail.com> a écrit :
On Sat, May 9, 2026 at 5:49 AM Jérémy Lal <kapouer@melix.org> wrote:
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;
pg_stat_all_tables will tell you if remote parts_s1.mytable_1 was really
analyzed.
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