Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE book (id SERIAL NOT NULL, name VARCHAR NOT NULL, author INTEGER NOT NULL, CONSTRAINT book_pkey PRIMARY KEY (id));
CREATE INDEX book_name_index ON book (name);
CREATE INDEX book_author_index ON book (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);

INSERT INTO book (id, name, author) SELECT generate_series(1, 100000), md5(random()::text), (generate_series(1, 100000) - 1) % 10000 + 1;
INSERT INTO author (id, name) SELECT generate_series(1, 10000), md5(random()::text); 

ANALYZE;

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE TABLE

CREATE INDEX

INSERT 0 100000

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

-- Uses sequential scan on book
EXPLAIN ANALYZE SELECT * FROM book WHERE name = 'x' OR author IN (SELECT id FROM author WHERE name = 'y'); 

QUERY PLAN

Seq Scan on book  (cost=8.30..2443.31 rows=50000 width=41) (actual time=26.878..26.880 rows=0 loops=1)

  Filter: (((name)::text = 'x'::text) OR (hashed SubPlan 1))

  Rows Removed by Filter: 100000

  SubPlan 1

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

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

Planning Time: 0.720 ms

Execution Time: 26.981 ms

EXPLAIN

-- Uses bitmap heap scan on book
EXPLAIN ANALYZE SELECT * FROM book WHERE name = 'x' OR author = ANY(ARRAY(SELECT id FROM author WHERE name = 'y'));

QUERY PLAN

Bitmap Heap Scan on book  (cost=56.45..350.69 rows=101 width=41) (actual time=0.046..0.047 rows=0 loops=1)

  Recheck Cond: (((name)::text = 'x'::text) OR (author = ANY ($0)))

  InitPlan 1 (returns $0)

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

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

  ->  BitmapOr  (cost=48.15..48.15 rows=101 width=0) (actual time=0.044..0.045 rows=0 loops=1)

        ->  Bitmap Index Scan on book_name_index  (cost=0.00..4.43 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)

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

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

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

Planning Time: 0.166 ms

Execution Time: 0.080 ms

EXPLAIN