public inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedfdw: foreign partition and aggregate function
3+ messages / 2 participants
[nested] [flat]
* fdw: foreign partition and aggregate function
@ 2026-05-09 09:48 Jérémy Lal <kapouer@melix.org>
0 siblings, 1 reply; 3+ messages in thread
From: Jérémy Lal @ 2026-05-09 09:48 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: fdw: foreign partition and aggregate function
@ 2026-05-10 02:51 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Jérémy Lal <kapouer@melix.org>
0 siblings, 1 reply; 3+ messages in thread
From: Ron Johnson @ 2026-05-10 02:51 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: fdw: foreign partition and aggregate function
@ 2026-05-12 08:09 Jérémy Lal <kapouer@melix.org>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jérémy Lal @ 2026-05-12 08:09 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-05-12 08:09 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-09 09:48 fdw: foreign partition and aggregate function Jérémy Lal <kapouer@melix.org>
2026-05-10 02:51 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-05-12 08:09 ` Jérémy Lal <kapouer@melix.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox