Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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, 500000), md5(random()::text);
INSERT INTO table2 (id, name) SELECT generate_series(1, 500000), md5(random()::text);
INSERT INTO table3 (id) VALUES (15678),(88336),(64586),(34164),(96267);

ANALYZE;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 500000

INSERT 0 500000

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 * FROM (SELECT * FROM table1 UNION SELECT * FROM table2) AS q
  WHERE id IN (SELECT id FROM table3);

QUERY PLAN

Merge Semi Join  (cost=187683.95..207933.98 rows=25000 width=36) (actual time=784.210..865.844 rows=10 loops=1)

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

  ->  Unique  (cost=187682.84..195182.84 rows=1000000 width=36) (actual time=766.565..849.540 rows=192535 loops=1)

        ->  Sort  (cost=187682.84..190182.84 rows=1000000 width=36) (actual time=766.563..808.106 rows=192535 loops=1)

              Sort Key: table1.id, table1.name

              Sort Method: external merge  Disk: 46024kB

              ->  Append  (cost=0.00..33334.00 rows=1000000 width=36) (actual time=0.033..409.856 rows=1000000 loops=1)

                    ->  Seq Scan on table1  (cost=0.00..9167.00 rows=500000 width=37) (actual time=0.032..142.827 rows=500000 loops=1)

                    ->  Seq Scan on table2  (cost=0.00..9167.00 rows=500000 width=37) (actual time=0.026..147.998 rows=500000 loops=1)

  ->  Sort  (cost=1.11..1.12 rows=5 width=4) (actual time=0.034..0.037 rows=5 loops=1)

        Sort Key: table3.id

        Sort Method: quicksort  Memory: 25kB

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

Planning Time: 0.662 ms

Execution Time: 932.412 ms

EXPLAIN

-- Uses index scans on table1 and table2
EXPLAIN ANALYZE
  SELECT * FROM (SELECT * FROM table1 UNION SELECT * FROM table2) AS q
  WHERE id = ANY(ARRAY(SELECT id FROM table3));

QUERY PLAN

HashAggregate  (cost=97.20..97.40 rows=20 width=36) (actual time=0.642..0.645 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.041..0.043 rows=5 loops=1)

  ->  Append  (cost=0.42..97.10 rows=20 width=36) (actual time=0.241..0.593 rows=10 loops=1)

        ->  Index Scan using table1_pkey on table1  (cost=0.42..48.40 rows=10 width=37) (actual time=0.240..0.387 rows=5 loops=1)

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

        ->  Index Scan using table2_pkey on table2  (cost=0.42..48.40 rows=10 width=37) (actual time=0.073..0.201 rows=5 loops=1)

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

Planning Time: 0.311 ms

Execution Time: 0.728 ms

EXPLAIN