Postgres 15

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();

version
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE TABLE stock (

dt timestamp,

timestamp bigint,

open float,

high float,

low float,

close float,

volume float

);

INSERT INTO stock (dt, timestamp, open, high,

low, close, volume) VALUES

('2023.03.22 11:30:00',1679484600000,1.83554,1.83567,1.83522,1.83526,174),

('2023.03.22 11:31:00',1679484660000,1.83527,1.83551,1.83524,1.83546,114),

('2023.03.22 11:32:00',1679484720000,1.83545,1.83554,1.83525,1.8354,143),

('2023.03.22 11:33:00',1679484780000,1.83545,1.83554,1.83531,1.83537,145),

('2023.03.22 11:34:00',1679484840000,1.83536,1.83544,1.83516,1.83518,135),

('2023.03.22 11:35:00',1679484900000,1.83517,1.83517,1.83481,1.83481,131),

('2023.03.22 11:36:00',1679484960000,1.83481,1.83483,1.83467,1.83478,102),

('2023.03.22 11:37:00',1679485020000,1.83474,1.83474,1.83416,1.83422,119),

('2023.03.22 11:38:00',1679485080000,1.83421,1.83437,1.83406,1.83437,116),

('2023.03.22 11:39:00',1679485140000,1.83437,1.83459,1.83429,1.83453,116),

('2023.03.22 11:40:00',1679485200000,1.83452,1.83492,1.83444,1.83469,122),

('2023.03.22 11:41:00',1679485260000,1.83469,1.83492,1.83464,1.83469,103),

('2023.03.22 11:42:00',1679485320000,1.83467,1.83467,1.8341,1.83419,108),

('2023.03.22 11:43:00',1679485380000,1.83419,1.83427,1.83389,1.83411,101),

('2023.03.22 11:44:00',1679485440000,1.83407,1.83462,1.83405,1.83451,168),

('2023.03.22 11:45:00',1679485500000,1.83451,1.83466,1.83446,1.83464,81),

('2023.03.22 11:46:00',1679485560000,1.83463,1.83463,1.83433,1.8344,96),

('2023.03.22 11:47:00',1679485620000,1.83439,1.83493,1.83435,1.83492,91),

('2023.03.22 11:48:00',1679485680000,1.83491,1.83491,1.83456,1.83468,129),

('2023.03.22 11:49:00',1679485740000,1.83466,1.83484,1.83464,1.83473,97),

('2023.03.22 11:50:00',1679485800000,1.83471,1.83525,1.83471,1.83509,117),

('2023.03.22 11:51:00',1679485860000,1.8351,1.83524,1.83508,1.83515,91),

('2023.03.22 11:52:00',1679485920000,1.83515,1.83553,1.83515,1.83547,125),

CREATE TABLE

INSERT 0 24

dt	timestamp	open	high	low	close	volume
2023-03-22 11:30:00	1679484600000	1.83554	1.83567	1.83522	1.83526	174
2023-03-22 11:31:00	1679484660000	1.83527	1.83551	1.83524	1.83546	114
2023-03-22 11:32:00	1679484720000	1.83545	1.83554	1.83525	1.8354	143
2023-03-22 11:33:00	1679484780000	1.83545	1.83554	1.83531	1.83537	145
2023-03-22 11:34:00	1679484840000	1.83536	1.83544	1.83516	1.83518	135
2023-03-22 11:35:00	1679484900000	1.83517	1.83517	1.83481	1.83481	131
2023-03-22 11:36:00	1679484960000	1.83481	1.83483	1.83467	1.83478	102
2023-03-22 11:37:00	1679485020000	1.83474	1.83474	1.83416	1.83422	119
2023-03-22 11:38:00	1679485080000	1.83421	1.83437	1.83406	1.83437	116
2023-03-22 11:39:00	1679485140000	1.83437	1.83459	1.83429	1.83453	116
2023-03-22 11:40:00	1679485200000	1.83452	1.83492	1.83444	1.83469	122
2023-03-22 11:41:00	1679485260000	1.83469	1.83492	1.83464	1.83469	103
2023-03-22 11:42:00	1679485320000	1.83467	1.83467	1.8341	1.83419	108
2023-03-22 11:43:00	1679485380000	1.83419	1.83427	1.83389	1.83411	101
2023-03-22 11:44:00	1679485440000	1.83407	1.83462	1.83405	1.83451	168
2023-03-22 11:45:00	1679485500000	1.83451	1.83466	1.83446	1.83464	81
2023-03-22 11:46:00	1679485560000	1.83463	1.83463	1.83433	1.8344	96
2023-03-22 11:47:00	1679485620000	1.83439	1.83493	1.83435	1.83492	91
2023-03-22 11:48:00	1679485680000	1.83491	1.83491	1.83456	1.83468	129
2023-03-22 11:49:00	1679485740000	1.83466	1.83484	1.83464	1.83473	97
2023-03-22 11:50:00	1679485800000	1.83471	1.83525	1.83471	1.83509	117
2023-03-22 11:51:00	1679485860000	1.8351	1.83524	1.83508	1.83515	91
2023-03-22 11:52:00	1679485920000	1.83515	1.83553	1.83515	1.83547	125
2023-03-22 11:53:00	1679485980000	1.83548	1.83553	1.83541	1.83543	17

SELECT 24

timestamp	dt	open	high	low	close	volume
1679484600000	2023-03-22 11:30:00	1.83527	1.83567	1.83516	1.83546	711
1679484900000	2023-03-22 11:35:00	1.83421	1.83517	1.83406	1.83481	584
1679485200000	2023-03-22 11:40:00	1.83407	1.83492	1.83389	1.83469	602
1679485500000	2023-03-22 11:45:00	1.83439	1.83493	1.83433	1.83492	494
1679485800000	2023-03-22 11:50:00	1.83471	1.83553	1.83471	1.83547	350

SELECT 5