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 natural_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


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


sale_year
sale_month
Qty
cat_id


2018
10
100
1

2018
10
0
2

2018
10
100
3

2018
10
400
4

2018
11
0
1

2018
11
200
2

2018
11
300
3

2018
11
400
4

2018
12
100
1

2018
12
200
2

2018
12
300
3

2018
12
0
4

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