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

Hi Japin,

On Tue, May 28, 2024 at 11:20 PM Japin Li <japinli@hotmail.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!

Best regards,
Etsuro Fujita