Postgres 14

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

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

-- Analyze tables
ANALYZE;

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

INSERT 0 1000000

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

-- This query executes MUCH slower than the optimized ANY(ARRAY(...)) variant below!
EXPLAIN ANALYZE SELECT * FROM book WHERE
  author_id IS NULL OR
  author_id IN (SELECT id FROM author WHERE name = 'some_name');

QUERY PLAN

Seq Scan on book  (cost=8.30..16933.31 rows=500000 width=8) (actual time=159.121..159.123 rows=0 loops=1)

  Filter: ((author_id IS NULL) OR (hashed SubPlan 1))

  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.015..0.016 rows=0 loops=1)

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

Planning Time: 0.598 ms

Execution Time: 159.227 ms

EXPLAIN

-- This query executes MUCH faster than the IN(...) variant above!
EXPLAIN ANALYZE SELECT * FROM book WHERE
  author_id IS NULL OR
  author_id = ANY(ARRAY(SELECT id FROM author WHERE name = 'some_name'));

QUERY PLAN

Bitmap Heap Scan on book  (cost=64.98..2467.85 rows=1000 width=8) (actual time=0.042..0.043 rows=0 loops=1)

  Recheck Cond: ((author_id IS NULL) OR (author_id = 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.009..0.009 rows=0 loops=1)

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

  ->  BitmapOr  (cost=56.68..56.68 rows=1000 width=0) (actual time=0.036..0.036 rows=0 loops=1)

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

              Index Cond: (author_id IS NULL)

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

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

Planning Time: 0.178 ms

Execution Time: 0.084 ms

EXPLAIN