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 orders (
customer_id text,
order_id text,
order_timestamp timestamp,
dispatch_seconds int,
distance int
);
INSERT INTO orders VALUES
('1ar5', '4r56', '2018-08-16 17:24:00', 1, 500),
('2et7', 'y452', '2018-08-16 17:24:30', 2, 400),
('1ar5', '9twi', '2018-08-16 18:24:00', 3, 300),
('1ar5', '82ir', '2018-08-17 19:24:00', 4, 200),
('2et7', 'dc1f', '2018-08-15 01:24:00', 5, 100);
CREATE TABLE
INSERT 0 5
SELECT
customer_id,
count,
order_id,
order_timestamp,
dispatch_seconds,
distance
FROM (
SELECT
*,
count(*) over (partition by customer_id),
first_value(order_id) over (partition by customer_id order by order_timestamp)
FROM orders
)s
WHERE order_id = first_value
| customer_id | count | order_id | order_timestamp | dispatch_seconds | distance |
|---|---|---|---|---|---|
| 1ar5 | 3 | 4r56 | 2018-08-16 17:24:00 | 1 | 500 |
| 2et7 | 2 | dc1f | 2018-08-15 01:24:00 | 5 | 100 |
SELECT 2