Postgres 12

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 12.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


CREATE TABLE bp  -- bitcoin_purchases
(
  bp_date TIMESTAMP NOT NULL,
  bp_count SMALLINT NOT NULL,
  coin_price DOUBLE PRECISION NOT NULL CHECK (coin_price > 0),
  bp_total DOUBLE PRECISION GENERATED ALWAYS AS (bp_count * coin_price) STORED
);

CREATE TABLE


INSERT INTO bp VALUES
('2020-01-05 10:00:00', '10',  '500'),
('2020-01-25 10:00:00', '20', '1000'),
('2020-01-28 10:00:00', '20', '1250'),
('2020-03-05 10:00:00', '10', '1500'),
('2020-03-05 10:00:00', '10', '2000'),
('2020-05-17 10:00:00', '10', '2500'),
('2020-05-19 10:00:00', '10', '3000'),
('2020-07-23 10:00:00', '10', '500.45'),
('2020-07-27 10:00:00', '10', '500.45'),
('2020-09-30 10:00:00', '10', '500.45');
SELECT * FROM bp;

INSERT 0 10




bp_date
bp_count
coin_price
bp_total


2020-01-05 10:00:00
10
500
5000

2020-01-25 10:00:00
20
1000
20000

2020-01-28 10:00:00
20
1250
25000

2020-03-05 10:00:00
10
1500
15000

2020-03-05 10:00:00
10
2000
20000

2020-05-17 10:00:00
10
2500
25000

2020-05-19 10:00:00
10
3000
30000

2020-07-23 10:00:00
10
500.45
5004.5

2020-07-27 10:00:00
10
500.45
5004.5

2020-09-30 10:00:00
10
500.45
5004.5



SELECT 10


CREATE TABLE cal (dc) AS 
SELECT GENERATE_SERIES
       (
           (DATE '2020-01-01'),
           (DATE '2020-12-31'),
           interval '1 MONTH'
       )::DATE;
SELECT * FROM cal;

SELECT 12




dc


2020-01-01

2020-02-01

2020-03-01

2020-04-01

2020-05-01

2020-06-01

2020-07-01

2020-08-01

2020-09-01

2020-10-01

2020-11-01

2020-12-01



SELECT 12


SELECT 
  bp.bp_date, bp.coin_price AS "price",
  SUM(bp_count) OVER (PARTITION BY EXTRACT('MONTH' FROM bp.bp_date)) AS "No./mth",
  SUM(bp_count) OVER () AS "No. coints"
FROM bp
ORDER BY bp.bp_date;


bp_date
price
No./mth
No. coints


2020-01-05 10:00:00
500
50
120

2020-01-25 10:00:00
1000
50
120

2020-01-28 10:00:00
1250
50
120

2020-03-05 10:00:00
1500
20
120

2020-03-05 10:00:00
2000
20
120

2020-05-17 10:00:00
2500
20
120

2020-05-19 10:00:00
3000
20
120

2020-07-23 10:00:00
500.45
20
120

2020-07-27 10:00:00
500.45
20
120

2020-09-30 10:00:00
500.45
10
120



SELECT 10

bp_date	bp_count	coin_price	bp_total
2020-01-05 10:00:00	10	500	5000
2020-01-25 10:00:00	20	1000	20000
2020-01-28 10:00:00	20	1250	25000
2020-03-05 10:00:00	10	1500	15000
2020-03-05 10:00:00	10	2000	20000
2020-05-17 10:00:00	10	2500	25000
2020-05-19 10:00:00	10	3000	30000
2020-07-23 10:00:00	10	500.45	5004.5
2020-07-27 10:00:00	10	500.45	5004.5
2020-09-30 10:00:00	10	500.45	5004.5