Postgres 14

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 sales (
id int NOT NULL  PRIMARY KEY,
sku text NOT NULL,
date date NOT NULL,
amount int NOT NULL,
CONSTRAINT date_sku UNIQUE (sku,date)
)

CREATE TABLE


INSERT INTO sales VALUES (1,'A','01/01/2022',10);
INSERT INTO sales VALUES (2,'A','02/01/2022',10);
INSERT INTO sales VALUES (3,'A','03/01/2022',11);
INSERT INTO sales VALUES (4,'A','04/01/2022',11);
INSERT INTO sales VALUES (5,'B','01/01/2022',15);
INSERT INTO sales VALUES (6,'B','02/01/2022',20);
INSERT INTO sales VALUES (7,'B','03/01/2022',13);
INSERT INTO sales VALUES (8,'B','04/01/2022',12);
INSERT INTO sales VALUES (9,'C','01/01/2022',10);
INSERT INTO sales VALUES (10,'D','01/01/2022',35);
INSERT INTO sales VALUES (11,'D','02/01/2022',21);

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT *
FROM sales


id
sku
date
amount


1
A
2022-01-01
10

2
A
2022-01-02
10

3
A
2022-01-03
11

4
A
2022-01-04
11

5
B
2022-01-01
15

6
B
2022-01-02
20

7
B
2022-01-03
13

8
B
2022-01-04
12

9
C
2022-01-01
10

10
D
2022-01-01
35

11
D
2022-01-02
21



SELECT 11


SELECT *
FROM (
  SELECT sku,
  AVG(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-02' THEN amount END) AS first_in,
  AVG(CASE WHEN date BETWEEN '2022-01-02' AND '2022-01-03' THEN amount END) AS second_in,
  AVG(CASE WHEN date BETWEEN '2022-01-03' AND '2022-01-04' THEN amount END) AS third_in
  FROM sales
  GROUP BY sku
) AS t
WHERE first_in > 14
OR second_in > 14
OR third_in > 14


sku
first_in
second_in
third_in


B
17.5000000000000000
16.5000000000000000
12.5000000000000000

D
28.0000000000000000
21.0000000000000000
null



SELECT 2

id	sku	date	amount
1	A	2022-01-01	10
2	A	2022-01-02	10
3	A	2022-01-03	11
4	A	2022-01-04	11
5	B	2022-01-01	15
6	B	2022-01-02	20
7	B	2022-01-03	13
8	B	2022-01-04	12
9	C	2022-01-01	10
10	D	2022-01-01	35
11	D	2022-01-02	21

sku	first_in	second_in	third_in
B	17.5000000000000000	16.5000000000000000	12.5000000000000000
D	28.0000000000000000	21.0000000000000000	null