Re: Add semi-join pushdown to postgres_fdw

Hello,

While playing with this feature I found the following.

Two foreign tables:
postgres@demo_postgres_fdw(17.0)=# \det aircrafts|seats      List of foreign tables Schema |   Table   |   Server    
--------+-----------+------------- public | aircrafts | demo_server public | seats     | demo_server
(2 rows)


This query uses optimization:

postgres@demo_postgres_fdw(17.0)=# EXPLAIN (costs off, verbose) SELECT * 
FROM aircrafts a
WHERE a.aircraft_code = '320' AND EXISTS (  SELECT * FROM seats s WHERE s.aircraft_code = a.aircraft_code
);                                                                                                 QUERY PLAN                                                                                       >
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> Foreign Scan   Output: a.aircraft_code, a.model, a.range   Relations: (public.aircrafts a) SEMI JOIN (public.seats s)   Remote SQL: SELECT r1.aircraft_code, r1.model, r1.range FROM bookings.aircrafts r1 WHERE ((r1.aircraft_code = '320')) AND EXISTS (SELECT NULL FROM bookings.seats r2 WHERE ((r2.aircraft_code =>
(4 rows)


But optimization not used for NOT EXISTS:

postgres@demo_postgres_fdw(17.0)=# EXPLAIN (costs off, verbose) SELECT * 
FROM aircrafts a
WHERE a.aircraft_code = '320' AND NOT EXISTS (  SELECT * FROM seats s WHERE s.aircraft_code = a.aircraft_code
);                                                   QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------- Nested Loop Anti Join   Output: a.aircraft_code, a.model, a.range   ->  Foreign Scan on public.aircrafts a         Output: a.aircraft_code, a.model, a.range         Remote SQL: SELECT aircraft_code, model, range FROM bookings.aircrafts WHERE ((aircraft_code = '320'))   ->  Materialize         Output: s.aircraft_code         ->  Foreign Scan on public.seats s               Output: s.aircraft_code               Remote SQL: SELECT aircraft_code FROM bookings.seats WHERE ((aircraft_code = '320'))
(10 rows)

Also, optimization not used after deleting first condition (a.aircraft_code = '320'):

postgres@demo_postgres_fdw(17.0)=# EXPLAIN (costs off, verbose) SELECT * 
FROM aircrafts a
WHERE EXISTS (  SELECT * FROM seats s WHERE s.aircraft_code = a.aircraft_code
);                                   QUERY PLAN                                   
-------------------------------------------------------------------------------- Hash Join   Output: a.aircraft_code, a.model, a.range   Inner Unique: true   Hash Cond: (a.aircraft_code = s.aircraft_code)   ->  Foreign Scan on public.aircrafts a         Output: a.aircraft_code, a.model, a.range         Remote SQL: SELECT aircraft_code, model, range FROM bookings.aircrafts   ->  Hash         Output: s.aircraft_code         ->  HashAggregate               Output: s.aircraft_code               Group Key: s.aircraft_code               ->  Foreign Scan on public.seats s                     Output: s.aircraft_code                     Remote SQL: SELECT aircraft_code FROM bookings.seats
(15 rows)


But the worst thing is that replacing AND with OR causes breaking session and server restart:

postgres@demo_postgres_fdw(17.0)=# EXPLAIN (costs off, verbose) SELECT * 
FROM aircrafts a
WHERE a.aircraft_code = '320' OR EXISTS (  SELECT * FROM seats s WHERE s.aircraft_code = a.aircraft_code
);
server closed the connection unexpectedly	This probably means the server terminated abnormally	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.

-- 
Pavel Luzanov
Postgres Professional: https://postgrespro.com