Foreign table as partition - Non optimal aggregation plan

[Stepan Yankevych <Stepan_Yankevych@xxxxxxxx>]

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@xxxxxxxx

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.