Postgres: как найти ближайший tsrange по метке времени за пределами диапазонов?

Решение зависит от точности того, что вы хотите.

Схема

Я предлагаю эти слегка адаптированные определения таблиц, чтобы упростить задачу, обеспечить целостность и повысить производительность:

CREATE TABLE supplier (
   supplier_id  serial PRIMARY KEY,
   supplier     text NOT NULL CHECK (length(title) < 280),
   type         service_type,
   duration     interval,
   geo_position geography(POINT,4326)
);

CREATE TABLE timeslot (
   timeslot_id  serial PRIMARY KEY,
   supplier_id  integer NOT NULL -- references supplier(id),
   slot_a       timestamptz NOT NULL,
   slot_z       timestamptz NOT NULL,
   CONSTRAINT   timeslot_range_valid CHECK (slot_a < slot_z)
   CONSTRAINT   timeslot_no_overlapping
     EXCLUDE USING gist (supplier_id WITH =, tstzrange(slot_a, slot_z) WITH &&)
);

CREATE INDEX timeslot_slot_z ON timeslot (supplier_id, slot_z);
CREATE INDEX supplier_geo_position_gist ON supplier USING gist (geo_position);

• Сохраните два столбца timestamptz slot_a и slot_z вместо столбца tstzrange slot и соответствующим образом измените ограничения. Теперь все диапазоны автоматически обрабатываются как включающие нижние и исключительные верхние границы по умолчанию, что позволяет избежать угловых ошибок и головной боли.

  Сопутствующее преимущество: всего 16 байтов для 2 timestamptz вместо 25 байтов (32 с дополнением) для tstzrange.

• Все запросы, которые у вас могли быть на slot, продолжают работать с tstzrange(slot_a, slot_z) в качестве замены.

• Добавьте индекс для (supplier_id, slot_z) для текущего запроса.
  И пространственный индекс для supplier.geo_position (который у вас, вероятно, уже есть).

  В зависимости от распределения данных в type, несколько частичных индексов для типов, распространенных в запросах, могут повысить производительность:

  CREATE INDEX supplier_geo_type_foo_gist ON supplier USING gist (geo_position)
  WHERE supplier = 'foo'::service_type;
  

Запрос/функция

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

CREATE FUNCTION f_suppliers_nearby(_type service_type, _lat text, _lon text, at_time timestamptz)
  RETURNS TABLE (supplier_id  int
               , name         text
               , duration     interval
               , geo_position geography(POINT,4326)
               , distance     float 
               , timeslot_id  int
               , slot_a       timestamptz
               , slot_z       timestamptz
               , time_dist    interval
   ) AS
$func$
   WITH sup_nearby AS (  -- find matching or later slot
      SELECT s.id, s.name, s.duration, s.geo_position
           , ST_Distance(ST_GeographyFromText('SRID=4326;POINT(' || _lat || ' ' || _lon || ')')
                          , geo_position) AS distance
           , t.timeslot_id, t.slot_a, t.slot_z
           , CASE WHEN t.slot_a IS NOT NULL
                  THEN GREATEST(t.slot_a - at_time, interval '0') END AS time_dist
      FROM   supplier s
      LEFT   JOIN LATERAL (
         SELECT *
         FROM   timeslot
         WHERE  supplier_id = supplier_id
         AND    slot_z > at_time + s.duration  -- excl. upper bound
         ORDER  BY slot_z
         LIMIT  1
         ) t ON true
      WHERE  s.type = _type
      ORDER  BY s.distance
      LIMIT  100
      )
   SELECT *
   FROM  (
      SELECT DISTINCT ON (supplier_id) *  -- 1 slot per supplier
      FROM  (
         TABLE sup_nearby  -- matching or later slot

         UNION ALL         -- earlier slot
         SELECT s.id, s.name, s.duration, s.geo_position
              , s.distance
              , t.timeslot_id, t.slot_a, t.slot_z
              , GREATEST(at_time - t.slot_a, interval '0') AS time_dist
         FROM   sup_nearby s
         CROSS  JOIN LATERAL (  -- this time CROSS JOIN!
            SELECT *
            FROM   timeslot
            WHERE  supplier_id = s.supplier_id
            AND    slot_z <= at_time  -- excl. upper bound
            ORDER  BY slot_z DESC
            LIMIT  1
            ) t
         WHERE  s.time_dist IS DISTINCT FROM interval '0'  -- exact matches are done
         ) sub
      ORDER  BY supplier_id, time_dist  -- pick temporally closest slot per supplier
   ) sub
   ORDER  BY time_dist, distance;  -- matches first, ordered by distance; then misses, ordered by time distance

$func$  LANGUAGE sql;

Я не использовал ваше представление supplier_slots и вместо этого оптимизировал его для повышения производительности. Вид все еще может быть удобным. Вы можете включить tstzrange(slot_a, slot_z) AS slot для обратной совместимости.

Базовый запрос для поиска 100 ближайших поставщиков — это задача из учебника «K ближайших соседей». Для этого хорошо подходит индекс GiST. Связанный:

• Как запросить все строки в радиусе 5 миль от моих координат?

Дополнительную задачу (найти ближайший во времени слот) можно разбить на две задачи: найти следующую более высокую и следующую более низкую строку. Основная функция этого решения состоит в наличии двух подзапросов с ORDER BY slot_z LIMIT 1 и ORDER BY slot_z DESC LIMIT 1, что приводит к двум очень быстрым сканированиям индекса.

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