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 Cell: +380 96 915 9551 Email: Stepan_Yankevych@epam.com
Lviv, Ukraine 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.