Hi All! We are using such feature as Foreign table as partition in PG 13 under CentOS Here is our table CREATE TABLE dwh.l1_snapshot ( l1_snapshot_id int8 NOT NULL DEFAULT nextval('sq_l1_snapshot_id'::regclass), start_date_id int4 NULL, ... ... ... dataset_id int4 NULL, -- ETL needs transaction_time timestamp NULL ) PARTITION BY RANGE (start_date_id);
We have several partitions locally and one partition for storing historical data as foreign table which is stored on another PG13 When I run following query . Partition pruning redirect query to that foreign table select count(1) from dwh.l1_snapshot ls where start_date_id = 20201109; I see remote SQL as following SELECT NULL FROM dwh.l1_snapshot_tail2 WHERE ((start_date_id = 20201109)). It transfers vie network hundred million records in our case When I query directly partition (almost the same what partition pruning does) I see another remote sql select count(1) from partitions.l1_snapshot_tail2 ls where start_date_id = 20201109; And remote sql is SELECT count(1) FROM dwh.l1_snapshot_tail2 WHERE ((start_date_id = 20201109)); So in case querying foreign table we see aggregation is propagated to remote host (Like driving_site in oracle) But in the first case with partition pruning the aggregation is not propagated to remote host. And of course different performance 22 sec vs 75sec That would great to have the same behavior in both cases (pushing aggregation to remote side). It should be possible at least for simple aggregation (without distinct etc) Thanks! Stepan Yankevych Office: +380 322 424 642xx58840<tel:+380%20322%20424%20642;ext=x58840> Cell: +380 96 915 9551<tel:+380%2096%20915%209551> Email: stepan_yankev...@epam.com<mailto:stepan_yankev...@epam.com> Lviv, Ukraine epam.com<http://www.epam.com> CONFIDENTIALITY CAUTION AND DISCLAIMER This message is intended only for the use of the individual(s) or entity(ies) to which it is addressed and contains information that is legally privileged and confidential. If you are not the intended recipient, or the person responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. All unintended recipients are obliged to delete this message and destroy any printed copies.