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 products (
id int,
name text
);
INSERT INTO products VALUES
(1, 'Orange'),
(2, 'Juice'),
(3, 'Fance');
CREATE TABLE reviews (
id int,
created_at date,
price int,
product_id int
);
INSERT INTO reviews VALUES
(1, '2020-12-12', 2, 1),
(2, '2020-12-14', 4, 1),
(3, '2020-12-15', 5, 2);
3 rows affected
3 rows affected
SELECT
*
FROM (
SELECT DISTINCT ON (p.id)
p.id,
p.name,
r.id as review_id,
r.price
FROM
reviews r
RIGHT JOIN products p ON r.product_id = p.id
ORDER BY p.id, r.created_at DESC NULLS LAST
) s
ORDER BY price DESC NULLS LAST
| id | name | review_id | price |
|---|---|---|---|
| 2 | Juice | 3 | 5 |
| 1 | Orange | 2 | 4 |
| 3 | Fance | null | null |