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
--UPDATE table1 SET value = {_previous_value} WHERE table1.id = {_r.id};
CREATE OR REPLACE FUNCTION functiontest()
RETURNS VOID
LANGUAGE plpgsql
AS
$f$
DECLARE _r RECORD;
DECLARE _previous_value DOUBLE PRECISION := 0;
BEGIN
FOR _r IN
SELECT table1.date, table1.id
FROM table1
ORDER BY table1.date, table1.id
LOOP
WITH
c AS
(
SELECT
table1.id,
table1.quantity + _previous_value AS total
FROM table1
WHERE table1.id = _r.id
ORDER BY table1.date, table1.id
LIMIT 1
)
SELECT
COALESCE(c.total, 0)
INTO
_previous_value
FROM c;
EXECUTE
'UPDATE table1 SET value = ' || _previous_value || ' WHERE table1.id = ' || _r.id || ';';
END LOOP;
END;
CREATE FUNCTION
begin;
explain analyze verbose select functiontest();
select*from table1 order by date,id limit 30;
rollback;
BEGIN
| QUERY PLAN |
|---|
| Result (cost=0.00..0.26 rows=1 width=4) (actual time=23398.695..23398.695 rows=1 loops=1) |
| Output: functiontest() |
| Planning Time: 0.040 ms |
| Execution Time: 23398.779 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