Postgres 15

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?.
select setseed(.42);
create table my_table(id,user_id,group_id,c1,c2,c3,deleted_at)as
select n,(random()*1e5)::int as user_id
        ,(random()*1e5)::int as group_id
        ,(random()*1e5)::int as c1
        ,(random()*1e5)::int as c2
        ,(random()*1e5)::int as c3
        ,case when .8>random() then now()-'1 year'::interval end as deleted_at
from generate_series(1,3e5)n;

create index idx1 on my_table(group_id);

setseed

SELECT 1

SELECT 300000

CREATE INDEX

vacuum analyze my_table;

VACUUM

explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null

QUERY PLAN

Aggregate  (cost=20.02..20.03 rows=1 width=8) (actual time=0.124..0.125 rows=1 loops=1)

  Output: count(DISTINCT user_id)

  ->  Bitmap Heap Scan on public.my_table  (cost=4.45..20.02 rows=1 width=4) (actual time=0.095..0.103 rows=2 loops=1)

        Output: id, user_id, group_id, c1, c2, c3, deleted_at

        Recheck Cond: (my_table.group_id = 123)

        Filter: (my_table.deleted_at IS NULL)

        Rows Removed by Filter: 2

        Heap Blocks: exact=4

        ->  Bitmap Index Scan on idx1  (cost=0.00..4.45 rows=4 width=0) (actual time=0.085..0.086 rows=4 loops=1)

              Index Cond: (my_table.group_id = 123)

Planning Time: 0.170 ms

Execution Time: 0.219 ms

EXPLAIN

create index idx2 on my_table(group_id)include(user_id,deleted_at);

CREATE INDEX

vacuum analyze my_table;

VACUUM

explain analyze verbose
select count(distinct user_id)
from my_table
where group_id = '123' and deleted_at is null

QUERY PLAN

Aggregate  (cost=4.50..4.51 rows=1 width=8) (actual time=0.071..0.072 rows=1 loops=1)

  Output: count(DISTINCT user_id)

  ->  Index Only Scan using idx2 on public.my_table  (cost=0.42..4.49 rows=1 width=4) (actual time=0.055..0.057 rows=2 loops=1)

        Output: group_id, user_id, deleted_at

        Index Cond: (my_table.group_id = 123)

        Filter: (my_table.deleted_at IS NULL)

        Rows Removed by Filter: 2

        Heap Fetches: 0

Planning Time: 0.108 ms

Execution Time: 0.095 ms

EXPLAIN