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 logistics (id SERIAL PRIMARY KEY, time_stamp DATE, product VARCHAR(255), quantity INT);
INSERT INTO logistics (time_stamp, product, quantity) VALUES
('2020-01-14', 'Product_A', '100'),
('2020-01-14', 'Product_B', '300'),
('2020-01-15', 'Product_B', '400'),
('2020-01-15', 'Product_C', '350'),
('2020-01-16', 'Product_B', '530'),
('2020-01-16', 'Product_C', '250'),
('2020-01-16', 'Product_D', '670'),
('2020-01-17', 'Product_C', '380'),
('2020-01-17', 'Product_D', '980'),
('2020-01-17', 'Product_E', '700'),
('2020-01-17', 'Product_F', '450');
11 rows affected
WITH cte AS (
SELECT time_stamp, product,
quantity - LAG(quantity, 1, 0) OVER (PARTITION BY product ORDER BY time_stamp) difference
FROM logistics
)
SELECT c1.*
FROM cte c1
WHERE EXISTS (
SELECT 1
FROM cte c2
WHERE c2.product = c1.product AND c2.difference < 0
)
ORDER BY c1.product, c1.time_stamp;
| time_stamp | product | difference |
|---|---|---|
| 2020-01-15 | Product_C | 350 |
| 2020-01-16 | Product_C | -100 |
| 2020-01-17 | Product_C | 130 |