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

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

On Thu, Aug 26, 2021 at 7:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > Wouldn't it be good to push order by clause even when where clause is
> given
> > in query?
>
> Maybe, but you haven't demonstrated it. In the example you show,
> the query plan still requires a local sort, so pre-sorting the
> individual inputs to that would just add cycles without accomplishing
> anything.
>
> In any case, this is not a bug.
>
> regards, tom lane
>

--
--
Regards,
Chetan

+919665562626