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.

Reply via email to