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