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 |