Пишу, по большей части, про историю, свою жизнь и немного про программирование.

Примерная оценка в Постгресе

Читая документацию по «Постгресу», набрёл на интересную конструкцию — TABLESAMPLE. Она позволяет обработать запросом не всю таблицу, а только указанный процент, причём строки выбираются случайным образом.

Например, встречается такая задача — показать какую-нибудь внутреннюю рекламу, для этого надо быстро выбрать какой-нибудь случайный идентификатор из таблицы, вот так это можно сделать при помощи указанной конструкции:

SELECT id FROM (
    SELECT id FROM very_big_table TABLESAMPLE SYSTEM (.0001) LIMIT 100
) _ ORDER BY RANDOM() LIMIT 1

Тут выбирается одна десятитысячная процента таблицы (0,0001%), а потом из этой выборки берётся первая строка. На настоящей таблице с почти миллиардом строк, такой запрос у меня занял около ста миллисекунд.

SYSTEM тут — метод выборки. Их всего два, второй (BERNOULLI) даёт более случайные и аккуратные результаты, но работает как-то уж очень медленно. SYSTEM работает на уровне блоков, выдавая случайные блоки целиком, и из-за этого я дополнительно сортирую результат — чтобы перемешать строки внутри блока.

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

Типа, сколько в среднем примерно сотрудники некой гипотетической компании тратят на обед в зависимости от пола (оценка по одному проценту данных):

SELECT sex, AVG(spent)
FROM meal TABLESAMPLE SYSTEM (1)
WHERE mtime BETWEEN SYMMETRIC '12:00:00' AND '14:00:00'
GROUP BY 1

Или сколько примерно раз я ездил такси «Везёт» за последние семь лет (оценка по пяти процентам и экстраполяция):

SELECT COUNT(*)*20
FROM taxi_call TABLESAMPLE BERNOULLI (5)
WHERE carrier='vezet' AND cdate >= '2011-08-03'