Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 10000

INSERT 0 10000

INSERT 0 5

ANALYZE

-- Print PostgreSQL version
SELECT version();

version

PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

-- Uses sequential scans on table1 and table2
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);

QUERY PLAN

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

-- Uses index scans on table1 and table2
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);

QUERY PLAN

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

EXPLAIN