Thanks Tom for your explanation.
I agree this isn't a bug.
After some investigation, found that foreign scan with sort keys has a higher cost
than foreign scan without sort keys.
So both the plans are explored and the planner rightly chooses the cheaper plan.
My intention is to get sorted data from foreign table scans every time
when the query has order by clause, irrespective of the cost.
With some tweaking, I was able to generate below plan where order by
and where clause are part remote sql:
postgres=# explain verbose select a from base where a>100 and a <200000
postgres-# order by a;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Merge Append (cost=200.03..330.34 rows=31 width=4)
Sort Key: base.a
-> Sort (cost=0.01..0.02 rows=1 width=4)
Output: base_1.a
Sort Key: base_1.a
-> Seq Scan on public.base base_1 (cost=0.00..0.00 rows=1 width=4)
Output: base_1.a
Filter: ((base_1.a > 100) AND (base_1.a < 200000))
-> Foreign Scan on public.derived_1 base_2 (cost=100.00..164.95 rows=15 width=4)
Output: base_2.a
Remote SQL: SELECT a FROM public.src1 WHERE ((a > 100)) AND ((a < 200000)) ORDER BY a ASC NULLS LAST
-> Foreign Scan on public.derived_2 base_3 (cost=100.00..164.95 rows=15 width=4)
Output: base_3.a
Remote SQL: SELECT a FROM public.src2 WHERE ((a > 100)) AND ((a < 200000)) ORDER BY a ASC NULLS LAST
(14 rows)
Thanks and Regards,
Chetan