add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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?.
--I have a status table like so, attributes not relevant to the question are omitted:

create table t(id, created, value)as values
(1, '2024-06-24T13:01:00'::timestamp, 'error')
,(2, '2024-06-24T13:02:00', 'ok')
,(3, '2024-06-24T13:03:00', 'warning')
,(4, '2024-06-24T13:04:00', 'error')
,(5, '2024-06-24T13:05:00', 'error')
,(6, '2024-06-24T13:05:30', 'error')
,(7, '2024-06-24T13:06:00', 'ok')
,(8, '2024-06-24T13:07:00', 'error')
,(9, '2024-06-24T13:07:30', 'error')
,(10, '2024-06-24T13:08:00', 'warning')
,(11, '2024-06-24T13:09:00', 'error')
;
/*
Task at Hand
I'd like to collapse the table into a bloc-like view, where the "error" blocs
(1, 4-6, 8-9, 11) are collapsed into a single line,
ut with the respective before and after states and timestamps also included like so:

error_first_occurance value_before timestamp_before value_after timestamp_after
2024-06-24T13:01:00 NULL NULL ok 2024-06-24T13:02:00
2024-06-24T13:04:00 warning 2024-06-24T13:03:00 ok 2024-06-24T13:06:00
2024-06-24T13:07:00 ok 2024-06-24T13:06:00 warning 2024-06-24T13:08:00
2024-06-24T13:09:00 warning 2024-06-24T13:08:00 NULL NULL
*/

SELECT 11
select distinct on(island_n)
first_value(created) over w2 as error_first_occurance
,first_value(value_before) over w2 as value_before
,first_value(timestamp_before)over w2 as timestamp_before
,last_value(value_after) over w2 as value_after
,last_value(timestamp_after) over w2 as timestamp_after
from(select*,count(*)filter(where value<>'error')over w1 as island_n
,value='error'
and value is distinct from lag(value)over w1 as is_starting_island
,lag(value) over w1 as value_before
,lag(created) over w1 as timestamp_before
,lead(value) over w1 as value_after
,lead(created)over w1 as timestamp_after
from t window w1 as(order by created) )_
where value='error'
window w2 as(partition by island_n order by created
rows between unbounded preceding and unbounded following)
order by island_n,1;
error_first_occurance value_before timestamp_before value_after timestamp_after
2024-06-24 13:01:00 null null ok 2024-06-24 13:02:00
2024-06-24 13:04:00 warning 2024-06-24 13:03:00 ok 2024-06-24 13:06:00
2024-06-24 13:07:00 ok 2024-06-24 13:06:00 warning 2024-06-24 13:08:00
2024-06-24 13:09:00 warning 2024-06-24 13:08:00 null null 
SELECT 4
WITH a AS (
SELECT
ROW_NUMBER() OVER (ORDER BY coalesce(t1.id,-1)),
t1.value AS value_before,
t1.created AS timestamp_before,
t2.created AS error_first_occurrence,
t2.value,
t2.created
FROM
t t1
FULL JOIN t t2 ON t1.id+1 = t2.id
WHERE
(t1.value = 'error' AND t2.value IS DISTINCT FROM 'error')
OR (t1.value IS DISTINCT FROM 'error' AND t2.value = 'error')
)
SELECT
a1.error_first_occurrence,
a1.value_before,
a1.timestamp_before,
a2.value AS value_after,
a2.created AS timestamp_after
FROM
a a1
JOIN a a2 ON a1.row_number+1 = a2.row_number
WHERE
a1.value = 'error';
error_first_occurrence value_before timestamp_before value_after timestamp_after
2024-06-24 13:01:00 null null ok 2024-06-24 13:02:00
2024-06-24 13:04:00 warning 2024-06-24 13:03:00 ok 2024-06-24 13:06:00
2024-06-24 13:07:00 ok 2024-06-24 13:06:00 warning 2024-06-24 13:08:00
2024-06-24 13:09:00 warning 2024-06-24 13:08:00 null null 
SELECT 4
select setseed(.42);

insert into t
select n
,'2024-06-24T13:09:00'::timestamp+n*'30s'::interval
--,now()-random()*'2 days'::interval
,('{error,ok,warning}'::text[])[(1+random()*2)::int]
from generate_series(12,5e5)n;

create index idx1 on t(id)with(fillfactor=100);
create index idx2 on t(id,value)with(fillfactor=100);
setseed 
SELECT 1

INSERT 0 499989

CREATE INDEX

CREATE INDEX
cluster verbose t using idx1;
CLUSTER
vacuum analyze t;
VACUUM
--deallocate knoep;
prepare knoep as
WITH a AS (
SELECT
ROW_NUMBER() OVER (ORDER BY coalesce(t1.id,-1)),
t1.value AS value_before,
t1.created AS timestamp_before,
t2.created AS error_first_occurrence,
t2.value,
t2.created
FROM
t t1
FULL JOIN t t2 ON t1.id+1 = t2.id
WHERE
(t1.value = 'error' AND t2.value IS DISTINCT FROM 'error')
OR (t1.value IS DISTINCT FROM 'error' AND t2.value = 'error')
)
SELECT
a1.error_first_occurrence,
a1.value_before,
a1.timestamp_before,
a2.value AS value_after,
a2.created AS timestamp_after
FROM
a a1
JOIN a a2 ON a1.row_number+1 = a2.row_number
WHERE
a1.value = 'error'
ORDER BY 1;

--deallocate window_over_window;
prepare window_over_window as
select distinct on(island_n)
first_value(created) over w2 as error_first_occurance
,first_value(value_before) over w2 as value_before
,first_value(timestamp_before)over w2 as timestamp_before
PREPARE

PREPARE

CREATE TABLE

TRUNCATE TABLE

DO
variant avg
knoep 00:00:01.223106
window_over_window 00:00:01.278612 
SELECT 2
id variant duration
9 knoep 00:00:01.146066
11 knoep 00:00:01.151102
3 knoep 00:00:01.18607
5 knoep 00:00:01.186097
2 window_over_window 00:00:01.226657
10 window_over_window 00:00:01.23627
12 window_over_window 00:00:01.256756
4 window_over_window 00:00:01.286488
6 window_over_window 00:00:01.313857
7 knoep 00:00:01.317059
8 window_over_window 00:00:01.351643
1 knoep 00:00:01.352242 
SELECT 12