PostgreSQL | Как получить одну случайную запись из таблицы?

Решение № 1 — Через сортировку всей таблицы и функцию RANDOM() в PostgreSQL

Это самый простой в понимании вариант, но самый медленный.

Команда для получения одной случайной записи из таблицы:

SELECT * FROM a.t_d ORDER BY RANDOM() LIMIT 1;

Ключевое выражение здесь RANDOM(). Оно генерирует случайное число в диапазоне 0.0 <= x < 1.0. Это значит, что НОЛЬ может быть, а ОДИН никогда не будет. В JavaScript это точно также, например.

Осуществлять запросы мы будем через пользовательский веб-интерфейс pgAdmin4.

Случайная запись из таблицы в PostgreSQL через SELECT RANDOM() - вызов 1 — Случайная запись из таблицы в PostgreSQL через SELECT RANDOM() — вызов 1

Случайная запись из таблицы в PostgreSQL через SELECT RANDOM() - вызов 2 — Случайная запись из таблицы в PostgreSQL через SELECT RANDOM() — вызов 2

Случайная запись из таблицы в PostgreSQL через SELECT RANDOM() - вызов 3 — Случайная запись из таблицы в PostgreSQL через SELECT RANDOM() — вызов 3

В нашем случае a.t_d — это:

Имя схемы — a
Имя таблицы — t_d

Выражение LIMIT 1 ограничивает выдачу записей таблицы, одной штукой. Если написать 10, то будет 10 записей.

Если отдельно вызывать,

SELECT RANDOM();

то можно получать такие результаты:

Случайное число в PostgreSQL через SELECT RANDOM() - вызов 1 — Случайное число в PostgreSQL через SELECT RANDOM() — вызов 1

Случайное число в PostgreSQL через SELECT RANDOM() - вызов 2 — Случайное число в PostgreSQL через SELECT RANDOM() — вызов 2

Случайное число в PostgreSQL через SELECT RANDOM() - вызов 3 — Случайное число в PostgreSQL через SELECT RANDOM() — вызов 3

Это решение основано на полном сканировании всей таблицы. Сначала обходим все записи, попутно расставляя случайные значения и только потом сортируем по случайным значениям. Ну и в самом конце отбираем всего одну запись по свежей «случайной сортировке».

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

Выборка случайной записи из 5 миллионов заняла 1,25 секунды при полном обходе таблицы через сортировку и RANDOM() - PostgreSQL — Выборка случайной записи из 5 миллионов заняла 1,25 секунды при полном обходе таблицы через сортировку и RANDOM() — PostgreSQL

Нужно решать задачу более быстрым способом.

Решение № 2 — Заранее определяемся со значением идентификатора через агрегатную функцию MAX() в PostgreSQL

Наше решение задачи можно существенно ускорить, если применить выборку одной случайной записи сразу по значению идентификатора.

Предположим, что у нас целочисленные идентификаторы записей в таблице. Они же являются первичными ключами, а значит по ним построен индекс.

Предположим, что наши идентификаторы начинаются с 1 и заканчиваются 5000000. Между идентификаторами нет пустых мест (данные никогда не удалялись). Все значения чётко инкрементированы (прибавляются на единицу).

Если так, то можем воспользоваться таким выражением.

SELECT * FROM a.t_d WHERE id = (SELECT floor((SELECT max(id) FROM a.t_d) * random()));

Получили случайную запись из таблицы, зная идентификатор, через обход индекса - PostgreSQL — Получили случайную запись из таблицы, зная идентификатор, через обход индекса — PostgreSQL

Обратите внимание на ускорение. Такая выборка позволила получить случайную запись за 317 миллисекунд. Это в 4 раза быстрее, чем первый способ.

В этом варианте мы отталкиваемся от максимального значения идентификатора в таблице. В нашем случае это 5000000 (пять миллионов). За это отвечает выражение «SELECT max(id) FROM a.t_d«. Используется агрегатная функция max().

Так как функция random() создаёт число от нуля до единицы, то в самых крайних случаях при умножении его на максимальное значение идентификатора мы можем получить НОЛЬ или почти МАКСИМАЛЬНОЕ ЗНАЧЕНИЕ ИДЕНТИФИКАТОРА.

Примеры реальных вызовов по таблице в 40 миллионов записей:

Получение случайного числа умноженного на максимальное значение идентификатора - PostgreSQL - вызов 1 — Получение случайного числа умноженного на максимальное значение идентификатора — PostgreSQL — вызов 1

Получение случайного числа умноженного на максимальное значение идентификатора - PostgreSQL - вызов 2 — Получение случайного числа умноженного на максимальное значение идентификатора — PostgreSQL — вызов 2

Получение случайного числа умноженного на максимальное значение идентификатора - PostgreSQL - вызов 3 — Получение случайного числа умноженного на максимальное значение идентификатора — PostgreSQL — вызов 3

Получились такие варианты чисел с дробной частью:

19347822.10754377
5946083.342926454
34500026.69378561

Мы видим, что они не целые. Значит следующим шагом мы будем приводить их к целому.

В PostgreSQL есть четыре варианта функций для приведения дробного числа к целому:

floor() — наименьшее целое
round() — по правилам математики
ceil() или ceiling() — наибольшее целое
trunc() — ближайшее к нулю целое

Мы выбрали функцию floor() — это «SELECT floor((SELECT max(id) FROM a.t_d) * random())«. Так мы будем получать целые случайные значения из диапазона идентификаторов.

После этого мы просто воспользуемся условием WHERE, где будем искать по столбцу id.

Решение № 3 — Используем предложение TABLESAMPLE для оператора SELECT в PostgreSQL

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

В этом случае нам нужно использовать предложение TABLESAMPLE для оператора SELECT.

SELECT * FROM a.t_d TABLESAMPLE SYSTEM (1) LIMIT 1;

Результат вызова:

Получили одну случайную запись из большой таблицы при помощи предложения TABLESAMPLE для оператора SELECT в PostgreSQL

Обратите внимание! Мы получили результат за 80 миллисекунд. Это почти в 16 раз быстрее первого способа с random() и почти в 4 раза быстрее второго способа c max().

В стандартный дистрибутив PostgreSQL включены два метода выборки для предложения TABLESAMPLE

BERNOULLI()
SYSTEM()

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

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

Мы выбрали значение 1 в «SYSTEM (1)«, потому что оно символизирует всего 1% записей из данной таблицы. Чем меньше это значение, тем случайнее будет полученная выборка записей. Чем выше значение (например 50 или 70), тем менее вероятно выпадение случайной записи из таблицы. При значении 100 мы всегда будем получать одну и ту же запись.

Также мы используем «LIMIT 1» для того, чтобы выдернуть одну запись из представленного подмножества отобранных записей через SYSTEM(). Мы указывали 1 процент для выборки — это значит, что с разрастанием таблицы в этот 1 процент будут попадать больше записей. Лимит позволяет нам всегда получать всего одну случайную запись из таблицы.

Решение № 4 — Используем предложение TABLESAMPLE и метод RANDOM()

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

Чтобы наверняка получать одну случайную запись при помощи TABLESAMPLE, нужно дополнить SELECT-запрос методом RANDOM(). Только на этот раз наш рандом будет обходить не миллионы записей, а всего несколько тысяч (зависит от процента переданного в «SYSTEM (1)«).

SELECT * FROM (SELECT * FROM a.t_d TABLESAMPLE SYSTEM(0.05)) AS qwe ORDER BY RANDOM() LIMIT 1;

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

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Раздел «Таблица 9.6. Случайные функции» — https://postgrespro.ru/docs/postgresql/14/functions-math#FUNCTIONS-MATH-RANDOM-TABLE

Раздел «Агрегатные функции» — https://postgrespro.ru/docs/postgresql/14/functions-aggregate

Команды SQL — https://postgrespro.ru/docs/postgresql/14/sql-commands

Команда SELECT — https://postgrespro.ru/docs/postgresql/14/sql-select

Предложение ORDER BY — https://postgrespro.ru/docs/postgresql/14/sql-select#SQL-ORDERBY

Предложение LIMIT — https://postgrespro.ru/docs/postgresql/14/sql-select#SQL-LIMIT