The following bug has been logged on the website:
Bug reference: 18627
Logged by: Mikaël Gourlaouen
Email address: gourlaouen.mikael@gmail.com
PostgreSQL version: 16.4
Operating system: Debian
Description:
Given the following schema and query:
```
CREATE TABLE origin (id text PRIMARY KEY, d_id text);
CREATE TABLE intermediary (id text PRIMARY KEY, version_id text NOT
NULL);
CREATE TABLE destination (id text NOT NULL, version_id text NOT NULL,
CONSTRAINT d_pk PRIMARY KEY(id, version_id));
EXPLAIN SELECT o.id
FROM origin o
LEFT JOIN intermediary i ON o.id = i.id
LEFT JOIN destination d ON d.id = o.d_id AND i.version_id = d.version_id;
```
Postgres 15 is able to nicely remove all the joins and gives a nice tidy
query plan:
```
Seq Scan on origin o (cost=0.00..16.50 rows=650 width=32)
```
Postgres 16 (and 17rc1) on the other hand are giving me this query plan
instead:
```
Hash Left Join (cost=24.62..42.84 rows=650 width=32)
Hash Cond: (o.id = i.id)
-> Seq Scan on origin o (cost=0.00..16.50 rows=650 width=64)
-> Hash (cost=16.50..16.50 rows=650 width=64)
-> Seq Scan on intermediary i (cost=0.00..16.50 rows=650
width=64)
```
As far as I understand, that join is not helpful in any away as no data from
the join affects the number of rows nor the data returned in each row.
