Thread

1. fdw: foreign partition and aggregate function

   Jérémy Lal <kapouer@melix.org> — 2026-05-09T09:48:13Z

   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
   

2. Re: fdw: foreign partition and aggregate function

   Ron Johnson <ronljohnsonjr@gmail.com> — 2026-05-10T02:51:15Z

   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!
   

3. Re: fdw: foreign partition and aggregate function

   Jérémy Lal <kapouer@melix.org> — 2026-05-12T08:09:46Z

   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
   
   >