BUG #17964: Missed query planner optimization

The following bug has been logged on the website:

Bug reference:      17964
Logged by:          Mathias Kunter
Email address:      mathiaskunter@gmail.com
PostgreSQL version: 14.8
Operating system:   x86_64-pc-linux-gnu
Description:

In the example below, the query planner uses a sequential scan (query 1)
even though it could use an index scan (query 2).

CREATE TABLE table1 (id SERIAL NOT NULL, name VARCHAR, CONSTRAINT
table1_pkey PRIMARY KEY (id));
CREATE TABLE table2 (id SERIAL NOT NULL, name VARCHAR, CONSTRAINT
table2_pkey PRIMARY KEY (id));
CREATE TABLE table3 (id INTEGER NOT NULL);

INSERT INTO table1 (id, name) SELECT generate_series(1, 10000),
md5(random()::text);
INSERT INTO table2 (id, name) SELECT generate_series(1, 10000),
md5(random()::text);
INSERT INTO table3 (id) VALUES (1538),(8836),(5486),(3464),(2673);

ANALYZE;

EXPLAIN ANALYZE SELECT id, name FROM (SELECT id, name FROM table1 UNION
SELECT id, name FROM table2) AS q
WHERE id IN (SELECT id FROM table3);
---------------------------------------------------------------
Hash Semi Join  (cost=769.11..1227.17 rows=500 width=36) (actual
time=10.952..15.094 rows=10 loops=1)
  Hash Cond: (table1.id = table3.id)
  ->  HashAggregate  (cost=768.00..968.00 rows=20000 width=36) (actual
time=10.757..13.597 rows=20000 loops=1)
        Group Key: table1.id, table1.name
        Batches: 1  Memory Usage: 2577kB
        ->  Append  (cost=0.00..668.00 rows=20000 width=36) (actual
time=0.010..4.135 rows=20000 loops=1)
              ->  Seq Scan on table1  (cost=0.00..184.00 rows=10000
width=37) (actual time=0.009..1.288 rows=10000 loops=1)
              ->  Seq Scan on table2  (cost=0.00..184.00 rows=10000
width=37) (actual time=0.010..1.336 rows=10000 loops=1)
  ->  Hash  (cost=1.05..1.05 rows=5 width=4) (actual time=0.014..0.015
rows=5 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Seq Scan on table3  (cost=0.00..1.05 rows=5 width=4) (actual
time=0.009..0.010 rows=5 loops=1)
Planning Time: 0.444 ms
Execution Time: 15.257 ms

EXPLAIN ANALYZE SELECT id, name FROM (SELECT id, name FROM table1 UNION
SELECT id, name FROM table2) AS q
WHERE id IN (1538,8836,5486,3464,2673);
---------------------------------------------------------------
HashAggregate  (cost=51.23..51.33 rows=10 width=36) (actual
time=0.065..0.067 rows=10 loops=1)
  Group Key: table1.id, table1.name
  Batches: 1  Memory Usage: 24kB
  ->  Append  (cost=0.29..51.18 rows=10 width=36) (actual time=0.025..0.057
rows=10 loops=1)
        ->  Index Scan using table1_pkey on table1  (cost=0.29..25.51 rows=5
width=37) (actual time=0.024..0.038 rows=5 loops=1)
              Index Cond: (id = ANY
('{1538,8836,5486,3464,2673}'::integer[]))
        ->  Index Scan using table2_pkey on table2  (cost=0.29..25.51 rows=5
width=37) (actual time=0.006..0.017 rows=5 loops=1)
              Index Cond: (id = ANY
('{1538,8836,5486,3464,2673}'::integer[]))
Planning Time: 0.170 ms
Execution Time: 0.097 ms


The results are also provided as SQL fiddles here:
https://dbfiddle.uk/xo7fug1o
https://www.db-fiddle.com/f/iUsfpdP2eD8YtdN2Em7Zyu/0