Как эффективно самостоятельно присоединиться к одной и той же таблице несколько раз с порядком сортировки по возрастанию и убыванию?

Для каждого блока ошибок требуется информация из трех строк, поэтому наиболее простой способ — использовать два шага, каждый из которых объединяет по два. Вы можете сделать это либо с помощью самосоединений, либо с помощью оконных функций, таких как lag(). Остальное — просто правильно расположить строки. Вот реализация с самообъединениями, которая мне кажется предпочтительнее (более кратко):

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';

Обратите внимание на использование IS DISTINCT и COALESCE для обозначения NULL.

Я не думаю, что между этим и подобными подходами (например, использованием оконных функций) будет большая разница в производительности, но если это достаточно важно, вам обязательно стоит попробовать некоторые варианты, иногда у планировщика PostgreSQL возникают странные идеи.

Другим подходом было бы использование рекурсивного запроса, но я не думаю, что в данном случае это вам что-нибудь даст (кроме еще более непонятного синтаксиса). Однако они являются наиболее общим и мощным инструментом для решения подобных проблем (агрегирование по группам последовательных строк), поэтому, если вы продолжаете сталкиваться с чем-то подобным, возможно, вам захочется взглянуть.

Что касается индексов, я предполагаю, что id — это первичный ключ, поэтому самый важный индекс уже есть. Помимо этого, многостолбцовый индекс на id и value может помочь ускорить первоначальное самосоединение, но только если ваши блоки с ошибками и без ошибок достаточно велики, поскольку в противном случае большинство строк таблицы все равно придется сканировать. .

Это проблема пробелов и островов . Как уже отмечал @Knoep , вы можете использовать оконные функции:

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) и собирает значения из предшествующих и последующих строк. После этого для каждого острова требуется один набор первых/последних значений, используя , отличающийся от .

Это примерно так же быстро , как и четверное само-join, но не обязательно полагаться на два тихих предположения о том, что в столбце id нет пробелов и что он следует тому же порядку, что и created - что один из них терпит неудачу если ни одно из двух не выполняется. Режим «окно над окном» также не требует дополнительных усилий по поддержанию предварительно отсортированного содержимого без зазоров id.

Альтернативный подход был бы именно тем, что вы сделали, просто свернутым так, как вы хотели:

1. Берите все error.
2. Найдите их непосредственно предыдущую строку.
3. Оставьте только те, перед которыми стоит знак без error, чтобы сохранить только начало острова.
4. Найдите самый ранний не-error после них.

order by..limit 1 — это то, как боковым подзапросам удается легко найти одну строку, которая предшествует или следует за целевой строкой.

демо на db<>fiddle

select*from t as t1 
left join lateral 
(  select value   as value_before
         ,created as timestamp_before 
   from t as t2 
   where t2.created<=t1.created
     and t2.id<>t1.id
   order by created desc 
   limit 1)as s1 on true
left join lateral 
(  select value   as value_after
         ,created as timestamp_after
   from t as t2 
   where t2.created>=t1.created
     and t2.value<>'error'
   order by created asc
   limit 1)as s2 on true
where t1.value='error'
  and s1.value_before is distinct from 'error'

Лучше всего использовать индексы покрытия:

create index idx1 on t(created)include(id,value);
create index idx2 on t(value)  include(id,created);

Это позволяет ему выполнить 3 быстрых сканирования только индекса.