Postgres 11

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 category
(
  cat_id   SERIAL,
  cat_name TEXT,
  CONSTRAINT category_pk PRIMARY KEY (cat_id)
);
INSERT INTO category (cat_name) VALUES ('hat'), ('shoe'), ('umbrella'), ('shirt');
4 rows affected

CREATE TABLE sale 
(
  sale_year  INTEGER NOT NULL,
  sale_month INTEGER NOT NULL,
  sale_cat   INTEGER NOT NULL,
  quantity   INTEGER NOT NULL CONSTRAINT sale_qty_gt_0 CHECK (quantity > 0),
  CONSTRAINT sale_category_fk FOREIGN KEY (sale_cat) REFERENCES category (cat_id),
  CONSTRAINT sale_nat__key_uq UNIQUE (sale_year, sale_month, sale_cat)
);
INSERT INTO sale VALUES (2018, 12, 1, 100);
INSERT INTO sale VALUES (2018, 12, 2, 200);
INSERT INTO sale VALUES (2018, 12, 3, 300);
INSERT INTO sale VALUES (2018, 11, 2, 200);
INSERT INTO sale VALUES (2018, 11, 3, 300);
INSERT INTO sale VALUES (2018, 11, 4, 400);
INSERT INTO sale VALUES (2018, 10, 1, 100);
INSERT INTO sale VALUES (2018, 10, 3, 100);
INSERT INTO sale VALUES (2018, 10, 4, 400);
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 DISTINCT s.sale_year, s.sale_month, c.cat_id
FROM sale s, category c;


sale_year
sale_month
cat_id


2018
10
1

2018
10
2

2018
10
3

2018
10
4

2018
11
1

2018
11
2

2018
11
3

2018
11
4

2018
12
1

2018
12
2

2018
12
3

2018
12
4


WITH cte1 AS
(
  SELECT DISTINCT s.sale_year, s.sale_month, c.cat_id
  FROM sale s, category c
)
SELECT c1.sale_year, c1.sale_month, c1.cat_id, COALESCE(s1.quantity, 0) AS "Qty"
FROM cte1 c1
LEFT JOIN sale s1 
ON  c1.sale_year  = s1.sale_year
AND c1.sale_month = s1.sale_month
AND c1.cat_id     = s1.sale_cat
ORDER BY sale_year, sale_month, cat_id;


sale_year
sale_month
cat_id
Qty


2018
10
1
100

2018
10
2
0

2018
10
3
100

2018
10
4
400

2018
11
1
0

2018
11
2
200

2018
11
3
300

2018
11
4
400

2018
12
1
100

2018
12
2
200

2018
12
3
300

2018
12
4
0

sale_year	sale_month	cat_id
2018	10	1
2018	10	2
2018	10	3
2018	10	4
2018	11	1
2018	11	2
2018	11	3
2018	11	4
2018	12	1
2018	12	2
2018	12	3
2018	12	4