Re: Getting sorted data from foreign server

On Tue, Oct 13, 2015 at 1:48 PM, Jeevan Chalke <
jeevan(dot)chalke(at)enterprisedb(dot)com> wrote:

>
> 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?
>
>
Thanks a lot for the catch. Per add_path() prologue
368 * ... First, we treat all parameterized paths
369 * as having NIL pathkeys, so that they cannot win comparisons on
the
370 * basis of sort order.

So, anyway those paths were getting eliminated by add_path().

I will take care of this one in the next version of patch.

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

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company