Postgres 13

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 agreement ( ag_id int, name text, cost numeric(10,2) );
create index ag_idx on agreement (ag_id);
insert into agreement (ag_id, name, cost) values ( 1, '333', 22 ),
(1,'333', 33), (1, '333', 7), (2, '555', 18 ), (2, '555', 2), (3, '777', 4);
select * from agreement;
6 rows affected

ag_id
name
cost

1
333
22.00

1
333
33.00

1
333
7.00

2
555
18.00

2
555
2.00

3
777
4.00

-- put some logic into function
-- to prevent column repetition I just return whole composite type
create function initial () 
returns table( agreement_id int, ag agreement ) language sql stable AS $$
select ag_id, t from agreement t;
$$;
select * from initial() t;

agreement_id
ag

1
(1,333,22.00)

1
(1,333,33.00)

1
(1,333,7.00)

2
(2,555,18.00)

2
(2,555,2.00)

3
(3,777,4.00)

explain( analyze, costs, buffers, verbose ) with totals_by_ag as (
  select 
    *,
    sum( (t.ag).cost ) over ( partition by agreement_id ) as total
  from initial() t
)
select * from totals_by_ag t
where (t.ag).ag_id = 1; -- index is NOT USED

QUERY PLAN

Subquery Scan on t  (cost=0.15..91.50 rows=5 width=112) (actual time=0.060..0.095 rows=3 loops=1)

  Output: t.agreement_id, t.ag, t.total

  Filter: ((t.ag).ag_id = 1)

  Rows Removed by Filter: 3

  Buffers: shared hit=2

  ->  WindowAgg  (cost=0.15..78.75 rows=1020 width=112) (actual time=0.056..0.089 rows=6 loops=1)

        Output: t_1.ag_id, t_1.*, sum(t_1.cost) OVER (?)

        Buffers: shared hit=2

        ->  Index Scan using ag_idx on public.agreement t_1  (cost=0.15..63.45 rows=1020 width=96) (actual time=0.017..0.026 rows=6 loops=1)

              Output: t_1.ag_id, t_1.*, t_1.cost

              Buffers: shared hit=2

Planning:

  Buffers: shared hit=8 read=6

Planning Time: 0.771 ms

Execution Time: 0.190 ms

explain( analyze, costs, buffers, verbose ) with totals_by_ag as (
  select 
    *,
    sum( (t.ag).cost ) over ( partition by agreement_id ) as total
  from initial() t
)
select * from totals_by_ag t
where agreement_id = 1; -- index is used when alias for column is used

QUERY PLAN

WindowAgg  (cost=4.19..12.74 rows=5 width=112) (actual time=0.040..0.042 rows=3 loops=1)

  Output: t.ag_id, t.*, sum(t.cost) OVER (?)

  Buffers: shared hit=2

  ->  Bitmap Heap Scan on public.agreement t  (cost=4.19..12.66 rows=5 width=96) (actual time=0.017..0.020 rows=3 loops=1)

        Output: t.ag_id, t.*, t.cost

        Recheck Cond: (t.ag_id = 1)

        Heap Blocks: exact=1

        Buffers: shared hit=2

        ->  Bitmap Index Scan on ag_idx  (cost=0.00..4.19 rows=5 width=0) (actual time=0.008..0.008 rows=3 loops=1)

              Index Cond: (t.ag_id = 1)

              Buffers: shared hit=1

Planning Time: 0.132 ms

Execution Time: 0.201 ms

explain( analyze, costs, buffers, verbose ) with totals_by_ag as (
  select 
    *,
    sum( (t.ag).cost ) over ( partition by (t.ag).ag_id ) as total --renamed
  from initial() t
)
select * from totals_by_ag t
where agreement_id = 1; -- index is NOT USED because grouping by original column

QUERY PLAN

Subquery Scan on t  (cost=0.15..91.50 rows=5 width=112) (actual time=0.024..0.035 rows=3 loops=1)

  Output: t.agreement_id, t.ag, t.total

  Filter: (t.agreement_id = 1)

  Rows Removed by Filter: 3

  Buffers: shared hit=2

  ->  WindowAgg  (cost=0.15..78.75 rows=1020 width=116) (actual time=0.023..0.032 rows=6 loops=1)

        Output: t_1.ag_id, t_1.*, sum(t_1.cost) OVER (?), t_1.ag_id

        Buffers: shared hit=2

        ->  Index Scan using ag_idx on public.agreement t_1  (cost=0.15..63.45 rows=1020 width=96) (actual time=0.011..0.015 rows=6 loops=1)

              Output: t_1.ag_id, t_1.*, t_1.cost

              Buffers: shared hit=2

Planning Time: 0.120 ms

Execution Time: 0.062 ms

explain( analyze, costs, buffers, verbose ) with totals_by_ag as (
  select 
    *,
    sum( (t.ag).cost ) over ( partition by (t.ag).ag_id ) as total --renamed
  from initial() t
)
select * from totals_by_ag t
where (t.ag).ag_id = 1; -- index is NOT USED even if at both cases original column

QUERY PLAN

Subquery Scan on t  (cost=0.15..91.50 rows=5 width=112) (actual time=0.024..0.035 rows=3 loops=1)

  Output: t.agreement_id, t.ag, t.total

  Filter: ((t.ag).ag_id = 1)

  Rows Removed by Filter: 3

  Buffers: shared hit=2

  ->  WindowAgg  (cost=0.15..78.75 rows=1020 width=116) (actual time=0.022..0.031 rows=6 loops=1)

        Output: t_1.ag_id, t_1.*, sum(t_1.cost) OVER (?), t_1.ag_id

        Buffers: shared hit=2

        ->  Index Scan using ag_idx on public.agreement t_1  (cost=0.15..63.45 rows=1020 width=96) (actual time=0.010..0.015 rows=6 loops=1)

              Output: t_1.ag_id, t_1.*, t_1.cost

              Buffers: shared hit=2

Planning Time: 0.118 ms

Execution Time: 0.062 ms
agreement_id	ag
1	(1,333,22.00)
1	(1,333,33.00)
1	(1,333,7.00)
2	(2,555,18.00)
2	(2,555,2.00)
3	(3,777,4.00)