Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption

On Thu, 30 May 2024 at 20:56, Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> Hi Japin,
>
> On Tue, May 28, 2024 at 11:20 PM Japin Li <japinli(at)hotmail(dot)com> wrote:
>> Sorry for the late reply. I'm not familiar with this. However, after some
>> tests, the COLLATE may influence the result; see the example below.
>
> That is true, but my point is that we do not need to worry about
> things like that, in *add_foreign_final_paths()*. I will explain the
> reason why below.
>
>> [local]:535513 postgres=# EXPLAIN (verbose) SELECT * FROM ft01 ORDER BY a COLLATE "en_US" FETCH FIRST 2 ROWS WITH TIES;
>> QUERY PLAN
>> -----------------------------------------------------------------------------------
>> Limit (cost=446.26..446.27 rows=2 width=64)
>> Output: a, ((a)::text)
>> -> Sort (cost=446.26..449.92 rows=1462 width=64)
>> Output: a, ((a)::text)
>> Sort Key: ft01.a COLLATE "en_US"
>> -> Foreign Scan on public.ft01 (cost=100.00..431.64 rows=1462 width=64)
>> Output: a, a
>> Remote SQL: SELECT a FROM public.t01
>> (8 rows)
>
>> [local]:535513 postgres=# EXPLAIN (verbose) SELECT * FROM ft01 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
>> QUERY PLAN
>> ----------------------------------------------------------------------------------
>> Foreign Scan on public.ft01 (cost=100.00..100.44 rows=2 width=32)
>> Output: a
>> Remote SQL: SELECT a FROM public.t01 ORDER BY a ASC NULLS LAST LIMIT 2::bigint
>> (3 rows)
>
> First of all let me briefly explain about how postgres_fdw considers
> pushing down the operations. The core allows it to do so
> step-by-step: first ORDER BY and then LIMIT (FETCH in this case).
> First, when called for ORDER BY, it executes
> add_foreign_ordered_paths() to consider the pushability of ORDER BY.
> Then, when called for FETCH, 1) if ORDER BY had been determined to be
> safe to push down in the first step, it executes
> add_foreign_final_paths() to consider the pushability of LIMIT; 2) if
> not, it just gives up on pushing down LIMIT (without executing that
> function), because if we can't push ORDER BY, we can't LIMIT either!
> I think while the former example would correspond to #2, the latter
> example would correspond to #1.
>
> The reason is: if getting to add_foreign_final_paths(), it means that
> postgres_fdw determined in the first step that ORDER BY is safe to
> push down, so we no longer need to worry that the clause might produce
> a different sort order and/or a different set of ties in the remote
> side.
>

Thanks for the explanation!

I think I understand what you mean. We can ensure that the ORDER BY can be
safely pushed down if we are in add_foreign_final_paths(). The reason the
FETCH clause cannot be pushed down is only because the remote may not
support it, right?

--
Regards,
Japin Li