Postgres 12

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 benchmark_booleans (
  id SERIAL UNIQUE NOT NULL,
  is_deleted boolean, -- not unique
  deleted_at timestamp
);
CREATE INDEX idx_is_deleted on benchmark_booleans (is_deleted);
CREATE INDEX idx_deleted_at on benchmark_booleans (deleted_at);
INSERT INTO benchmark_booleans (is_deleted, deleted_at)
SELECT 
 (RANDOM() > 0.5) as is_deleted,
 case when RANDOM() < 0.5 
     then null
     else now() - trunc(random() * 20) * '1 year'::interval
      + trunc(random() * 365) * '1 day'::interval 
 end as deleted_at
 FROM generate_series(1,100000);
100000 rows affected

select count(*) from benchmark_booleans where is_deleted = true;

count

49921

select count(*) from benchmark_booleans where deleted_at is null;

count

49940

explain (analyze,verbose,costs,buffers) 
select count(*) from benchmark_booleans where is_deleted = true;

QUERY PLAN

Aggregate  (cost=1590.23..1590.24 rows=1 width=8) (actual time=27.429..27.429 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=456 read=35 written=32

  ->  Seq Scan on fiddle_xdjlwcliluiuwevjsrvm.benchmark_booleans  (cost=0.00..1468.09 rows=48854 width=0) (actual time=0.239..20.531 rows=49921 loops=1)

        Output: id, is_deleted, deleted_at

        Filter: benchmark_booleans.is_deleted

        Rows Removed by Filter: 50079

        Buffers: shared hit=456 read=35 written=32

Planning Time: 0.101 ms

Execution Time: 27.456 ms

explain (analyze,verbose,costs,buffers) 
select count(*) from benchmark_booleans where deleted_at is null;

QUERY PLAN

Aggregate  (cost=525.62..525.63 rows=1 width=8) (actual time=220.955..220.956 rows=1 loops=1)

  Output: count(*)

  Buffers: shared hit=36 read=584 written=244

  ->  Bitmap Heap Scan on fiddle_xdjlwcliluiuwevjsrvm.benchmark_booleans  (cost=12.08..524.40 rows=489 width=0) (actual time=6.625..215.882 rows=49940 loops=1)

        Recheck Cond: (benchmark_booleans.deleted_at IS NULL)

        Heap Blocks: exact=491

        Buffers: shared hit=36 read=584 written=244

        ->  Bitmap Index Scan on idx_deleted_at  (cost=0.00..11.96 rows=489 width=0) (actual time=6.556..6.556 rows=49940 loops=1)

              Index Cond: (benchmark_booleans.deleted_at IS NULL)

              Buffers: shared hit=5 read=124 written=65

Planning Time: 0.618 ms

Execution Time: 220.995 ms