BUG #17162: order by clause is not pushed down to foreign scans when a WHERE clause is given in query

The following bug has been logged on the website:

Bug reference: 17162
Logged by: Chetan Suttraway
Email address: chetansuttraway(at)gmail(dot)com
PostgreSQL version: 13.0
Operating system: windows
Description:

I am assuming that order by clause needs to be pushed to foreign scans
irrespective whether the parent node is append, sort or any parallel node.

1. setup tables using inheritance wherein the derived tables are foreign
tables
postgres=# create table base(a int, b int , c int);
CREATE TABLE
postgres=# create table src1(a int, b int, c int);
CREATE TABLE
postgres=# create table src2(a int, b int, c int);
CREATE TABLE
postgres=# insert into src1 values(generate_series(1,1000000),
generate_series(1, 1000000), generate_series(1, 1000000));
INSERT 0 1000000
postgres=# insert into src2 values(generate_series(1,1000000),
generate_series(1, 1000000), generate_series(1, 1000000));
INSERT 0 1000000
postgres=# create foreign table derived_1() inherits(base) SERVER myserver
OPTIONS ( table_name 'src1') ;
CREATE FOREIGN TABLE
postgres=# create foreign table derived_2() inherits(base) SERVER myserver
OPTIONS ( table_name 'src2') ;
CREATE FOREIGN TABLE

2. Check for ORDER BY without WHERE clause.
In this case, you can see the ORDER BY clause being pushed into the remote
sql.
postgres=# explain verbose select a from base order by a;
QUERY PLAN
----------------------------------------------------------------------------------------
Merge Append (cost=200.03..486.86 rows=5851 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
-> Foreign Scan on public.derived_1 base_2 (cost=100.00..205.60
rows=2925 width=4)
Output: base_2.a
Remote SQL: SELECT a FROM public.src1 ORDER BY a ASC NULLS LAST
-> Foreign Scan on public.derived_2 base_3 (cost=100.00..205.60
rows=2925 width=4)
Output: base_3.a
Remote SQL: SELECT a FROM public.src2 ORDER BY a ASC NULLS LAST
(13 rows)

3. check with ORDER BY and WHERE clause.
In this plan, you can see that the order by clause is not pushed into remote
sql.

postgres=# explain verbose select a from base where a>100 and a <200000
order by a;
QUERY PLAN
--------------------------------------------------------------------------------------------
Sort (cost=309.27..309.35 rows=31 width=4)
Output: base.a
Sort Key: base.a
-> Append (cost=0.00..308.50 rows=31 width=4)
-> 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..154.18
rows=15 width=4)
Output: base_2.a
Remote SQL: SELECT a FROM public.src1 WHERE ((a > 100)) AND
((a < 200000))
-> Foreign Scan on public.derived_2 base_3 (cost=100.00..154.18
rows=15 width=4)
Output: base_3.a
Remote SQL: SELECT a FROM public.src2 WHERE ((a > 100)) AND
((a < 200000))
(13 rows)

Wouldn't it be good to push order by clause even when where clause is given
in query?