Postgres 13

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 items (
  id      SERIAL                   NOT NULL PRIMARY KEY,
  country VARCHAR(2)               NOT NULL,
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  price   NUMERIC(11, 2)           NOT NULL
);
CREATE TABLE payments (
  id      SERIAL                   NOT NULL PRIMARY KEY,
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  amount  NUMERIC(11, 2)           NOT NULL,
  item_id INTEGER                  NULL
);
CREATE TABLE extras (
  id      SERIAL                   NOT NULL PRIMARY KEY,
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  amount  NUMERIC(11, 2)           NOT NULL,
  item_id INTEGER                  NULL
);

INSERT INTO items VALUES
  (1, 'CZ', '2016-11-01', 100),
  (2, 'CZ', '2016-11-02', 100),
  (3, 'PL', '2016-11-03', 20),
  (4, 'CZ', '2016-11-04', 150);
INSERT INTO payments VALUES
  (1, '2016-11-01', 60, 1),
  (2, '2016-11-01', 60, 1),
  (3, '2016-11-02', 100, 2),
  (4, '2016-11-03', 25, 3),
  (5, '2016-11-04', 150, 4);
INSERT INTO extras VALUES
  (1, '2016-11-01', 5, 1),
  (2, '2016-11-02', 1, 2),
  (3, '2016-11-03', 2, 3),
  (4, '2016-11-03', 3, 3),
  (5, '2016-11-04', 5, 4);

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 4

INSERT 0 5

INSERT 0 5

group_by	item_count	cost	earned	extra_earned
CZ	3	350.00	370.00	11.00
PL	1	20.00	25.00	5.00

SELECT 2