Getting sorted data from foreign server

Hi All,
standard_qp_callback() sets root->query_pathkeys to pathkeys on which the
result of query_planner are expected be sorted upon (see the function for
more details). The patch checks if any prefix of these pathkeys can be
entirely evaluated using the foreign relation and at the foreign server
under consideration. If yes, it gets estimates of costs involved and adds
paths with those pathkeys. There can be multiple pathkeyless paths added
for a given base relation. For every such path one path with pathkeys is
added. If there is an index matching on the foreign server, getting the
data sorted from foreign server improves execution time as seen from the
results. The patch adds this functionality entirely in postgres_fdw.

For a postgres_fdw foreign table ft1(val int, val2 int), with the patch

EXPLAIN VERBOSE SELECT * FROM ft1 ORDER BY val; gives
QUERY PLAN
------------------------------------------------------------------------
Foreign Scan on public.ft1 (cost=100.29..6480.42 rows=100118 width=8)
Output: val, val2
Remote SQL: SELECT val, val2 FROM public.lt ORDER BY val ASC
(3 rows)

observe that the query sent to the foreign server has ORDER BY clause in
it. The test script attached has more examples of the same. The patch adds
a small test case.

Results
------------
Attached find the script used to measure the performance. The script
creates a foreign server and foreign table pointing to the local server and
local table resp. The test runs three different types of queries (simple
sort, group by, sorted result from inheritance hierarchy) multiple times
and calculates the average execution time for each query with and without
the patch. The performance is measured for foreign table (ft1 above)
populated with 100 rows (in-memory sorting) and with 100000 rows (external
sorting) resp. The output of the script with and without patch and with
different sizes of foreign table is attached here.

We can observe following
1. For large number of rows (when the memory is not enough to hold all the
data to be sorted) we see 20-25% reduction in the query execution time when
there is matching index on the foreign server.

2. For very small number of rows (when the memory is enough to hold all the
data to be sorted) there is not much performance gain and sometimes the
planner is not choosing the path with pathkeys for foreign scans.

3. In all the cases, the planning time increases owing to EXPLAIN queries
fired on the foreign server.

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