Re: Getting sorted data from foreign server

> On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>>
>> In the interest of full disclosure, I asked Ashutosh to work on this
>> patch and have discussed the design with him several times. I believe
>> that this is a good direction for PostgreSQL to be going. It's
>> trivially easy right now to write a query against an FDW that performs
>> needlessly easy, because a join, or a sort, or an aggregate is
>> performed on the local server rather than the remote one. I, and
>> EnterpriseDB, want that to get fixed. However, we also want it to get
>> fixed in the best possible way, and not to do anything unless there is
>> consensus on it. So, if anyone has opinions on this topic, please
>> jump in.
>>
>

Are we planning to push sorting on foreign server with parametrized
foreign path?

We might get a parametrized path when local table is small enough and
foreign table is bigger, like, for this query
SELECT big_ft.x FROM big_ft, small_lt WHERE big_ft.x = small_lt.y;
we might end up getting parametrized foreign path with remote query
like:
SELECT big_ft.x FROM big_ft WHERE big_ft.x = $1;

And with this, if we have an ORDER BY clause like "ORDER BY big_ft.x"
we will get remote query like:
SELECT big_ft.x FROM big_ft WHERE big_ft.x = $1 ORDER BY big_ft.x;

Is this possible???

If yes, then don't we need to sort again on local server?

Assume each row on local server matches three rows from foreign table,
then for each $1, we will have 3 rows returned from the foreign server,
of-course sorted. But then all these fetched rows in batch of 3, needs
to be re-sorted on local server, isn't it?
If yes, this will be much more costly than fetching unsorted rows and
sorting then locally only once.

Or am I missing something here?

--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company