public inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Jérémy Lal <kapouer@melix.org>
To: pgsql-general@lists.postgresql.org
Subject: fdw: foreign partition and aggregate function
Date: Sat, 9 May 2026 11:48:13 +0200
Message-ID: <CAJxTCxyUtQuESS+Nk3+HRwjirZrs9A6Ypis=XfPcVV0=siJ0Qg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: kapouer@melix.org, pgsql-general@lists.postgresql.org
  Subject: Re: fdw: foreign partition and aggregate function
  In-Reply-To: <CAJxTCxyUtQuESS+Nk3+HRwjirZrs9A6Ypis=XfPcVV0=siJ0Qg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox