Re: estimation problems for DISTINCT ON with FDW - Mailing list pgsql-hackers

From Etsuro Fujita
Subject Re: estimation problems for DISTINCT ON with FDW
Date
Msg-id CAPmGK17c8AnwPGvEHtKA-BW2MQFhD-MNwe=NE8gf5jKJ5bYdCA@mail.gmail.com
Whole thread Raw
In response to Re: estimation problems for DISTINCT ON with FDW  (Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>)
List pgsql-hackers
On Mon, Jun 29, 2020 at 7:02 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
> > It doesn't cost out the plan of pushing the DISTINCT ON down to the foreign side, which is probably the best way to
runthe query.  I guess it makes sense that FDW machinery in general doesn't want to try to push a PostgreSQL specific
construct.
>
> I think you are right, the DISTINCT operation is not being pushed to
> remote(I may be wrong here. just for info - I looked at remote SQL
> from explain(verbose) on the query to find this out) and so is for
> estimates.

I think you are right.

> But when fdw is
> used for non-sharded configurations such as just to get existing data
> from another remote postgres server, oracle, hadoop or  some other
> remote database engines where DISTINCT operation is supported, it's
> good to push that to remote for both explains/estimates as well as in
> the actual queries itself, to reduce data transferred from remote
> database server to local postgres database server.

I think so too.  And I think we could do so using the upper-planner
pathification (ie, GetForeignUpperPaths() with UPPERREL_DISTINCT in
create_distinct_paths()).  It's on my long-term TODO list to implement
that in postgres_fdw.

Best regards,
Etsuro Fujita



pgsql-hackers by date:

Previous
From: Georgios
Date:
Subject: Re: Include access method in listTables output
Next
From: Asif Rehman
Date:
Subject: Re: +(pg_lsn, int8) and -(pg_lsn, int8) operators