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();
CREATE TABLE Projected_stock
(
Material INT,
Date date,
Stock VARCHAR(512),
Demand VARCHAR(512),
Supply VARCHAR(512),
Projected_Stock INT,
Target_Stock INT
);
INSERT INTO Projected_stock (Material,Date, Stock, Demand, Supply, Projected_Stock, Target_Stock) VALUES
('123456', '24/06/2024', '60', '0', '0', '60', '24'),
('123456', '26/06/2024', '0', '4', '0', '56', '24'),
('123456', '27/06/2024', '0', '4', '0', '52', '24'),
('123456', '02/07/2024', '0', '0', '10', '62', '24'),
('123456', '04/07/2024', '0', '0', '10', '72', '24'),
('123456', '04/07/2024', '0', '0', '1', '73', '24'),
('123456', '04/07/2024', '0', '1', '0', '72', '24'),
('123456', '04/07/2024', '0', '1', '0', '71', '24'),
('123456', '04/07/2024', '0', '4', '0', '67', '24'),
('123456', '04/07/2024', '0', '4', '0', '63', '24'),
('123456', '05/07/2024', '0', '4', '0', '59', '24'),
('123456', '11/07/2024', '0', '0', '11', '70', '24'),
('123456', '13/07/2024', '0', '1', '0', '69', '24'),
('123456', '13/07/2024', '0', '1', '0', '68', '24'),
('123456', '13/07/2024', '0', '1', '0', '67', '24'),
('123456', '14/07/2024', '0', '4', '0', '63', '24'),
('123456', '16/07/2024', '0', '4', '0', '59', '24'),
('123456', '18/07/2024', '0', '0', '11', '70', '24'),
('123456', '18/07/2024', '0', '4', '0', '66', '24'),
('123456', '19/07/2024', '0', '1', '0', '65', '24'),
('123456', '19/07/2024', '0', '1', '0', '64', '24'),
('123456', '21/07/2024', '0', '4', '0', '60', '24'),
('123456', '21/07/2024', '0', '1', '0', '59', '24'),
('123456', '21/07/2024', '0', '1', '0', '58', '24'),
CREATE TABLE
INSERT 0 64
| material | date | stock | demand | supply | projected_stock | target_stock |
|---|---|---|---|---|---|---|
| 123456 | 2024-06-24 | 60 | 0 | 0 | 60 | 24 |
| 123456 | 2024-06-26 | 0 | 4 | 0 | 56 | 24 |
| 123456 | 2024-06-27 | 0 | 4 | 0 | 52 | 24 |
| 123456 | 2024-07-02 | 0 | 0 | 10 | 62 | 24 |
| 123456 | 2024-07-04 | 0 | 0 | 10 | 72 | 24 |
| 123456 | 2024-07-04 | 0 | 0 | 1 | 73 | 24 |
| 123456 | 2024-07-04 | 0 | 1 | 0 | 72 | 24 |
| 123456 | 2024-07-04 | 0 | 1 | 0 | 71 | 24 |
| 123456 | 2024-07-04 | 0 | 4 | 0 | 67 | 24 |
| 123456 | 2024-07-04 | 0 | 4 | 0 | 63 | 24 |
| 123456 | 2024-07-05 | 0 | 4 | 0 | 59 | 24 |
| 123456 | 2024-07-11 | 0 | 0 | 11 | 70 | 24 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 69 | 24 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 68 | 24 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 67 | 24 |
| 123456 | 2024-07-14 | 0 | 4 | 0 | 63 | 24 |
| 123456 | 2024-07-16 | 0 | 4 | 0 | 59 | 24 |
| 123456 | 2024-07-18 | 0 | 0 | 11 | 70 | 24 |
| 123456 | 2024-07-18 | 0 | 4 | 0 | 66 | 24 |
| 123456 | 2024-07-19 | 0 | 1 | 0 | 65 | 24 |
| 123456 | 2024-07-19 | 0 | 1 | 0 | 64 | 24 |
| 123456 | 2024-07-21 | 0 | 4 | 0 | 60 | 24 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 59 | 24 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 58 | 24 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 57 | 24 |
| 123456 | 2024-07-25 | 0 | 0 | 9 | 66 | 24 |
| 123456 | 2024-07-25 | 0 | 4 | 0 | 62 | 24 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 71 | 24 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 80 | 24 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 89 | 24 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 98 | 24 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 107 | 24 |
| 123457 | 2024-06-24 | 30 | 0 | 0 | 30 | 24 |
| 123457 | 2024-06-26 | 0 | 4 | 0 | 26 | 24 |
| 123457 | 2024-06-27 | 0 | 4 | 0 | 22 | 24 |
| 123457 | 2024-07-02 | 0 | 0 | 10 | 32 | 24 |
| 123457 | 2024-07-04 | 0 | 0 | 10 | 42 | 24 |
| 123457 | 2024-07-04 | 0 | 0 | 1 | 43 | 24 |
| 123457 | 2024-07-04 | 0 | 1 | 0 | 42 | 24 |
| 123457 | 2024-07-04 | 0 | 1 | 0 | 41 | 24 |
| 123457 | 2024-07-04 | 0 | 4 | 0 | 37 | 24 |
| 123457 | 2024-07-04 | 0 | 4 | 0 | 33 | 24 |
| 123457 | 2024-07-05 | 0 | 4 | 0 | 29 | 24 |
| 123457 | 2024-07-11 | 0 | 0 | 11 | 40 | 24 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 39 | 24 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 38 | 24 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 37 | 24 |
| 123457 | 2024-07-14 | 0 | 4 | 0 | 33 | 24 |
| 123457 | 2024-07-16 | 0 | 4 | 0 | 29 | 24 |
| 123457 | 2024-07-18 | 0 | 0 | 11 | 40 | 24 |
| 123457 | 2024-07-18 | 0 | 4 | 0 | 36 | 24 |
| 123457 | 2024-07-19 | 0 | 1 | 0 | 35 | 24 |
| 123457 | 2024-07-19 | 0 | 1 | 0 | 34 | 24 |
| 123457 | 2024-07-21 | 0 | 4 | 0 | 30 | 24 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 29 | 24 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 28 | 24 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 27 | 24 |
| 123457 | 2024-07-25 | 0 | 0 | 9 | 36 | 24 |
| 123457 | 2024-07-25 | 0 | 4 | 0 | 32 | 24 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 41 | 24 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 50 | 24 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 59 | 24 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 68 | 24 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 77 | 24 |
SELECT 64
| material | date | stock | demand | supply | projected_stock | target_stock | overstock |
|---|---|---|---|---|---|---|---|
| 123456 | 2024-06-24 | 60 | 0 | 0 | 60 | 24 | 1 |
| 123456 | 2024-06-26 | 0 | 4 | 0 | 56 | 24 | 1 |
| 123456 | 2024-06-27 | 0 | 4 | 0 | 52 | 24 | 1 |
| 123456 | 2024-07-02 | 0 | 0 | 10 | 62 | 24 | 1 |
| 123456 | 2024-07-04 | 0 | 0 | 10 | 72 | 24 | 1 |
| 123456 | 2024-07-04 | 0 | 0 | 1 | 73 | 24 | 1 |
| 123456 | 2024-07-04 | 0 | 1 | 0 | 72 | 24 | 1 |
| 123456 | 2024-07-04 | 0 | 1 | 0 | 71 | 24 | 1 |
| 123456 | 2024-07-04 | 0 | 4 | 0 | 67 | 24 | 1 |
| 123456 | 2024-07-04 | 0 | 4 | 0 | 63 | 24 | 1 |
| 123456 | 2024-07-05 | 0 | 4 | 0 | 59 | 24 | 1 |
| 123456 | 2024-07-11 | 0 | 0 | 11 | 70 | 24 | 1 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 69 | 24 | 1 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 68 | 24 | 1 |
| 123456 | 2024-07-13 | 0 | 1 | 0 | 67 | 24 | 1 |
| 123456 | 2024-07-14 | 0 | 4 | 0 | 63 | 24 | 1 |
| 123456 | 2024-07-16 | 0 | 4 | 0 | 59 | 24 | 1 |
| 123456 | 2024-07-18 | 0 | 0 | 11 | 70 | 24 | 1 |
| 123456 | 2024-07-18 | 0 | 4 | 0 | 66 | 24 | 1 |
| 123456 | 2024-07-19 | 0 | 1 | 0 | 65 | 24 | 1 |
| 123456 | 2024-07-19 | 0 | 1 | 0 | 64 | 24 | 1 |
| 123456 | 2024-07-21 | 0 | 4 | 0 | 60 | 24 | 1 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 59 | 24 | 1 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 58 | 24 | 1 |
| 123456 | 2024-07-21 | 0 | 1 | 0 | 57 | 24 | 1 |
| 123456 | 2024-07-25 | 0 | 0 | 9 | 66 | 24 | 1 |
| 123456 | 2024-07-25 | 0 | 4 | 0 | 62 | 24 | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 71 | 24 | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 80 | 24 | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 89 | 24 | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 98 | 24 | 1 |
| 123456 | 2024-07-30 | 0 | 0 | 9 | 107 | 24 | 1 |
| 123457 | 2024-06-24 | 30 | 0 | 0 | 30 | 24 | 0 |
| 123457 | 2024-06-26 | 0 | 4 | 0 | 26 | 24 | 0 |
| 123457 | 2024-06-27 | 0 | 4 | 0 | 22 | 24 | 0 |
| 123457 | 2024-07-02 | 0 | 0 | 10 | 32 | 24 | 0 |
| 123457 | 2024-07-04 | 0 | 0 | 10 | 42 | 24 | 0 |
| 123457 | 2024-07-04 | 0 | 0 | 1 | 43 | 24 | 0 |
| 123457 | 2024-07-04 | 0 | 1 | 0 | 42 | 24 | 0 |
| 123457 | 2024-07-04 | 0 | 1 | 0 | 41 | 24 | 0 |
| 123457 | 2024-07-04 | 0 | 4 | 0 | 37 | 24 | 0 |
| 123457 | 2024-07-04 | 0 | 4 | 0 | 33 | 24 | 0 |
| 123457 | 2024-07-05 | 0 | 4 | 0 | 29 | 24 | 0 |
| 123457 | 2024-07-11 | 0 | 0 | 11 | 40 | 24 | 0 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 39 | 24 | 0 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 38 | 24 | 0 |
| 123457 | 2024-07-13 | 0 | 1 | 0 | 37 | 24 | 0 |
| 123457 | 2024-07-14 | 0 | 4 | 0 | 33 | 24 | 0 |
| 123457 | 2024-07-16 | 0 | 4 | 0 | 29 | 24 | 0 |
| 123457 | 2024-07-18 | 0 | 0 | 11 | 40 | 24 | 0 |
| 123457 | 2024-07-18 | 0 | 4 | 0 | 36 | 24 | 0 |
| 123457 | 2024-07-19 | 0 | 1 | 0 | 35 | 24 | 0 |
| 123457 | 2024-07-19 | 0 | 1 | 0 | 34 | 24 | 0 |
| 123457 | 2024-07-21 | 0 | 4 | 0 | 30 | 24 | 0 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 29 | 24 | 0 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 28 | 24 | 0 |
| 123457 | 2024-07-21 | 0 | 1 | 0 | 27 | 24 | 0 |
| 123457 | 2024-07-25 | 0 | 0 | 9 | 36 | 24 | 0 |
| 123457 | 2024-07-25 | 0 | 4 | 0 | 32 | 24 | 0 |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 41 | 24 | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 50 | 24 | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 59 | 24 | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 68 | 24 | ? |
| 123457 | 2024-07-30 | 0 | 0 | 9 | 77 | 24 | ? |
SELECT 64