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?.

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE product_spend (
 period VARCHAR(30),
report_date DATE,
spend_dollars FLOAT
);


INSERT INTO product_spend(period,report_date,spend_dollars) 
values
('30-sept-19','2019-09-30','4958617.00'),
('31-dec-19','2019-09-30','5038630'),
('31-mar-20','2020-03-31','5156327'),
('30-jun-20','2020-06-30','5344183'),
('30-sept-20','2020-09-30','5562796'),
('31-dec-20' ,'2020-12-31','5696796'),
('31-mar-21','2021-03-31','5749467'),
('30-jun-21','2021-06-30' ,'5680087');
8 rows affected

SELECT * FROM product_spend;


period
report_date
spend_dollars


30-sept-19
2019-09-30
4958617

31-dec-19
2019-09-30
5038630

31-mar-20
2020-03-31
5156327

30-jun-20
2020-06-30
5344183

30-sept-20
2020-09-30
5562796

31-dec-20
2020-12-31
5696796

31-mar-21
2021-03-31
5749467

30-jun-21
2021-06-30
5680087


SELECT 
  period, spend_dollars, 

  ROUND((spend_dollars / LAG(spend_dollars, 1)      ---  <<=== note offset 1!
    OVER (ORDER BY period))::NUMERIC * 100, 2) AS quarter,  

  ROUND((spend_dollars / LAG(spend_dollars, 4)      ---  <<=== note offset 4!
    OVER (ORDER BY period))::NUMERIC * 100, 2) AS annual
    
FROM product_spend
ORDER BY period;


period
spend_dollars
quarter
annual


30-jun-20
5344183
null
null

30-jun-21
5680087
106.29
null

30-sept-19
4958617
87.30
null

30-sept-20
5562796
112.18
null

31-dec-19
5038630
90.58
94.28

31-dec-20
5696796
113.06
100.29

31-mar-20
5156327
90.51
103.99

31-mar-21
5749467
111.50
103.36

period	report_date	spend_dollars
30-sept-19	2019-09-30	4958617
31-dec-19	2019-09-30	5038630
31-mar-20	2020-03-31	5156327
30-jun-20	2020-06-30	5344183
30-sept-20	2020-09-30	5562796
31-dec-20	2020-12-31	5696796
31-mar-21	2021-03-31	5749467
30-jun-21	2021-06-30	5680087

period	spend_dollars	quarter	annual
30-jun-20	5344183	null	null
30-jun-21	5680087	106.29	null
30-sept-19	4958617	87.30	null
30-sept-20	5562796	112.18	null
31-dec-19	5038630	90.58	94.28
31-dec-20	5696796	113.06	100.29
31-mar-20	5156327	90.51	103.99
31-mar-21	5749467	111.50	103.36