Cила PostgreSQL


    Встречаясь со сложной нетривиальной задачей по поиску и обработке данных, порой хочется решить ее в лоб. И хотя ты понимаешь, что решение возможно будет медленным или вообще нежизнеспособным, а знаний и опыта не хватает, чтобы решить ее по-настоящему, не нужно спешить. Важно понять, что СУБД были специально созданы для этого, и решать задачи, предназначенные для них, другими способами не стоит.

    Задача


    Поиск отелей с доступными номерами на конкретные даты некоторой группой людей.

    Проект


    Когда проект попал к нам в руки, поиск уже был реализован. Он работал медленно, очень медленно. А все потому, что расчеты и выборки велись не на стороне базы данных, а на стороне web-приложения: выбиралась тонна записей из разных таблиц, и в циклах подбирались и рассчитывались номера, фильтровались, сортировались и выводились постранично. Очень неэффективно. А приложение, к слову, написано на Ruby on Rails.
    Не надо так.

    Исходные данные


    Исходная схема данных (в примерах искусственно упрощена, чтобы влезть в ограничения sqlfiddle)


    Places — направления, курорты. Из полей — только название.


    Districts — районы. Каждое направление может иметь несколько районов. Поля: название и id направления.


    Properties — отели, могут быть привязаны к направлению или к конкретному району. Поля:

    • name — название
    • dest_type — тип полиморфной связи с направлением или районом («Place» или «District»)
    • dest_id — id связи с направлением или районом
    • stars — звездность (от 0 до 5)
    • currency — код валюты


    Property_arrival_rules — правила въезда в каждый отель. Поля:

    • arrival_date — дата заезда
    • property_id — id отеля
    • rule — тип правила (0 или 1), в зависимости от типа по разному рассчитывается дата выезда, подробнее в решении ниже
    • min_stay — минимальное количество ночей для проживания

    Отсутствие записи в таблице на конкретную дату означает, что въезд в этот день невозможен. Зачем хранится так? Все дело в типах правил въезда. Подробнее об этих типах в решении ниже.


    Rooms — номера в отелях, точнее типы номеров, т.к. например, 2-х комнатных одинаковых номеров может быть несколько в одном отеле. Поля: название и id отеля.


    Room_availabilities — доступность номера на каждую ночь. Поля:

    • room_id — id номера
    • date — дата
    • initial_count — количество доступных номеров
    • sales_count — количество уже забронированных номеров

    Отсутствие записи на какую-либо ночь означает недоступность номера.


    Room_price_policies — политики номеров. Один и тот же номер может иметь различные расценки в зависимости от количества гостей, типа питания и других условий. Поля:

    • room_id — id номера
    • max_guests — максимальное количество гостей
    • meal_type — тип питания, число от 0 до 8, где 0 — без питания, 1 — завтрак, 2 — полупансион и т.д.
    • has_special_requirements — наличие специальных условий, булево значение
    • before_type — тип специального условия (0 или 1), 0 — политика действует, только если бронирование происходит до определенной даты, 1 — политика действует, если бронирование совершается за N дней до даты заезда
    • before_date — дата для before_type 0
    • days_before_arrival — количество дней для before_type 1


    Room_prices — цены по политикам номеров за каждую ночь в валюте отеля. Поля:

    • room_price_policy_id — id политики номера
    • price_date — дата
    • price — цена

    Отсутствие записи за какую-либо ночь означает невозможность приобрести номер в эту ночь.


    Currency_rates — курсы обмена валют. Поля:

    • sale_currency — код продаваемой валюты
    • buy_currency — код покупаемой валюты
    • price — курс, число единиц продаваемой валюты, деленное на курс, даст число единиц покупаемой валюты

    Входные параметры


    Пользователь в форме поиска может выбрать:

    • Направление или район — что-то из places или districts. Причем если это направление, то при поиске надо искать не только отели направления, но и отели всех районов направления
    • Желаемая дата заезда
    • Желаемая дата выезда
    • Состав группы людей, например, 3 взрослых + 2 ребенка (7 и 9 лет)
    • Опционально, фильтры по цене за ночь, звездности отеля, типу питания

    Результаты поиска


    Результатом поиска должен стать список отелей по направлению, району. И для каждого отеля:

    • Подходящие даты заезда-выезда
    • Подходящий по вместимости самый дешевый номер ИЛИ 3 самых дешевых номера если нет номера вмещающего всю группу
    • Стоимость за период заезда-выезда в базовой валюте на каждый номер, попавший в результат

    Список отелей должен быть отсортирован: сначала идут отели с подходящим номером, затем отели с 3-мя самыми дешевыми, затем отели без доступных номеров. Дополнительно возможна сортировка по звездности отеля или стоимости за период.

    При этом надо учитывать, что прийти в приложение из базы должно уже лимитированное число записей для конкретной страницы (пагинация).

    Это возможно? Да, в 2 (два!) sql-запроса (после небольшой модификации схемы данных)

    Решение


    Допустим пользователь ищет по следующим параметрам:

    • Направление “Валь Торанс”, в которое входят еще два района “Тинь Ле Лак” и “Тинь Валь Кларе”
    • Желаемая дата заезда: 2 января 2018
    • Желаемая дата выезда: 8 января 2018 (соответственно количество желаемых ночей — 6)
    • Состав группы людей: 3 взрослых + 2 ребенка (7 и 9 лет)
    • Сегодня: 17 августа 2017

    Шаг 1. Ближайшая дата заезда к желаемой


    По сути, надо найти по одному правилу въезда для каждого отеля направления или района с ближайшей датой к желаемой дате въезда. И здесь можно допустить, что ищем ближайшую дату не дальше N дней от желаемой, например, 7 дней. Вот так выглядит такой запрос.

    Запрос ближайшей даты заезда
    SELECT DISTINCT ON (property_id)
      arrival_date,
      property_id,
      abs('2018-01-02'::date - arrival_date) AS days_diff
    FROM property_arrival_rules
    INNER JOIN properties ON properties.id = property_arrival_rules.property_id
    WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
      AND (
        (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
        OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
      )
    ORDER BY property_id, days_diff
    


    Шаг 2. Подходящая дата выезда


    Нам надо рассчитать дату выезда на каждый отель исходя из выбранного правила въезда (из шага 1) и количества ночей, вычисленных как разница между желаемыми датами выезда-заезда.

    И тут открылась первая проблема, т.к. правила въезда оказались очень хитрыми. Есть два типа правил:

    Тип 1. Можно заехать в определенный день на любое количество дней, но не меньше чем на N дней

    Тип 2. Можно заехать в определенный день строго на N дней

    И когда в искомый период попадают правила типа 2, то чтобы рассчитать весь период следует просматривать следующее правило, идущее в день окончания правила — дата заезда из правила + N дней.

    Реальный пример правила типа 2. В отель можно въезжать только по субботам ровно на неделю. Если я хочу въехать на срок от 1 до 6 дней — мне все равно придется брать на всю неделю. Если же я хочу взять больше чем на 7 дней, например, на 9 дней, то мне придется взять или на 14 дней или ограничить себя сроком меньше — на 7 дней. И так далее…

    И получается, что алгоритм расчета даты выезда выглядит следующим образом:

    1. берем найденное правило въезда и предполагаемую дату выезда (дата заезда из правила + желаемое количество ночей)
    2. проверяем находится ли дата выезда внутри минимального периода правила: от “даты заезда” до “даты заезда + N дней”
    2.1. если внутри, т.е. период правила перекрывает желаемые даты — проверяем к какому концу периода ближе
    2.1.1. если ближе к началу и это не первое просматриваемое правило, то дата выезда — это дата заезда из правила
    2.1.2. иначе датой выезда оказывается “дата заезда + N дней”
    2.2. если снаружи, т.е. периода правила может быть недостаточно — проверяем какого типа правило мы смотрим
    2.2.1. если типа 1, то предполагаемая дата выезда и будет рассчитанной датой выезда
    2.2.2. если типа 2, берем следующее правило на дату: “дата заезда + N дней”
    2.2.2.1. если следующее правило существует, то рекурсивно повторяем п.2 уже для этого правила, с учетом того, что это не первое просматриваемое правило
    2.2.2.2. если следующее правило не существует, то датой выезда будет “дата заезда + N дней”

    И как такое положить на sql?

    Можно на стороне приложения заранее рассчитать по правилам въезда все возможные периоды заезда-выезда на каждый день и положить в отдельную таблицу с полями:

    arrival_date
    (дата заезда)
    wanted_departure_date
    (желаемая дата выезда)
    departure_date
    (фактическая
    рассчитанная
    дата выезда)
    property_id
    (id отеля)

    Или даже более плотно, дабы уменьшить количество записей, т.к. для правил типа 2 будут часто совпадать дата заезда и рассчитанная дата выезда для некоторых рядом стоящих дней
    arrival_date
    (дата заезда)
    wanted_departure_range
    (желаемый период выезда,
    тип daterange)
    departure_date
    (фактическая
    рассчитанная
    дата выезда)
    property_id
    (id отеля)

    И назовем ее property_arrival_periods — рассчитанные периоды въезда.

    Для того, чтобы ограничить число записей в этой таблице и сделать расчет не бесконечным, нужно добавить некое ограничение на максимальный срок бронирования, например, 30 дней. При таком ограничении на каждый отель на один год, в худшем случае, будет ~11000 записей, что выглядит вполне неплохо.

    Таким образом при добавлении / изменении / удалении правила въезда, мы фоном в приложении:

    • удаляем рассчитанные периоды за даты: от “даты правила минус 30 дней” до “даты правила”
    • рассчитываем периоды на каждый день от “даты правила минус 30 дней” до “даты правила” на каждый период бронирования: на 1 день, на 2 дня, на 3 дня, …, на 30 дней

    И тогда при поиске нам ничего не нужно считать, а только выбрать из этой новой таблицы.

    Запрос дат заезда-выезда
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
      ORDER BY property_id, days_diff
    )
    
    SELECT 
      property_arrival_periods.arrival_date, 
      property_arrival_periods.departure_date, 
      property_arrival_periods.property_id
    FROM property_arrival_periods
    INNER JOIN fit_arrival_rules
      ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
        AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
    WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    


    Шаг 3. Доступные номера


    Берем все доступные номера, т.е. те, что имеют записи на рассчитанный период въезда-выезда (из шага 2) и одновременно доступны каждую ночь периода.

    Запрос доступных номеров
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
      ORDER BY property_id, days_diff
    ),
    fit_arrival_dates AS (
      SELECT 
        property_arrival_periods.arrival_date, 
        property_arrival_periods.departure_date, 
        property_arrival_periods.property_id
      FROM property_arrival_periods
      INNER JOIN fit_arrival_rules
        ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
          AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
      WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    )
    
    SELECT room_availabilities.room_id
    FROM room_availabilities
    INNER JOIN rooms ON rooms.id = room_availabilities.room_id
    INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
    WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
      AND initial_count - sales_count > 0
    GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
    HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    


    Шаг 4. Стоимость номеров и “помещается ли группа?”


    Берем политики номеров (из шага 3), для которых есть цены на каждый день рассчитанного периода, и вычисляем стоимость за период и среднюю цену за ночь, пересчитывая суммы при этом из валюты отеля в некую базовую валюту (в нашем случае — EUR). Кроме того, необходимо учитывать специальные условия политик “бронирование до даты” и “бронирование за N дней до въезда”.

    Также нам понадобится признак “помещается ли вся группа в номер” на каждую полученную политику.
    По задаче политика должна содержать максимально допустимые возрасты с количеством.
    Например, в номер могут заехать 3 взрослых + 2 ребенка 5 лет.
    В такой номер смогут поместиться группы:

    • 3 взрослых
    • 3 взрослых + ребенок 4 лет
    • 2 взрослых + ребенок 10 лет (на место взрослого)

    Но не поместятся:

    • 4 взрослых
    • 3 взрослых + ребенок 7 лет
    • 2 взрослых + 2 ребенка 9 лет

    И это проблема.

    Мало того, что изначально максимальное количество гостей представлено полем типа hstore (к которому условия проблемно будет написать) в странном виде: Map, где ключи — максимальный возраст, а значения — количество, а для взрослых — ключ вообще “adults”.

    Так еще и непонятно, как вообще представить такую информацию так, чтобы можно было проверить поместится группа людей или нет.

    А давайте представим максимальное количество гостей в виде массива мест (отсортированного по возрастанию), где каждое место — максимальный возраст (18 для взрослого). И тогда вместимость номера “3 взрослых + 2 ребенка 5 лет” будет выглядеть как

    [5, 5, 18, 18, 18]

    А группу людей представим как массив их возрастов, и тогда “2 взрослых + 2 ребенка (5 и 9 лет)” будут выглядеть как

    [5, 9, 18, 18]

    В итоге, в таблицу политик (room_price_policies) был добавлен столбец вместимости (capacity) хранящий ее в таком виде.

    Но еще остается вопрос. Как на sql написать условие (или запрос): поместится ли [5, 9, 18, 18] в [5, 5, 18, 18, 18]? Получается нам надо для каждого гостя из группы искать место в номере, и возраст места должен быть больше или равен возрасту гостя, и учитывать, что на одно место только один человек. Этакое рекурсивное исключение гостей и мест в номере.

    И здесь нам помогут хранимые процедуры. Для нашей задачи процедура выглядит следующим образом.

    Процедура 'помещается ли группа в номер?'
    CREATE OR REPLACE FUNCTION is_room_fit_guests(guests INTEGER[], capacity INTEGER[])
    RETURNS BOOLEAN
    AS
    $$ 
    DECLARE
      guest int;
      seat int;
      seat_index int;
      max_array_index CONSTANT int := 2147483647;
    BEGIN
      guest = guests[1];
    
      IF guest IS NULL
      THEN 
        RETURN TRUE;
      END IF;
    
      seat_index := 1;
      FOREACH seat IN ARRAY capacity
      LOOP
        IF guest <= seat
        THEN
          RETURN is_room_fit_guests(guests[2:max_array_index], capacity[1:seat_index-1] || capacity[seat_index+1:max_array_index]);
        END IF;
        seat_index := seat_index + 1;
      END LOOP;
    
      RETURN FALSE;
    END;
    $$ 
    LANGUAGE plpgsql;
    


    И пример использования.

    И теперь наш запрос выглядит так.

    Запрос с расчетом стоимости и вместимости
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
      ORDER BY property_id, days_diff
    ),
    fit_arrival_dates AS (
      SELECT 
        property_arrival_periods.arrival_date, 
        property_arrival_periods.departure_date, 
        property_arrival_periods.property_id
      FROM property_arrival_periods
      INNER JOIN fit_arrival_rules
        ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
          AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
      WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    )
    
    SELECT
      rooms.property_id,
      fit_arrival_dates.arrival_date,
      fit_arrival_dates.departure_date,
      room_price_policy_id,
      room_price_policies.meal_type,
      (
        CASE
          WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
          ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
        END
      ) AS total,
      (
        CASE
          WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
          ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
        END
      ) AS average_night_price,
      rooms.id AS room_id,
      is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
    FROM room_prices
    INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
    INNER JOIN rooms ON room_price_policies.room_id = rooms.id
    INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
    LEFT JOIN currency_rates 
      ON currency_rates.sale_currency = room_properties.currency 
      AND currency_rates.buy_currency = 'EUR'
    INNER JOIN (
      SELECT room_availabilities.room_id
      FROM room_availabilities
      INNER JOIN rooms ON rooms.id = room_availabilities.room_id
      INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
      WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
        AND initial_count - sales_count > 0
      GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
      HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    ) ra ON ra.room_id = rooms.id
    INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
    WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
      AND (room_price_policies.has_special_requirements = FALSE
        OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
          AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
        OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
          AND room_price_policies.days_before_arrival IS NOT NULL 
          AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
      )
      AND room_price_policies.capacity IS NOT NULL
    GROUP BY
      rooms.property_id,
      fit_arrival_dates.arrival_date,
      fit_arrival_dates.departure_date,
      room_price_policy_id,
      room_price_policies.meal_type,
      rooms.id,
      room_properties.currency,
      currency_rates.price,
      room_price_policies.capacity
    HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    


    Шаг 5. Подходящие отели


    Выбираем отели с данными (из шага 4) по одной самой дешевой политике номера с положительным значением “помещается ли вся группа в номер”.

    Запрос подходящих отелей
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
      ORDER BY property_id, days_diff
    ),
    fit_arrival_dates AS (
      SELECT 
        property_arrival_periods.arrival_date, 
        property_arrival_periods.departure_date, 
        property_arrival_periods.property_id
      FROM property_arrival_periods
      INNER JOIN fit_arrival_rules
        ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
          AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
      WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    ),
    properties_with_rooms AS (
      SELECT
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
            ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS total,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
            ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS average_night_price,
        rooms.id AS room_id,
        is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
      FROM room_prices
      INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
      INNER JOIN rooms ON room_price_policies.room_id = rooms.id
      INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
      LEFT JOIN currency_rates 
        ON currency_rates.sale_currency = room_properties.currency 
        AND currency_rates.buy_currency = 'EUR'
      INNER JOIN (
        SELECT room_availabilities.room_id
        FROM room_availabilities
        INNER JOIN rooms ON rooms.id = room_availabilities.room_id
        INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
        WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
          AND initial_count - sales_count > 0
        GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
        HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
      ) ra ON ra.room_id = rooms.id
      INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
      WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
        AND (room_price_policies.has_special_requirements = FALSE
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
            AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
            AND room_price_policies.days_before_arrival IS NOT NULL 
            AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
        )
        AND room_price_policies.capacity IS NOT NULL
      GROUP BY
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        rooms.id,
        room_properties.currency,
        currency_rates.price,
        room_price_policies.capacity
      HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    )
    
    SELECT DISTINCT ON(property_id) *, 
      1 as all_guests_placed
    FROM properties_with_rooms
    WHERE fit_people = TRUE
    ORDER BY property_id, total
    


    Шаг 6. Неподходящие отели с номерами в наличии


    Такие отели, в которых нет номера под всю группу гостей, в качестве вариантов для бронирования нескольких номеров. Выбираем отели из шага 4 с отрицательным значением “помещается ли вся группа в номер”, но не попавшие в результат шага 5

    Запрос неподходящих отелей
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
      ORDER BY property_id, days_diff
    ),
    fit_arrival_dates AS (
      SELECT 
        property_arrival_periods.arrival_date, 
        property_arrival_periods.departure_date, 
        property_arrival_periods.property_id
      FROM property_arrival_periods
      INNER JOIN fit_arrival_rules
        ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
          AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
      WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    ),
    properties_with_rooms AS (
      SELECT
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
            ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS total,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
            ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS average_night_price,
        rooms.id AS room_id,
        is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
      FROM room_prices
      INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
      INNER JOIN rooms ON room_price_policies.room_id = rooms.id
      INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
      LEFT JOIN currency_rates 
        ON currency_rates.sale_currency = room_properties.currency 
        AND currency_rates.buy_currency = 'EUR'
      INNER JOIN (
        SELECT room_availabilities.room_id
        FROM room_availabilities
        INNER JOIN rooms ON rooms.id = room_availabilities.room_id
        INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
        WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
          AND initial_count - sales_count > 0
        GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
        HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
      ) ra ON ra.room_id = rooms.id
      INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
      WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
        AND (room_price_policies.has_special_requirements = FALSE
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
            AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
            AND room_price_policies.days_before_arrival IS NOT NULL 
            AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
        )
        AND room_price_policies.capacity IS NOT NULL
      GROUP BY
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        rooms.id,
        room_properties.currency,
        currency_rates.price,
        room_price_policies.capacity
      HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    ),
    properties_with_recommended_room AS (
      SELECT DISTINCT ON(property_id) *, 
        1 as all_guests_placed
      FROM properties_with_rooms
      WHERE fit_people = TRUE
      ORDER BY property_id, total
    )
    
    SELECT DISTINCT ON(property_id) *, 
      0 as all_guests_placed   
    FROM properties_with_rooms
    WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
    ORDER BY property_id, total
    


    Шаг 7. Все отели направления


    И наконец, объединяем результаты, сортируя сначала подходящие отели (из шага 5), затем неподходящие отели с доступными номерами (из шага 6), затем все остальные отели, дополнительно сортируя по стоимости за период или звездности отеля при необходимости, а также добавляя пагинацию (20 отелей на странице)

    Конечный запрос поиска отелей
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
      ORDER BY property_id, days_diff
    ),
    fit_arrival_dates AS (
      SELECT 
        property_arrival_periods.arrival_date, 
        property_arrival_periods.departure_date, 
        property_arrival_periods.property_id
      FROM property_arrival_periods
      INNER JOIN fit_arrival_rules
        ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
          AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
      WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    ),
    properties_with_rooms AS (
      SELECT
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
            ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS total,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
            ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS average_night_price,
        rooms.id AS room_id,
        is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
      FROM room_prices
      INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
      INNER JOIN rooms ON room_price_policies.room_id = rooms.id
      INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
      LEFT JOIN currency_rates 
        ON currency_rates.sale_currency = room_properties.currency 
        AND currency_rates.buy_currency = 'EUR'
      INNER JOIN (
        SELECT room_availabilities.room_id
        FROM room_availabilities
        INNER JOIN rooms ON rooms.id = room_availabilities.room_id
        INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
        WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
          AND initial_count - sales_count > 0
        GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
        HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
      ) ra ON ra.room_id = rooms.id
      INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
      WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
        AND (room_price_policies.has_special_requirements = FALSE
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
            AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
            AND room_price_policies.days_before_arrival IS NOT NULL 
            AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
        )
        AND room_price_policies.capacity IS NOT NULL
      GROUP BY
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        rooms.id,
        room_properties.currency,
        currency_rates.price,
        room_price_policies.capacity
      HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    ),
    properties_with_recommended_room AS (
      SELECT DISTINCT ON(property_id) *, 
        1 as all_guests_placed
      FROM properties_with_rooms
      WHERE fit_people = TRUE
      ORDER BY property_id, total
    ),
    properties_without_recommended_room AS (
      SELECT DISTINCT ON(property_id) *, 
        0 as all_guests_placed   
      FROM properties_with_rooms
      WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
      ORDER BY property_id, total
    ),
    properties_with_cheapest_room AS (
      SELECT * FROM properties_with_recommended_room
      UNION ALL
      SELECT * FROM properties_without_recommended_room
    )
    
    SELECT properties.*,
      (
        CASE 
          WHEN room_id IS NOT NULL THEN 1
          ELSE 0
        END
      ) AS room_available,
      properties_with_cheapest_room.arrival_date,
      properties_with_cheapest_room.departure_date,
      properties_with_cheapest_room.room_id,
      properties_with_cheapest_room.room_price_policy_id,
      properties_with_cheapest_room.total,
      properties_with_cheapest_room.average_night_price,
      properties_with_cheapest_room.all_guests_placed
    FROM properties
    LEFT JOIN properties_with_cheapest_room ON properties_with_cheapest_room.property_id = properties.id
    WHERE
        (
          (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
          OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
        )
    ORDER BY all_guests_placed DESC NULLS LAST, room_available DESC, total ASC
    LIMIT 20 OFFSET 0
    


    Шаг 8. 3 самых дешевых номера


    Перед тем как отдать результат пользователю, для неподходящих отелей с доступными номерами отдельным sql-запросом, выбираем 3 самых дешевых номера. Запрос очень похож на поиск самих отелей. Разве что выбираются уникальные номера и только на конкретные отели (из шага 6). Допустим, что на текущей странице два таких отеля, и их id — 1 и 4. Запрос будет таким.

    3 дешевых номера
    WITH fit_arrival_rules AS (
      SELECT DISTINCT ON (property_id)
        arrival_date,
        property_id,
        abs('2018-01-02'::date - arrival_date) AS days_diff
      FROM property_arrival_rules
      INNER JOIN properties ON properties.id = property_arrival_rules.property_id
      WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
        AND property_id IN (1, 4)
      ORDER BY property_id, days_diff
    ),
    fit_arrival_dates AS (
      SELECT 
        property_arrival_periods.arrival_date, 
        property_arrival_periods.departure_date, 
        property_arrival_periods.property_id
      FROM property_arrival_periods
      INNER JOIN fit_arrival_rules
        ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
          AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
      WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
    ),
    properties_with_available_rooms AS (
      SELECT DISTINCT ON (rooms.id)
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
            ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS total,
        (
          CASE
            WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
            ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
          END
        ) AS average_night_price,
        rooms.id AS room_id
      FROM room_prices
      INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
      INNER JOIN rooms ON room_price_policies.room_id = rooms.id
      INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
      LEFT JOIN currency_rates 
        ON currency_rates.sale_currency = room_properties.currency 
        AND currency_rates.buy_currency = 'EUR'
      INNER JOIN (
        SELECT room_availabilities.room_id
        FROM room_availabilities
        INNER JOIN rooms ON rooms.id = room_availabilities.room_id
        INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
        WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
          AND initial_count - sales_count > 0
        GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
        HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
      ) ra ON ra.room_id = rooms.id
      INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
      WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
        AND (room_price_policies.has_special_requirements = FALSE
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
            AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
          OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
            AND room_price_policies.days_before_arrival IS NOT NULL 
            AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
        )
      GROUP BY
        rooms.property_id,
        fit_arrival_dates.arrival_date,
        fit_arrival_dates.departure_date,
        room_price_policy_id,
        room_price_policies.meal_type,
        rooms.id,
        room_properties.currency,
        currency_rates.price
      HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
    )
    
    SELECT 
      distinct_available_rooms.property_id,
      distinct_available_rooms.room_id,
      distinct_available_rooms.room_price_policy_id,
      distinct_available_rooms.total
    FROM properties
    JOIN LATERAL (
      SELECT * FROM properties_with_available_rooms
      WHERE properties.id = properties_with_available_rooms.property_id
      ORDER BY total
      LIMIT 3
    ) distinct_available_rooms ON distinct_available_rooms.property_id = properties.id        
    WHERE properties.id IN (1, 4)
    ORDER BY distinct_available_rooms.total
    


    Результат


    Ускорение работы поиска в десятки раз и это при относительно небольшом количестве данных, а со временем разница будет ощущаться все больше и больше.

    И конечно тонна полезного опыта, полученного в ходе решения.
    Поделиться публикацией
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 51
    • –4
      По вашему, вы сделали так, как нужно?
      • 0
        Да, поиск работает, и гораздо быстрее чем изначально.
        • –2
          А как этот ад поддерживать-то и дорабатывать теперь?
          • –2
            Минуса-то аргументируйте…
            Мой аргумент — это то чтобы дополнить такой запрос на 2 экрана, надо потратить от получаса времени, чтобы разобраться как он и почему работает и как его исправить. И это надо будет делать КАЖДЫЙ раз при встрече с таким запросом. И протестировать его и отладить по частям неудобно. И план запроса по такой бандуре сложен и непонятен.
    • +4
      Написать правильный sql запрос — это отдельное искусство, далеко не все программисты (особенно на ruby on rails) способны это сделать. Да и сам фрэймворк и любые ORM не располагают к тому, что бы сочинять сложные sql запросы.
      • +1
        Кроме того что надо написать правильно запрос, надо еще и провести некий анализ БД
        «наложение» индексов может значительно повысить эффективность
      • 0
        WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7

        Весьма странное место. Зачем нужно проверять даты за год назад?
        • +1
          Да, опечатка. Должно быть

          WHERE '2018-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7

          Поправили, спасибо.
          • +1

            Можно сократить до WHERE arrival_date BETWEEN '2018-01-02'::date - 8 AND '2018-01-02'::date + 8.

            • –1
              Ненадо так сокращать, ибо between работает по разному Иногда как интервал иногда как отрезок. Это я про разные базы. И эта экономия в три кнопки потом вльется в труднонаходимый косяк.
        • –1
          О да, отличное решение. Давайте грузить базу одним длинным запросом с кучей join'ов, ведь такое решение отлично масштабируется и вообще не испытывает никаких проблем (спойлер: нет).

          Когда у вас мало пользователей, то да, вообще никаких проблем. А они возникнут ровно тогда, когда их количество увеличится, и скажем, у вас будут постоянные локи на таблицы. Что вы будете делать тогда с запросом, который будет из-за этого адово тупить?

          Я так же напомню, что в postgres даже нет грязного чтения, только repetable read.
          • +1
            На самом деле автор хотел донести то что он убрал нагрузку с бэкенда и сложил на откуп БД.
            С одной стороны на одном и том же железе получился прирост производительности, с другой стороны его решение не оптимально и требует доработки
            • 0
              Проблема в том, что база данных и так почти всегда bottleneck. Не понятно, зачем еще больше выносить на нее нагрузку.
              • 0
                Не вся логика должна быть заложена в коде, в крупных продуктах очень много сделано через хранимые процедуры, тем более раз мы оперируем с данными из бд, то сомневаюсь что какой-то язык будет работать быстрее чем нативный SQL
                Тот же самый birt-viewer все отчеты строит в БД, потому что он оперирует полученными данными для отображения
                • –3
                  хранимые процедуры это даже хуже огромных запросов.
                  И вы серьезно думаете, что например Java медленнее чем pgSQL?
                  • +4

                    Вы путаете мягкое с тёплым
                    Во-первых, sql выполняет именно то для чего он создан, запросы к бд
                    Во-вторых, приложению сначала надо получит данные из бд, а потом уже с ними выполнять некие действия
                    В-третьих, мы все знаем что Java очень любит память
                    Для текущего решения делать выборку средствами БД самое разумное. Но никто не отменял оптимизацию запросов и индексирования таблиц

                    • +1
                      Я думаю, что для перечисленных вариантов с джойнами база выполнит запрос с меньшим числом операций чем вы это проделаете в своей программе на яве. Либо, вы умеете делать Все оптимизации которые делаются в базе и пишите их каждый раз когда надо вытащить данные. Но даже если так, запрос будет быстрее потому что
                      — при выполнении можно зачитать данные только из индекса (например для выполнения exists
                      — не произойдет конфликта согласованности данных только если вы не используете уровень изоляции serializable что дорого
                      — у базы есть статистика о кардинальности связей и она может применя разные
                      алгоримы для соединения таблиц
                      — база кеширует данные таблиц и индексов в памяти
                      Это просто 4 фактора что на вскидку в голову пришли.
                      В итоге чтении данных потребуется меньше операций чтения с диска (что на самом деле является единственной проблемой при чтении как в задаче с поиском) причем меньше не в константу раз а лучше.
                      • +1
                        «хранимые процедуры это даже хуже огромных запросов» — вот так просто, как шашкой рубанул. Хуже огромных запросов только кривые руки девелоперов. А процедуры и функции — это то, что надо.
                • +2
                  А зачем ему грязное чтение? Он версионник. Конечно, его вакуум — отдельная и больная тема, но с чего бы ему «адово тупить» на локах, когда пишущие не лочат читающих?
                  • –2
                    Официальная документация с вами не согласна. Локи от update отлично конфликтуют с локами для select.
                    • +1

                      А нет, это я читаю не тем местом.

                      • +1
                        Локов нет, но есть нюансы. Например, в Oracle есть знаменитый «Snapshot too old». Ну и select (в Oracle точно, возможно и в PostgreSQL, этот момент не помню), в некоторых случаях может менять данные, но это всё экзотика. В первом приближении, можно считать, что в версионниках читающие не блокируют пишущих и наоборот (для того версионники и придуманы). Причём, в PostgreSQL версионное чтение экстремально дешёвое (дешевле чем в Oracle с его UNDO), поскольку версии не восстанавливаются, а просто читаются с диска и из кэша. Но за это приходится расплачиваться вакуумом.
                        • 0
                          Подозреваю вы под словами селект может менять данные понимаете его реализацию select for update. При последовательном чтении строк действительно происходит пометка блока с зачитанными строками чтобы можно было реализовать select for update skip locked. Но такой паттерн это реализация очередей как правило и там не требуется апдейтов обычно, и проблем нет. Это про оракл.

                          А поясните, что значит версии не восстанавливаются и почему чтение радикально дешевле чем в оракле? В оракле есть три уровня изоляции и в пг вроде тоже read commited read dirty и serializable
                          Третий реально требует поиска блока нужной версии в анду, а read committed позволяет просто найти оригинальную версию блока в анду причем его адрес в самом блоке и записан так что там небольшие потери. А как постгрес действует?
                          • 0
                            думаю, под «select в некоторых случаях может менять данные» подразумевается история в pg про обновление хинт битов при первом чтении вставленной строки. суть в том, что pg версионник, то есть под капотом в его таблицах лежат незакомиченные и удаленные строки, которые периодически чистит автовакуум. при выборе, какие строки убить, автовакуум ориентируется на хинт биты (они нужны еще для кучи разных вещей, не только для этого). когда вы начали транзакцию, но еще не закоммитили ее, строка все равно появляется таблице, а в хитн битах у нее пусто. как только транзакция коммитится, информация об этом замечательном факте попадает в clog. к сожалению, понять в момент коммита, в каких строках таблиц транзакция успела поменять данные проблемно, да и часть страничек может быть вытеснена из буферного кеша (а повторно считывать их дорого). поэтому в строках из закомиченной транзакции хинт биты остаются пустыми. а вот как только мы запросим через select одну из таких строк, pg проставит им хинт биты, что вызовет запись на диск при чтении данных. ну а если таких строк было много, то первое чтение может породить серьезную нагрузку на диск за счет вытеснение грязных страничек их общих буферов pg. это порой ставит в тупик не наступавших на эти грабли людей.
                            • 0
                              Оригинальная схема, спасибо.
                            • +1
                              Нет, select… for update, это вообще не совсем select, а DML в чистом виде. В том что касается Oracle, я говорил о ситуации, когда честный select просматривает блоки с устаревшими блокировками ранее закрытых транзакций и очищает их, что приводит к записи на диск. Про PostgreSQL вам подробно ответили выше. От себя лишь добавлю, что версионное чтение в Oracle может быть весьма накладным, поскольку включает в себя собственно чтение текущего состояния блока, а затем чтение UNDO, для приведения состояния этого блока на требуемый момент в прошлом. Если UNDO не хватает — получаем ORA-01555. В отличии от Oracle, PostgreSQL хранит исторические данные там же где и текущие и ему, грубо говоря, нет разницы, читать то или другое.
                      • 0
                        А давайте вы напишете более-менее реалистичный сценарий? Здесь описан поиск и нет никаких UPDATE и уж тем более нет EXCLUSIVE блокировок. Данные только читаются. Адово тупить будет только разве что плохо написанный код.
                        • 0
                          Данные только читаются.

                          а как вы собираетесь обновлять эти данные?
                          • 0
                            В статье описано как. Мы обновляем нужные записи периодов только при изменениях и делаем это в фоне.
                      • 0

                        Я бы поднял Solr/ElasticSearch c денормализованными данными, оптимизированными под нужный вид поиска.
                        Быть может, это было бы больше кода, связанного с обновлением поискового индекса, но решение точно получилось бы проще и масштабировалось бы лучше.

                        • 0
                          У нас по такому же принципу поиск организован
                          • 0
                            Вроде ничего решение, правда оно кажется очень трудоемким по реализации обновления данных, а также объем данных будет очень и очень большим. Кроме того, мы неоднократно встречались с ситуацией, когда какая-то нода отстает в кластере или же индекс совсем развалился. Обычно непросто понять, что произошло. Тут это еще как-то мониторить отдельно нужно. С другой стороны, по нашему опыту PostgreSQL великолепно держит нагрузку. Наше решение позволило «малой кровью» точечно переписать проблемную часть проекта.
                            • 0

                              Проблема вашего решения в наличии связи между бизнес-требованиями и структурой базы. Это проблемно, т.к.


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

                              Трудоемкость — это довод, если умеете лепить хитрые запросы и не умеете в индекс. Но на вашем месте я бы как минимум вынес производные поля как минимум в отдельные таблицы.

                              • 0
                                У нас производных полей почти нет. Мы вынесли в отдельную таблицу периоды… Схема БД была изменена крайне незначительно.
                          • –2
                            Запрос, конечно, выглядит монструозно. Саппорт (или следующий разработчик) вас за него помянет добрым словом, когда придется вносить какие-то изменения или искать баги.
                            Почему было не написать логику в Stored Procedure? Ведь тогда этого монитра можно было бы побить на куски, которые человек был бы способен понять.
                            • 0
                              Может запрос и выглядит устрашающим, но только если его рассматривать целиком.
                              На деле он строится из кусков, это всего лишь несколько CTE следующие друг за другом. И в приложении каждое CTE строится отдельно, и более того, они переиспользуются в других подобных запросах.
                              И если рассматривать запрос отдельно, по шагам, как в статье, то ничего страшного в нем нет.
                              • 0

                                И чем же хранимая процедура упростит внесение изменений или поиск багов? На куски можно побить и в коде приложения с ровно таким же успехом.

                              • 0
                                А зачем в таблице Properties поля dest_type и dest_id? Почему не сделать place_id и district_id? Или только district_id.
                                • 0
                                  Это так называемая полиморфная связь. Сейчас property можно привязать либо к place, либо к district. Представьте, что появляется третья (четвертая, пятая, ...) сущность к которой можно будет привязать property. При такой связи в структуру таблицы properties не придется вносить изменений.
                                • 0
                                  Как на счёт отдельного инстанстанса Hot Standby (один или даже несколько) для масштабирования производительности такого решения.
                                  • 0

                                    Можно настроить таким образом чтобы писалось на мастер, а читалось со слейва, я думаю топикстартеру пока это не нужно.

                                  • 0
                                    Если боретесь за оптимизацию, то конечно надо всю логику переносить в базу данных. При этом, если стоит выбор между хранимыми процедурами и SQL, то однозначно SQL. SQL будет работать быстрее при условии, если вы используете все возможности SQL. Когда боретесь за производительность надо читать планы запросов и понимать где узкие места и что делать в той или иной ситуации. В приведенных запросах без планов тяжело сказать все ли вы выжали, но на вскидку можно сказать что использование DISTINCT или DISTINC ON это уже не эффективно. Есть паттерны как обходиться без DISTINCT. Оптимизированный запрос не значит доступен в понимании другими разработчиками, тем более если он не является программистом БД.
                                    • 0

                                      А как обойтись без DISTINCT если нужны уникальные записи?

                                      • +1
                                        Например так
                                        WITH RECURSIVE t AS (
                                        (SELECT min(val) AS val FROM test)
                                        UNION ALL
                                        SELECT (SELECT min(val) FROM test WHERE val > t.val)
                                        AS val FROM t WHERE t.val IS NOT NULL
                                        )
                                        SELECT val FROM t WHERE val IS NOT NULL;
                                        (Автор данного запроса Максим Богук.)
                                        • 0

                                          Хм, интересно. Я полный нуб в Postgres, не знал что можно так, спасибо

                                          • 0
                                            Возник реальный интерес — где и почему может быть запрещён DISTINCT (который максимально оптимизирован в движке БД на языке C++, в противовес интерпретатору T-SQL)?
                                            • 0
                                              Никто не говорит, что надо запретить DISTINCT. Если в таблице по какому-то поля имеются дублирующиеся значения, то чтобы отобрать только уникальные значения выполняется полное сканирование всей таблицы. В приведенном выше запросе выборка уникальных значений выполняется с использованием индекса (при условии наличия индекса на поле val), соответственно не надо выполнять полное сканирование таблицы. Но надо понимать что, если данные по этому полю имеют низкую селективность, то индекс работать не будет. Поэтому для правильно оптимизированного запроса надо знать свои данные и понимать где вы можете заставить работать индекс, а где нет. А вообще если данные в выборке дублируются, то чаще всего это недостаточно правильно написан запрос и проще всего естественно написать DISTINCT.
                                              • 0
                                                B-tree index и не не будет работать, а эффективность его использования будет падать. А битмап вполне себе прокатит. Уточняю, просто чтобы не возникало обреченности :)
                                                • 0
                                                  Хочу поправиться. В приведенном выше запросе всегда будет работать индекс B-tree не зависимо от селективности данных, т.к. используется функция min или max, то достаточно использовать индекс и к таблице нет вообще никакого обращения.

                                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.