Re: Add semi-join pushdown to postgres_fdw - Mailing list pgsql-hackers

Ashutosh Bapat писал 2022-08-29 17:12:
> Hi Alexander,
> Thanks for working on this. It's great to see FDW join pushdown scope
> being expanded to more complex cases.
> 
> I am still figuring out the implementation. It's been a while I have
> looked at join push down code.
> 
> But following change strikes me odd
>  -- subquery using immutable function (can be sent to remote)
>  PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3
> IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) =
> '1970-01-17'::date) ORDER BY c1;
>  EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20);
> -                                                      QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------
> - Sort
> +
> 
> QUERY PLAN
>
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> + Foreign Scan
>     Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
> -   Sort Key: t1.c1
> -   ->  Nested Loop Semi Join
> -         Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, 
> t1.c8
> -         Join Filter: (t1.c3 = t2.c3)
> -         ->  Foreign Scan on public.ft1 t1
> -               Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, 
> t1.c7, t1.c8
> -               Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8
> FROM "S 1"."T 1" WHERE (("C 1" < 20))
> -         ->  Materialize
> -               Output: t2.c3
> -               ->  Foreign Scan on public.ft2 t2
> -                     Output: t2.c3
> -                     Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE
> (("C 1" > 10)) AND ((date(c5) = '1970-01-17'::date))
> -(14 rows)
> +   Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
> +   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
> r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 20)) AND (EXISTS
> (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND
> ((date(r3.c5) = '1970-01-17'::date)) AND ((r1.c3 = r3.c3)))) ORDER BY
> r1."C 1" ASC NULLS LAST
> +(4 rows)
> 
> date_in              | s           |        1 | [0:0]={cstring}
> date_in which will be used to cast a test to date is not immutable. So
> the query should't be pushed down. May not be a problem with your
> patch. Can you please check?

Hi.

It is not related to my change and works as expected. As I see, we have 
expression FuncExprdate(oid = 2029, args=Var ) = Const(type date)
(date(r3.c5) = '1970-01-17'::date).
Function is

# select proname, provolatile from pg_proc where oid=2029;
  proname | provolatile
---------+-------------
  date    | i

So it's shippable.
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional