Postgres 10

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

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name CHARACTER VARYING(50)
);

CREATE TABLE orders_catalog (
	order_code INT PRIMARY KEY,
	order_desc CHARACTER VARYING(50) NOT NULL,
	cost REAL NOT NULL
);

CREATE TABLE encounter (
	encounter_id INT PRIMARY KEY,
	user_id INT NOT NULL,
	encounter_type CHARACTER VARYING(50) NOT NULL,

	CONSTRAINT FK_encounter FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE orders (
	order_id INT PRIMARY KEY,
	order_code INT NOT NULL,
	encounter_id INT NOT NULL,
	created_dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

	CONSTRAINT FK_orders_catalog FOREIGN KEY (order_code) REFERENCES orders_catalog (order_code),
	CONSTRAINT FK_orders_encounter FOREIGN KEY (encounter_id) REFERENCES encounter(encounter_id)
);
--

INSERT INTO users(user_id, name) VALUES(1, 'Peter');
INSERT INTO users(user_id, name) VALUES(2, 'Charles');
INSERT INTO users(user_id, name) VALUES(3, 'Eva') ;
INSERT INTO users(user_id, name) VALUES(4, 'John');
INSERT INTO users(user_id, name) VALUES(5, 'Helene');

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

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

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

1 rows affected

1 rows affected

SELECT
  u.user_id,
  name,
  COUNT (e.encounter_type) OVER (
    PARTITION BY u.user_id
  ) AS position,
  SUM (c.cost) OVER (
    PARTITION BY o.encounter_id ORDER BY o.created_dt DESC
  ) AS cost
FROM
  users u
INNER JOIN
  encounter e USING (user_id)
INNER JOIN
  orders o USING (encounter_id)
INNER JOIN
  orders_catalog c USING (order_code)
ORDER BY user_id;


user_id
name
position
cost


1
Peter
3
180.02

1
Peter
3
240.02

1
Peter
3
100.34

2
Charles
2
240.02

2
Charles
2
214.11

3
Eva
2
214.11

3
Eva
2
146.85

4
John
2
181.03

4
John
2
162.13

5
Helene
2
168.18

5
Helene
2
140.02

user_id	name	position	cost
1	Peter	3	180.02
1	Peter	3	240.02
1	Peter	3	100.34
2	Charles	2	240.02
2	Charles	2	214.11
3	Eva	2	214.11
3	Eva	2	146.85
4	John	2	181.03
4	John	2	162.13
5	Helene	2	168.18
5	Helene	2	140.02