Postgres 16

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 IF NOT EXISTS items (
    article character varying PRIMARY KEY 
);
CREATE TABLE IF NOT EXISTS self_prices_history (
	updated_at timestamp NOT NULL,
	article character varying NOT NULL,
	value numeric,
	CONSTRAINT self_prices_history_pk PRIMARY KEY (updated_at, article)
);
CREATE TABLE IF NOT EXISTS client_prices_history (
	updated_at timestamp NOT NULL,
	article character varying NOT NULL,
	value numeric,
	CONSTRAINT client_prices_history_pk PRIMARY KEY (updated_at, article)
);

INSERT INTO items VALUES ('001'), ('002'), ('003'), ('004');

INSERT INTO client_prices_history VALUES 
('2024-07-16 09:10:19.950802', '001', 1100),
('2024-07-16 15:10:05.299459', '001', 1147),
('2024-07-17 21:10:16.538367', '001', 1142),
('2024-07-19 09:10:20.937887', '001', 1136),
('2024-07-19 15:15:38.553837', '001', 1133),
('2024-07-20 15:10:05.43201', '001', 1042),
('2024-08-25 15:10:22.056807', '002', 1181),
('2024-08-27 21:10:38.562463', '002', 1122),
('2024-08-29 15:10:28.421389', '002', 1120),
('2024-08-30 21:10:21.551706', '002', 1105),
('2024-09-02 15:10:05.561434', '002', 1107),
('2024-08-07 09:31:15.799588', '003', 6209),
('2024-08-07 15:10:05.359173', '003', 6231),
('2024-08-08 03:10:11.48272', '003', 3993),
('2024-08-10 15:10:20.400104', '003', 6268),
('2024-08-11 15:10:08.059679', '003', 6283),
('2024-08-12 15:10:04.702603', '003', 6285);

INSERT INTO self_prices_history VALUES 
('2023-12-22 22:00:06.116714', '001', 1604),
('2024-07-12 21:00:16.216461', '001', 1652),
('2024-08-02 18:00:18.123571', '001', 1710),
('2024-08-10 15:30:52.816604', '001', 1736),
('2024-09-02 11:00:16.306556', '001', 1790),
('2023-07-14 09:59:32.623492', '002', 2031),
('2023-08-25 09:06:38.553189', '002', 2031),
('2023-08-27 12:22:25.965515', '002', 2031),
('2023-08-30 09:03:19.702678', '002', 2031),
('2023-08-31 09:02:24.267454', '002', 2031),
('2023-07-14 09:59:32.582678', '003', 7207),
('2023-08-18 08:56:21.850859', '003', 7568),
('2023-08-25 09:06:38.553189', '003', 7568),
('2023-08-27 12:22:25.965515', '003', 7568),
('2023-08-28 08:59:35.525273', '003', 7568);

SELECT * FROM items;
SELECT * FROM client_prices_history;
SELECT * FROM self_prices_history;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 4

INSERT 0 17

INSERT 0 15

article
001
002
003
004

SELECT 4

updated_at	article	value
2024-07-16 09:10:19.950802	001	1100
2024-07-16 15:10:05.299459	001	1147
2024-07-17 21:10:16.538367	001	1142
2024-07-19 09:10:20.937887	001	1136
2024-07-19 15:15:38.553837	001	1133
2024-07-20 15:10:05.43201	001	1042
2024-08-25 15:10:22.056807	002	1181
2024-08-27 21:10:38.562463	002	1122
2024-08-29 15:10:28.421389	002	1120
2024-08-30 21:10:21.551706	002	1105
2024-09-02 15:10:05.561434	002	1107
2024-08-07 09:31:15.799588	003	6209
2024-08-07 15:10:05.359173	003	6231
2024-08-08 03:10:11.48272	003	3993
2024-08-10 15:10:20.400104	003	6268
2024-08-11 15:10:08.059679	003	6283
2024-08-12 15:10:04.702603	003	6285

SELECT 17

updated_at	article	value
2023-12-22 22:00:06.116714	001	1604
2024-07-12 21:00:16.216461	001	1652
2024-08-02 18:00:18.123571	001	1710
2024-08-10 15:30:52.816604	001	1736
2024-09-02 11:00:16.306556	001	1790
2023-07-14 09:59:32.623492	002	2031
2023-08-25 09:06:38.553189	002	2031
2023-08-27 12:22:25.965515	002	2031
2023-08-30 09:03:19.702678	002	2031
2023-08-31 09:02:24.267454	002	2031
2023-07-14 09:59:32.582678	003	7207
2023-08-18 08:56:21.850859	003	7568
2023-08-25 09:06:38.553189	003	7568
2023-08-27 12:22:25.965515	003	7568
2023-08-28 08:59:35.525273	003	7568

SELECT 15

with ds AS (
SELECT generate_series(
	'2023-01-01',
	'2025-12-31',
	'1 day'::interval
)::date AS date
),
items AS (
	SELECT article FROM items 
),
all_combinations AS (
    SELECT ds.date, items.article
    FROM ds
    CROSS JOIN items
),
client_prices AS (
	SELECT 
		date(updated_at) as date,
		article,
		value as client_price
	FROM client_prices_history
),
self_prices AS (
	SELECT 
		date(updated_at) as date,
		article,
		value as self_price
	FROM self_prices_history
),
combined_prices AS (
    SELECT 
        all_combinations.date,
        all_combinations.article,
        cp.client_price,
        sp.self_price,
        MAX(cp.client_price) OVER (
			PARTITION BY all_combinations.article ORDER BY all_combinations.date
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_client_price,
        MAX(sp.self_price) OVER (
			PARTITION BY all_combinations.article ORDER BY all_combinations.date
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_self_price,
		COUNT(*) OVER (PARTITION BY all_combinations.article) as count_rows
    FROM all_combinations
    LEFT JOIN self_prices sp
        ON all_combinations.date = sp.date
        AND all_combinations.article = sp.article
    LEFT JOIN client_prices cp
        ON all_combinations.date = cp.date
        AND all_combinations.article = cp.article
)
SELECT 
    date,
    article,
    COALESCE(filled_client_price, 0) AS client_price,
    COALESCE(filled_self_price, 0) AS self_price,
	count_rows
FROM combined_prices
WHERE date between '2024-07-14' and '2024-08-30'
-- ORDER BY date, article;
ORDER BY 2,1;

date	article	client_price	self_price	count_rows
2024-07-14	001	0	1652	1098
2024-07-15	001	0	1652	1098
2024-07-16	001	1100	1652	1098
2024-07-16	001	1147	1652	1098
2024-07-17	001	1147	1652	1098
2024-07-18	001	1147	1652	1098
2024-07-19	001	1147	1652	1098
2024-07-19	001	1147	1652	1098
2024-07-20	001	1147	1652	1098
2024-07-21	001	1147	1652	1098
2024-07-22	001	1147	1652	1098
2024-07-23	001	1147	1652	1098
2024-07-24	001	1147	1652	1098
2024-07-25	001	1147	1652	1098
2024-07-26	001	1147	1652	1098
2024-07-27	001	1147	1652	1098
2024-07-28	001	1147	1652	1098
2024-07-29	001	1147	1652	1098
2024-07-30	001	1147	1652	1098
2024-07-31	001	1147	1652	1098
2024-08-01	001	1147	1652	1098
2024-08-02	001	1147	1710	1098
2024-08-03	001	1147	1710	1098
2024-08-04	001	1147	1710	1098
2024-08-05	001	1147	1710	1098
2024-08-06	001	1147	1710	1098
2024-08-07	001	1147	1710	1098
2024-08-08	001	1147	1710	1098
2024-08-09	001	1147	1710	1098
2024-08-10	001	1147	1736	1098
2024-08-11	001	1147	1736	1098
2024-08-12	001	1147	1736	1098
2024-08-13	001	1147	1736	1098
2024-08-14	001	1147	1736	1098
2024-08-15	001	1147	1736	1098
2024-08-16	001	1147	1736	1098
2024-08-17	001	1147	1736	1098
2024-08-18	001	1147	1736	1098
2024-08-19	001	1147	1736	1098
2024-08-20	001	1147	1736	1098
2024-08-21	001	1147	1736	1098
2024-08-22	001	1147	1736	1098
2024-08-23	001	1147	1736	1098
2024-08-24	001	1147	1736	1098
2024-08-25	001	1147	1736	1098
2024-08-26	001	1147	1736	1098
2024-08-27	001	1147	1736	1098
2024-08-28	001	1147	1736	1098
2024-08-29	001	1147	1736	1098
2024-08-30	001	1147	1736	1098
2024-07-14	002	0	2031	1096
2024-07-15	002	0	2031	1096
2024-07-16	002	0	2031	1096
2024-07-17	002	0	2031	1096
2024-07-18	002	0	2031	1096
2024-07-19	002	0	2031	1096
2024-07-20	002	0	2031	1096
2024-07-21	002	0	2031	1096
2024-07-22	002	0	2031	1096
2024-07-23	002	0	2031	1096
2024-07-24	002	0	2031	1096
2024-07-25	002	0	2031	1096
2024-07-26	002	0	2031	1096
2024-07-27	002	0	2031	1096
2024-07-28	002	0	2031	1096
2024-07-29	002	0	2031	1096
2024-07-30	002	0	2031	1096
2024-07-31	002	0	2031	1096
2024-08-01	002	0	2031	1096
2024-08-02	002	0	2031	1096
2024-08-03	002	0	2031	1096
2024-08-04	002	0	2031	1096
2024-08-05	002	0	2031	1096
2024-08-06	002	0	2031	1096
2024-08-07	002	0	2031	1096
2024-08-08	002	0	2031	1096
2024-08-09	002	0	2031	1096
2024-08-10	002	0	2031	1096
2024-08-11	002	0	2031	1096
2024-08-12	002	0	2031	1096
2024-08-13	002	0	2031	1096
2024-08-14	002	0	2031	1096
2024-08-15	002	0	2031	1096
2024-08-16	002	0	2031	1096
2024-08-17	002	0	2031	1096
2024-08-18	002	0	2031	1096
2024-08-19	002	0	2031	1096
2024-08-20	002	0	2031	1096
2024-08-21	002	0	2031	1096
2024-08-22	002	0	2031	1096
2024-08-23	002	0	2031	1096
2024-08-24	002	0	2031	1096
2024-08-25	002	1181	2031	1096
2024-08-26	002	1181	2031	1096
2024-08-27	002	1181	2031	1096
2024-08-28	002	1181	2031	1096
2024-08-29	002	1181	2031	1096
2024-08-30	002	1181	2031	1096
2024-07-14	003	0	7568	1097
2024-07-15	003	0	7568	1097
2024-07-16	003	0	7568	1097
2024-07-17	003	0	7568	1097
2024-07-18	003	0	7568	1097
2024-07-19	003	0	7568	1097
2024-07-20	003	0	7568	1097
2024-07-21	003	0	7568	1097
2024-07-22	003	0	7568	1097
2024-07-23	003	0	7568	1097
2024-07-24	003	0	7568	1097
2024-07-25	003	0	7568	1097
2024-07-26	003	0	7568	1097
2024-07-27	003	0	7568	1097
2024-07-28	003	0	7568	1097
2024-07-29	003	0	7568	1097
2024-07-30	003	0	7568	1097
2024-07-31	003	0	7568	1097
2024-08-01	003	0	7568	1097
2024-08-02	003	0	7568	1097
2024-08-03	003	0	7568	1097
2024-08-04	003	0	7568	1097
2024-08-05	003	0	7568	1097
2024-08-06	003	0	7568	1097
2024-08-07	003	6209	7568	1097
2024-08-07	003	6231	7568	1097
2024-08-08	003	6231	7568	1097
2024-08-09	003	6231	7568	1097
2024-08-10	003	6268	7568	1097
2024-08-11	003	6283	7568	1097
2024-08-12	003	6285	7568	1097
2024-08-13	003	6285	7568	1097
2024-08-14	003	6285	7568	1097
2024-08-15	003	6285	7568	1097
2024-08-16	003	6285	7568	1097
2024-08-17	003	6285	7568	1097
2024-08-18	003	6285	7568	1097
2024-08-19	003	6285	7568	1097
2024-08-20	003	6285	7568	1097
2024-08-21	003	6285	7568	1097
2024-08-22	003	6285	7568	1097
2024-08-23	003	6285	7568	1097
2024-08-24	003	6285	7568	1097
2024-08-25	003	6285	7568	1097
2024-08-26	003	6285	7568	1097
2024-08-27	003	6285	7568	1097
2024-08-28	003	6285	7568	1097
2024-08-29	003	6285	7568	1097
2024-08-30	003	6285	7568	1097
2024-07-14	004	0	0	1096
2024-07-15	004	0	0	1096
2024-07-16	004	0	0	1096
2024-07-17	004	0	0	1096
2024-07-18	004	0	0	1096
2024-07-19	004	0	0	1096
2024-07-20	004	0	0	1096
2024-07-21	004	0	0	1096
2024-07-22	004	0	0	1096
2024-07-23	004	0	0	1096
2024-07-24	004	0	0	1096
2024-07-25	004	0	0	1096
2024-07-26	004	0	0	1096
2024-07-27	004	0	0	1096
2024-07-28	004	0	0	1096
2024-07-29	004	0	0	1096
2024-07-30	004	0	0	1096
2024-07-31	004	0	0	1096
2024-08-01	004	0	0	1096
2024-08-02	004	0	0	1096
2024-08-03	004	0	0	1096
2024-08-04	004	0	0	1096
2024-08-05	004	0	0	1096
2024-08-06	004	0	0	1096
2024-08-07	004	0	0	1096
2024-08-08	004	0	0	1096
2024-08-09	004	0	0	1096
2024-08-10	004	0	0	1096
2024-08-11	004	0	0	1096
2024-08-12	004	0	0	1096
2024-08-13	004	0	0	1096
2024-08-14	004	0	0	1096
2024-08-15	004	0	0	1096
2024-08-16	004	0	0	1096
2024-08-17	004	0	0	1096
2024-08-18	004	0	0	1096
2024-08-19	004	0	0	1096
2024-08-20	004	0	0	1096
2024-08-21	004	0	0	1096
2024-08-22	004	0	0	1096
2024-08-23	004	0	0	1096
2024-08-24	004	0	0	1096
2024-08-25	004	0	0	1096
2024-08-26	004	0	0	1096
2024-08-27	004	0	0	1096
2024-08-28	004	0	0	1096
2024-08-29	004	0	0	1096
2024-08-30	004	0	0	1096

SELECT 195