Subject: Re: Add semi-join pushdown to postgres_fdw

From: Pavel Luzanov <[email-masked]>
Date: Fri, 9 Feb 2024 23:08:11 +0300

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

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 9 Feb 2024 22:27:27 +0200

> postgres@demo_postgres_fdw(17.0)=# \det aircrafts|seats

Thank you, Pavel. I'm looking into this.

------
Regards,
Alexander Korotkov

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 12 Feb 2024 04:27:14 +0200

Hi, Pavel!

> But optimization not used for NOT EXISTS:

Right, anti-joins are not supported yet.

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

This is a costing issue.  Optimization worlds for me when set
"use_remote_estimate = true" for the server;

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

I haven't managed to reproduce this yet.  Could you give more details:
machine, OS, compile options, backtrace?

------
Regards,
Alexander Korotkov

---

From: Pavel Luzanov <[email-masked]>
Date: Mon, 12 Feb 2024 11:50:34 +0300

Hi, Alexander!

> machine, OS, compile options, backtrace?

We already had off-list conversation with Alexander Pyhalov.

Yesterday, after rebuilding the server, I can't reproduce the error.
I have good reason to believe that the problem was on my side.
On Friday, I tested another patch and built the server several times.
Most likely, I just made a mistake during the server build.

Sorry for the noise.