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 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit |
SELECT 1
CREATE TABLE orders
(
user_id INTEGER,
order_id INTEGER,
order_date DATE,
quantity INTEGER,
revenue FLOAT,
product VARCHAR(255)
);
CREATE TABLE
ALTER TABLE orders ADD CONSTRAINT orders_pk
PRIMARY KEY (user_id, order_id);
ALTER TABLE
CREATE INDEX uop_ix ON orders USING BTREE
(
user_id, order_date, product
);
CREATE INDEX
CREATE INDEX user_id_order_date_uq ON orders USING BTREE
(
user_id, order_date
);
CREATE INDEX
INSERT INTO orders VALUES
(1, 1, '2021-03-05', 1, 15, 'books'), -- = 15
(1, 4, '2021-11-17', 2, 25, 'books'), -- = 50 = 65
(4, 12, '2022-11-20', 2, 95, 'books'), -- = 190
(4, 14, '2022-11-23', 4, 17, 'books'), -- = 68 = 258
-- = 323 - Grand total
(1, 2, '2022-03-07', 1, 3, 'music'),
(1, 3, '2022-06-15', 1, 900, 'travel'),
(2, 6, '2021-04-12', 2, 4, 'music'),
(2, 8, '2022-11-03', 1, 8, 'music'),
(2, 7, '2021-06-29', 3, 9, 'books'),
(2, 5, '2022-08-03', 2, 32, 'books'),
(3, 9, '2022-11-07', 1, 575, 'food'),
(3, 10, '2022-11-20', 2, 95, 'food'),
(3, 11, '2022-11-20', 1, 95, 'food'),
(4, 13, '2022-11-21', 1, 95, 'food'),
(5, 15, '2022-11-20', 1, 95, 'food'),
(5, 16, '2022-11-25', 2, 95, 'books'),
(5, 17, '2022-11-29', 1, 95, 'food');
INSERT 0 17
SELECT
user_id, -- some of these fields are not necessary
product, -- they just help in getting the full picture.
order_date,
SUM(revenue * quantity) OVER (PARTITION BY user_id, product) AS rev,
ROUND(AVG(quantity) OVER (PARTITION BY user_id, product), 2) AS avg_qty,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id, order_date) AS rn
FROM
orders
ORDER BY user_id, order_date;
| user_id | product | order_date | rev | avg_qty | rn |
|---|---|---|---|---|---|
| 1 | books | 2021-03-05 | 65 | 1.50 | 1 |
| 1 | books | 2021-11-17 | 65 | 1.50 | 2 |
| 1 | music | 2022-03-07 | 3 | 1.00 | 3 |
| 1 | travel | 2022-06-15 | 900 | 1.00 | 4 |
| 2 | music | 2021-04-12 | 16 | 1.50 | 1 |
| 2 | books | 2021-06-29 | 91 | 2.50 | 2 |
| 2 | books | 2022-08-03 | 91 | 2.50 | 3 |
| 2 | music | 2022-11-03 | 16 | 1.50 | 4 |
| 3 | food | 2022-11-07 | 860 | 1.33 | 1 |
| 3 | food | 2022-11-20 | 860 | 1.33 | 2 |
| 3 | food | 2022-11-20 | 860 | 1.33 | 3 |
| 4 | books | 2022-11-20 | 258 | 3.00 | 1 |
| 4 | food | 2022-11-21 | 95 | 1.00 | 2 |
| 4 | books | 2022-11-23 | 258 | 3.00 | 3 |
| 5 | food | 2022-11-20 | 190 | 1.00 | 1 |
| 5 | books | 2022-11-25 | 190 | 2.00 | 2 |
| 5 | food | 2022-11-29 | 190 | 1.00 | 3 |
SELECT 17
WITH cte AS
(
SELECT
user_id, -- some of these fields are not necessary
product, -- they just help in getting the full picture.
order_date,
SUM(revenue * quantity) OVER (PARTITION BY user_id, product) AS rev,
AVG(quantity) OVER (PARTITION BY user_id, product) AS avg_qty,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id, order_date) AS rn
FROM
orders
ORDER BY user_id, order_date
)
SELECT
SUM(rev) AS tot_rev,
ROUND(AVG(avg_qty), 2) AS avg_q
FROM
cte
WHERE
product = 'books' AND rn = 1;
| tot_rev | avg_q |
|---|---|
| 323 | 2.25 |
SELECT 1