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=11.063..15.096 rows=10 loops=1)

  Hash Cond: (table1.id = table3.id)

  ->  HashAggregate  (cost=768.00..968.00 rows=20000 width=36) (actual time=10.451..13.547 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.009..4.067 rows=20000 loops=1)

              ->  Seq Scan on table1  (cost=0.00..184.00 rows=10000 width=37) (actual time=0.008..1.263 rows=10000 loops=1)

              ->  Seq Scan on table2  (cost=0.00..184.00 rows=10000 width=37) (actual time=0.010..1.279 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.449 ms

Execution Time: 15.264 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 = ANY(ARRAY(SELECT id FROM table3));

QUERY PLAN

HashAggregate  (cost=86.45..86.65 rows=20 width=36) (actual time=0.081..0.083 rows=10 loops=1)

  Group Key: table1.id, table1.name

  Batches: 1  Memory Usage: 24kB

  InitPlan 1 (returns $0)

    ->  Seq Scan on table3  (cost=0.00..1.05 rows=5 width=4) (actual time=0.008..0.009 rows=5 loops=1)

  ->  Append  (cost=0.29..86.35 rows=20 width=36) (actual time=0.040..0.073 rows=10 loops=1)

        ->  Index Scan using table1_pkey on table1  (cost=0.29..43.03 rows=10 width=37) (actual time=0.040..0.054 rows=5 loops=1)

              Index Cond: (id = ANY ($0))

        ->  Index Scan using table2_pkey on table2  (cost=0.29..43.03 rows=10 width=37) (actual time=0.006..0.018 rows=5 loops=1)

              Index Cond: (id = ANY ($0))

Planning Time: 0.157 ms

Execution Time: 0.118 ms

EXPLAIN