Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE author (id int primary key);

CREATE TABLE

EXPLAIN SELECT * FROM author WHERE id IN (
  SELECT id FROM author WHERE id > 10
  UNION
  SELECT id FROM author WHERE id < 10
) AND id > 100

QUERY PLAN

Hash Join  (cost=117.48..138.95 rows=567 width=4)

  Hash Cond: (author_1.id = author.id)

  ->  HashAggregate  (cost=75.48..92.48 rows=1700 width=4)

        Group Key: author_1.id

        ->  Append  (cost=10.74..71.23 rows=1700 width=4)

              ->  Bitmap Heap Scan on author author_1  (cost=10.74..31.37 rows=850 width=4)

                    Recheck Cond: (id > 10)

                    ->  Bitmap Index Scan on author_pkey  (cost=0.00..10.53 rows=850 width=0)

                          Index Cond: (id > 10)

              ->  Bitmap Heap Scan on author author_2  (cost=10.74..31.37 rows=850 width=4)

                    Recheck Cond: (id < 10)

                    ->  Bitmap Index Scan on author_pkey  (cost=0.00..10.53 rows=850 width=0)

                          Index Cond: (id < 10)

  ->  Hash  (cost=31.37..31.37 rows=850 width=4)

        ->  Bitmap Heap Scan on author  (cost=10.74..31.37 rows=850 width=4)

              Recheck Cond: (id > 100)

              ->  Bitmap Index Scan on author_pkey  (cost=0.00..10.53 rows=850 width=0)

                    Index Cond: (id > 100)

EXPLAIN

EXPLAIN SELECT * FROM (
    SELECT * FROM author WHERE id > 10
    UNION
    SELECT * FROM author WHERE id < 10
) WHERE id > 100

QUERY PLAN

HashAggregate  (cost=57.11..65.74 rows=863 width=4)

  Group Key: author.id

  ->  Append  (cost=12.87..54.95 rows=863 width=4)

        ->  Bitmap Heap Scan on author  (cost=12.87..35.62 rows=850 width=4)

              Recheck Cond: ((id > 10) AND (id > 100))

              ->  Bitmap Index Scan on author_pkey  (cost=0.00..12.65 rows=850 width=0)

                    Index Cond: ((id > 10) AND (id > 100))

        ->  Bitmap Heap Scan on author author_1  (cost=4.29..15.02 rows=13 width=4)

              Recheck Cond: ((id < 10) AND (id > 100))

              ->  Bitmap Index Scan on author_pkey  (cost=0.00..4.29 rows=13 width=0)

                    Index Cond: ((id < 10) AND (id > 100))

EXPLAIN