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

CREATE TABLE prices (
  id INT,
  product_id INT, /*Foreign key*/
  created_at DATE,
  price INT
);
INSERT INTO prices (id, product_id, created_at, price) VALUES (1, 1, '2020-01-01', 11000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (2, 2, '2020-01-01', 3999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (3, 3, '2020-01-01', 9999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (4, 4, '2020-01-01', 2000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (5, 1, '2020-01-02', 9999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (6, 2, '2020-01-02', 2999);    
INSERT INTO prices (id, product_id, created_at, price) VALUES (7, 5, '2020-01-02', 2999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (8, 7, '2020-01-03', 8999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (9, 1, '2020-01-03', 7000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (10, 5, '2020-01-03', 4000);
INSERT INTO prices (id, product_id, created_at, price) VALUES (11, 6, '2020-01-03', 3999);
INSERT INTO prices (id, product_id, created_at, price) VALUES (12, 3, '2020-01-03', 6999);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select *
from prices
order by 2, 3


id
product_id
created_at
price


1
1
2020-01-01
11000

5
1
2020-01-02
9999

9
1
2020-01-03
7000

2
2
2020-01-01
3999

6
2
2020-01-02
2999

3
3
2020-01-01
9999

12
3
2020-01-03
6999

4
4
2020-01-01
2000

7
5
2020-01-02
2999

10
5
2020-01-03
4000

11
6
2020-01-03
3999

8
7
2020-01-03
8999


select created_at, avg(prev_price - price), sum(prev_price - price)
from (select p.*, lag(price) over (partition by product_id order by created_at) as prev_price
      from prices p
     ) p
group by created_at
order by created_at


created_at
avg
sum


2020-01-01
null
null

2020-01-02
1000.5000000000000000
2001

2020-01-03
1666.0000000000000000
4998

id	product_id	created_at	price
1	1	2020-01-01	11000
5	1	2020-01-02	9999
9	1	2020-01-03	7000
2	2	2020-01-01	3999
6	2	2020-01-02	2999
3	3	2020-01-01	9999
12	3	2020-01-03	6999
4	4	2020-01-01	2000
7	5	2020-01-02	2999
10	5	2020-01-03	4000
11	6	2020-01-03	3999
8	7	2020-01-03	8999

created_at	avg	sum
2020-01-01	null	null
2020-01-02	1000.5000000000000000	2001
2020-01-03	1666.0000000000000000	4998