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 table1 as
select n::int as id
, n::int+'2019-09-01'::date as "date"
,(random()*1e3)::int as quantity
,(random()*1e3)::int as "value"
from generate_series(1,2e5)n;
alter table table1 add constraint table1_pk_id primary key(id);
create index on table1 (date,id)include(quantity);
select*from table1 tablesample bernoulli(.42)repeatable(.42)limit 8;
| setseed |
|---|
SELECT 1
SELECT 200000
ALTER TABLE
CREATE INDEX
| id | date | quantity | value |
|---|---|---|---|
| 109 | 2019-12-19 | 802 | 504 |
| 616 | 2021-05-09 | 348 | 685 |
| 732 | 2021-09-02 | 780 | 889 |
| 876 | 2022-01-24 | 764 | 331 |
| 1316 | 2023-04-09 | 573 | 449 |
| 2131 | 2025-07-02 | 957 | 411 |
| 2141 | 2025-07-12 | 701 | 645 |
| 2787 | 2027-04-19 | 61 | 160 |
SELECT 8
vacuum analyze table1;
VACUUM
begin;
explain analyze verbose
with new_state as (
select id
, sum(quantity)over(order by date,id) new_value
from table1)
update table1
set value=new_value
from new_state
where table1.id=new_state.id;
select*from table1 order by date,id limit 30;
rollback;
BEGIN
| QUERY PLAN |
|---|
| Update on public.table1 (cost=6559.42..23665.43 rows=0 width=0) (actual time=3707.492..3707.495 rows=0 loops=1) |
| -> Hash Join (cost=6559.42..23665.43 rows=200000 width=46) (actual time=67.486..661.883 rows=200000 loops=1) |
| Output: new_state.new_value, table1.ctid, new_state.* |
| Inner Unique: true |
| Hash Cond: (new_state.id = table1.id) |
| -> Subquery Scan on new_state (cost=0.42..11588.42 rows=200000 width=48) (actual time=0.544..400.413 rows=200000 loops=1) |
| Output: new_state.new_value, new_state.*, new_state.id |
| -> WindowAgg (cost=0.42..9588.42 rows=200000 width=16) (actual time=0.089..331.044 rows=200000 loops=1) |
| Output: table1_1.id, sum(table1_1.quantity) OVER (?), table1_1.date |
| -> Index Only Scan using table1_date_id_quantity_idx on public.table1 table1_1 (cost=0.42..6088.42 rows=200000 width=12) (actual time=0.069..116.410 rows=200000 loops=1) |
| Output: table1_1.id, table1_1.date, table1_1.quantity |
| Heap Fetches: 196803 |
| -> Hash (cost=3082.00..3082.00 rows=200000 width=10) (actual time=66.588..66.589 rows=200000 loops=1) |
| Output: table1.ctid, table1.id |
| Buckets: 262144 Batches: 2 Memory Usage: 6358kB |
| -> Seq Scan on public.table1 (cost=0.00..3082.00 rows=200000 width=10) (actual time=0.015..28.391 rows=200000 loops=1) |
| Output: table1.ctid, table1.id |
| Planning Time: 0.793 ms |
| Execution Time: 3707.857 ms |
EXPLAIN
| id | date | quantity | value |
|---|---|---|---|
| 1 | 2019-09-02 | 743 | 743 |
| 2 | 2019-09-03 | 937 | 1680 |
| 3 | 2019-09-04 | 94 | 1774 |
| 4 | 2019-09-05 | 491 | 2265 |
| 5 | 2019-09-06 | 575 | 2840 |
| 6 | 2019-09-07 | 570 | 3410 |
| 7 | 2019-09-08 | 838 | 4248 |
| 8 | 2019-09-09 | 131 | 4379 |
| 9 | 2019-09-10 | 312 | 4691 |
| 10 | 2019-09-11 | 82 | 4773 |
| 11 | 2019-09-12 | 410 | 5183 |
| 12 | 2019-09-13 | 423 | 5606 |
| 13 | 2019-09-14 | 977 | 6583 |
| 14 | 2019-09-15 | 943 | 7526 |
| 15 | 2019-09-16 | 489 | 8015 |
| 16 | 2019-09-17 | 206 | 8221 |
| 17 | 2019-09-18 | 272 | 8493 |
| 18 | 2019-09-19 | 274 | 8767 |
| 19 | 2019-09-20 | 854 | 9621 |
| 20 | 2019-09-21 | 783 | 10404 |
| 21 | 2019-09-22 | 826 | 11230 |
| 22 | 2019-09-23 | 235 | 11465 |
| 23 | 2019-09-24 | 917 | 12382 |
| 24 | 2019-09-25 | 119 | 12501 |
| 25 | 2019-09-26 | 314 | 12815 |
| 26 | 2019-09-27 | 188 | 13003 |
| 27 | 2019-09-28 | 108 | 13111 |
| 28 | 2019-09-29 | 632 | 13743 |
| 29 | 2019-09-30 | 29 | 13772 |
| 30 | 2019-10-01 | 905 | 14677 |
SELECT 30
ROLLBACK