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 book
CREATE TABLE book (id SERIAL NOT NULL, author_id INTEGER NOT NULL, publisher_id INTEGER NOT NULL, CONSTRAINT book_pkey PRIMARY KEY (id));
CREATE INDEX book_author_index ON book (author_id);
CREATE INDEX book_publisher_index ON book (publisher_id);

-- Create table author
CREATE TABLE author (id SERIAL NOT NULL, name VARCHAR NOT NULL, CONSTRAINT author_pkey PRIMARY KEY (id));
CREATE INDEX author_name_index ON author (name);

-- Create table publisher
CREATE TABLE publisher (id SERIAL NOT NULL, name VARCHAR NOT NULL, CONSTRAINT publisher_pkey PRIMARY KEY (id));
CREATE INDEX publisher_name_index ON publisher (name);

-- Insert dummy data for testing
INSERT INTO book (id, author_id, publisher_id) SELECT generate_series(1, 1000000), (generate_series(1, 1000000) - 1) % 10000 + 1, (generate_series(1, 1000000) - 1) % 10000 + 1;
INSERT INTO author (id, name) SELECT generate_series(1, 10000), md5(random()::text); 
INSERT INTO publisher (id, name) SELECT generate_series(1, 10000), md5(random()::text); 

-- Analyze tables
ANALYZE;

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

INSERT 0 1000000

INSERT 0 10000

INSERT 0 10000

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

-- Use case: Find books by either author or publisher name
-- This query executes a few 1000 times slower than the ANY(ARRAY(...)) variant below!
EXPLAIN ANALYZE SELECT * FROM book WHERE
  author_id IN (SELECT id FROM author WHERE name = 'some_name') OR
  publisher_id IN (SELECT id FROM publisher WHERE name = 'some_name');

QUERY PLAN

Seq Scan on book  (cost=16.61..20422.61 rows=750000 width=12) (actual time=227.689..227.691 rows=0 loops=1)

  Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2))

  Rows Removed by Filter: 1000000

  SubPlan 1

    ->  Index Scan using author_name_index on author  (cost=0.29..8.30 rows=1 width=4) (actual time=0.014..0.015 rows=0 loops=1)

          Index Cond: ((name)::text = 'some_name'::text)

  SubPlan 2

    ->  Index Scan using publisher_name_index on publisher  (cost=0.29..8.30 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

          Index Cond: ((name)::text = 'some_name'::text)

Planning Time: 0.951 ms

Execution Time: 227.822 ms

EXPLAIN

-- Find books by either author or publisher name
-- This query executes a few 1000 times faster than the IN(...) variant above!
EXPLAIN ANALYZE SELECT * FROM book WHERE
  author_id = ANY(ARRAY(SELECT id FROM author WHERE name = 'some_name')) OR
  publisher_id = ANY(ARRAY(SELECT id FROM publisher WHERE name = 'some_name'));

QUERY PLAN

Bitmap Heap Scan on book  (cost=121.10..4113.39 rows=1999 width=12) (actual time=0.046..0.047 rows=0 loops=1)

  Recheck Cond: ((author_id = ANY ($0)) OR (publisher_id = ANY ($1)))

  InitPlan 1 (returns $0)

    ->  Index Scan using author_name_index on author  (cost=0.29..8.30 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1)

          Index Cond: ((name)::text = 'some_name'::text)

  InitPlan 2 (returns $1)

    ->  Index Scan using publisher_name_index on publisher  (cost=0.29..8.30 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

          Index Cond: ((name)::text = 'some_name'::text)

  ->  BitmapOr  (cost=104.50..104.50 rows=2000 width=0) (actual time=0.040..0.040 rows=0 loops=1)

        ->  Bitmap Index Scan on book_author_index  (cost=0.00..51.75 rows=1000 width=0) (actual time=0.030..0.030 rows=0 loops=1)

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

        ->  Bitmap Index Scan on book_publisher_index  (cost=0.00..51.75 rows=1000 width=0) (actual time=0.007..0.007 rows=0 loops=1)

              Index Cond: (publisher_id = ANY ($1))

Planning Time: 0.218 ms

Execution Time: 0.088 ms

EXPLAIN