Postgres: получать случайные записи из таблицы - слишком медленно

В моей базе данных postgres у меня есть следующие отношения (упрощенные ради этого вопроса):

Objects (currently has about 250,000 records)
-------
n_id
n_store_object_id (references store.n_id, 1-to-1 relationship, some objects don't have store records)
n_media_id (references media.n_id, 1-to-1 relationship, some objects don't have media records)

Store (currently has about 100,000 records)
-----
n_id
t_name,
t_description,
n_status,
t_tag

Media
-----
n_id
t_media_path

До сих пор так хорошо. Когда мне нужно запросить данные, я запустил это (обратите внимание на limit 2 в конце, как часть требования):

select
    o.n_id,
    s.t_name,
    s.t_description,
    me.t_media_path
from
    objects o
    join store s on (o.n_store_object_id = s.n_id and s.n_status > 0 and s.t_tag is not null)
    join media me on o.n_media_id = me.n_id
limit
    2

Это отлично работает и возвращает мне две записи, как и ожидалось. Время выполнения при этом составляет около 20 мс - просто отлично.

Теперь мне нужно получить 2 случайных элемента каждый раз, когда выполняется запрос. Я думал, что добавлю order by random(), например:

select
    o.n_id,
    s.t_name,
    s.t_description,
    me.t_media_path
from
    objects o
    join store s on (o.n_store_object_id = s.n_id and s.n_status > 0 and s.t_tag is not null)
    join media me on o.n_media_id = me.n_id
order by
    random()
limit
    2

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

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

Ответы

Ответ 1

Я думаю, что вам лучше сначала выбрать случайные объекты, а затем выполнить соединение с этими объектами после их выбора. 1.e., запросите один раз, чтобы выбрать случайные объекты, затем запросите еще раз, чтобы присоединиться к тем объектам, которые были выбраны.

Ответ 2

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

Ответ 4

Кажется, ваша проблема такова: у вас есть таблица с 250 000 строк и нужны две случайные строки. Таким образом, вам нужно создать 250 000 случайных чисел, а затем отсортировать строки по их номерам. Две секунды, чтобы сделать это, кажется мне довольно быстро.

Единственный реальный способ ускорить выбор - не создавать 250 000 случайных чисел, а вместо этого искать строки по индексу.

Я думаю, вам нужно будет изменить схему таблицы для оптимизации для этого случая. Как насчет чего-то типа:

  • 1) Создайте новый столбец с последовательностью, начинающейся с 1.
  • 2) Каждая строка будет иметь number.
  • 3) Создайте индекс на: number % 1000
  • 4) Запрос для строк, где number % 1000 равен случайному числу между 0 и 999 (это должно попасть в индекс и загрузить случайный часть вашей базы данных).
  • 5) Возможно, вы можете добавить RANDOM() в свое предложение ORDER BY и он будет просто сортировать этот кусок вашей базы данных и быть 1000x быстрее.
  • 6) Затем выберите первые две из этих строк.

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

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