MIME-Version: 1.0
From: =?UTF-8?B?SsOpcsOpbXkgTGFs?= <kapouer@melix.org>
Date: Sat, 9 May 2026 11:48:13 +0200
Message-ID: 
 <CAJxTCxyUtQuESS+Nk3+HRwjirZrs9A6Ypis=XfPcVV0=siJ0Qg@mail.gmail.com>
Subject: fdw: foreign partition and aggregate function
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009939c006515f6b27"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJxTCxyUtQuESS%2BNk3%2BHRwjirZrs9A6Ypis%3DXfPcVV0%3DsiJ0Qg%40mail.gmail.com>
Precedence: bulk

--0000000000009939c006515f6b27
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

Using PostgreSQL on Debian 18.3-1.pgdg13+1

I've setup a partitioned table, with local and foreign partitions like this
Cl=C3=A9 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 =3D 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 =3D 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 =3D 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=C3=A9r=C3=A9my

--0000000000009939c006515f6b27
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi,<div><br></div><div>Using PostgreSQL o=
n Debian 18.3-1.pgdg13+1</div><div><br></div><div>I&#39;ve setup a partitio=
ned table, with local and foreign partitions like this</div>Cl=C3=A9 de par=
tition : LIST (part_id)<br>Partitions: foreign_parts_s1.mytable_1 FOR VALUE=
S IN (1), FOREIGN,<br><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 parts_=
s10.mytable_10 FOR VALUES IN (10), PARTITIONED,=C2=A0</div><div><br></div><=
div>and ran ANALYZE mytable;</div><div><br></div><div>Now when I query dire=
ctly the foreign table, the plan is what I expect</div><div>SELECT count(*)=
 FROM foreign_parts_s1.mytable_1 WHERE part_id =3D 1;<br>=C2=A0Foreign Scan=
<br>=C2=A0 =C2=A0Output: (count(*))<br>=C2=A0 =C2=A0Relations: Aggregate on=
 (foreign_parts_s1.mytable_1)<br>=C2=A0 =C2=A0Remote SQL: SELECT count(*) F=
ROM parts_s1.mytable_1<br><br></div><div>and is somewhat as fast as the sam=
e query on the remote server.</div><div><br></div>However, when I query the=
 parent table, the aggregate is not pushed down:<br>SELECT count(*) FROM my=
table WHERE part_id =3D 1;<br>Aggregate<br>Output: count(*)<br>-&gt; Foreig=
n Scan on foreign_parts_s1.mytable_1 mytable<br>Remote SQL: SELECT NULL FRO=
M parts_s1.mytable_1 WHERE ((part_id =3D 1))</div><div dir=3D"ltr"><br></di=
v><div>and it reads all the rows, so it&#39;s not a viable query.</div><div=
><br></div><div>I tried with various parameters like<br>enable_partitionwis=
e_aggregate</div><div>enable_partitionwise_join</div><div>also use_remote_e=
stimate on the server options.<br><br>Is it the expected behavior ? Did I m=
iss something ?</div><div><br></div><div>J=C3=A9r=C3=A9my<br></div>
</div>

--0000000000009939c006515f6b27--