postgresql 9.4 / 9.5 - выберите для обновления одной случайной строки в большом наборе данных с большим количеством операций чтения и записи.

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

У меня есть следующие ограничения:

  • Мне нужно применить SELECT ... FOR UPDATE только к строкам, где deal_id является текущей сделкой моего приложения (т.е. я не применяю его ко ВСЕЙ таблице / ко ВСЕМ строкам таблицы, только к тем, где например deal_id = 3 например)
  • Мне нужно выбрать только строки, где доступно = true
  • Мне нужно вывести только 1 строку (когда игрок покупает билет, я должен проверить эти 1 миллион строк и СЛУЧАЙНО выбрать одну для него (только одно из множества решений Stackoverflow, таких как здесь или TABLESAMPLE не работают легко)
  • Обычно у меня есть около 1 миллиона строк, которые соответствуют deal_id = 3 (3 в качестве примера) и available = true (из общего числа около 30 миллионов строк в любой момент времени)
  • У меня очень много READS и WRITES => от 50 до 100+ одновременных чтений в таблице и, как следствие, примерно такое же количество записей (как однажды выбрано, available = true изменяется на false внутри SELECT..for UPDATE)
  • У меня блокировка, пока выполняется выбор / обновление в строке. Теперь я использую SELECT..FOR UPDATE с pg_try_advisory_xact_lock (и когда postgresql 9.5 выйдет из бета-версии, я буду использовать SKIP LOCKED)
  • Мне нужна невероятно быстрая скорость. я нацеливаю запрос на ‹5 мс
  • Что касается идентификаторов, могут быть огромные промежутки между идентификаторами в таблице в целом, НО внутри `` билетов из конкретной сделки '' (см. запрос ниже) нет никакого разрыва между идентификаторами (даже самыми маленькими), что, как я полагаю, может иметь значение чтобы найти наиболее подходящий запрос.

Вот мой текущий запрос. Это ПРОИЗВОЛЬНЫЙ ВЫБОР, но теперь я хочу изменить его / воссоздать, чтобы иметь СЛУЧАЙНЫЙ ВЫБОР (но избегайте обычного предела random () 1, который должен проходить через все 1M строк и работает очень медленно, даже, возможно, избежать смещения (?), поскольку он заведомо медленный на больших наборах данных).

UPDATE tickets s
        SET available = false
        FROM (
              SELECT id
              FROM   tickets
              WHERE  deal_id = #{@deal.id}
              AND    available
              AND    pg_try_advisory_xact_lock(id)
              LIMIT  1
              FOR    UPDATE
              ) sub
        WHERE         s.id = sub.id
        RETURNING     s.name, s.id

как изменить этот запрос, чтобы перейти от произвольного выбора к СЛУЧАЙНОМУ выбору и с максимально быстрым запросом?

Я хотел бы, если возможно, ощутимые предложения запросов, которые я попробую в своем приложении.


postgresql sql random postgresql-9.4
person Mathieu    schedule 26.10.2015    source источник
comment
Неужели нужно задавать (в принципе) один и тот же вопрос трижды? stackoverflow.com/q/33330915/2235885   -  person joop    schedule 27.10.2015
comment
Хорошо, я знаю, что не смог дать полную информацию об ограничениях, поэтому в итоге я получил предложения, которые не отвечали моим потребностям. моя вина. вот почему здесь я даю всю необходимую информацию   -  person Mathieu    schedule 27.10.2015
comment
Может просто добавить SKIP LOCKED в подзапрос?   -  person Mikko Rantalainen    schedule 20.05.2021

Ответы (1)


arrow_upward
1
arrow_downward

Что касается идентификаторов, могут быть огромные промежутки между идентификаторами в таблице в целом, НО внутри `` билетов из конкретной сделки '' (см. запрос ниже) нет никакого разрыва между идентификаторами (даже самыми маленькими), что, как я полагаю, может иметь значение чтобы найти наиболее подходящий запрос.

Это делает вашу жизнь намного проще. Я бы использовал следующий подход.

0) Создать индекс по (deal_id, available, id).

1) Получить значения MIN и MAX идентификатора для данного deal_id.

SELECT MIN(id) AS MinID, MAX(id) AS MaxID
FROM   tickets
WHERE  deal_id = #{@deal.id}
AND    available

Если этот запрос приводит к сканированию индекса вместо поиска, используйте два отдельных запроса для MIN и MAX.

2) Сгенерируйте случайное целое число RandID в найденном диапазоне [MinID; MaxID].

3) Выберите строку с ID=RandID. Запрос должен искать индекс.

UPDATE tickets s
    SET available = false
    FROM (
          SELECT id
          FROM   tickets
          WHERE  deal_id = #{@deal.id}
          AND    available
          AND    id = @RandID
          AND    pg_try_advisory_xact_lock(id)
          LIMIT  1
          FOR    UPDATE
          ) sub
    WHERE         s.id = sub.id
    RETURNING     s.name, s.id

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

Сказав все это, я понял, что это не сработает. Когда вы говорите, что в идентификаторах нет пробелов, вы, скорее всего, имеете в виду, что нет пробелов для идентификаторов с одинаковым deal_id (независимо от значения столбца available), но не для идентификаторов с одинаковыми deal_id И available=true.

Как только первая случайная строка будет установлена ​​на available=false, в идентификаторах будет разрыв.

Вторая попытка

Добавьте float столбец RandomNumber в таблицу tickets, который должен содержать случайное число в диапазоне (0,1). Каждый раз, когда вы добавляете строку в эту таблицу, генерируйте такое случайное число и сохраняйте его в этом столбце.

Создать указатель на (deal_id, available, RandomNumber).

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

UPDATE tickets s
    SET available = false
    FROM (
          SELECT id
          FROM   tickets
          WHERE  deal_id = #{@deal.id}
          AND    available
          AND    pg_try_advisory_xact_lock(id)
          ORDER BY RandomNumber
          LIMIT  1
          FOR    UPDATE
          ) sub
    WHERE         s.id = sub.id
    RETURNING     s.name, s.id
person Vladimir Baranov    schedule 27.10.2015
comment
Примечание: это примерно то же самое решение, что и в ответе на первый вопрос OP. - person joop; 27.10.2015
comment
большое спасибо за это предложение. Что касается вашей первой попытки, вы совершенно правы, нет дыр, когда вы берете билеты в пределах определенного deal_id, но наверняка будут дыры, когда вы возьмете билеты с конкретными сделками и доступными = true. - person Mathieu; 27.10.2015
comment
Я новичок в postgresql, поэтому есть кое-что, чего я не совсем понимаю: в чем преимущество создания нового столбца (random ()) и размещения на нем индекса В отличие от запроса столбца id (первичный и может иметь индекс тоже): оба числа, оба имеют дыры / промежутки между значениями ... почему нам действительно нужно создать новый столбец, а не использовать существующий столбец идентификатора, Is - это потому, что намного БЫСТРЕЕ ЗАКАЗАТЬ значения с плавающей запятой между 0 и 1 ( созданный random (), чем ЗАКАЗАТЬ целочисленные значения от 1 до 1 миллиона? - person Mathieu; 27.10.2015
comment
Столбец RandomNumber используется для выбора случайной строки. Случайные числа вычисляются заранее, и вам не нужно генерировать миллион случайных чисел каждый раз, когда вы хотите выбрать только одну строку. Для сортировки значений с плавающей запятой и целочисленных значений требуется то же время, но когда у вас есть только целочисленные идентификаторы, нет возможности выбрать случайную строку. Целочисленные идентификаторы сортировать нет смысла. - person Vladimir Baranov; 28.10.2015