PostgreSQL 9.5: что нового? Часть 2. TABLESAMPLE28.09.2015 19:18

Продолжаем обзор нововведений в PostgreSQL 9.5. Первая часть тут.

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

SELECT ... FROM TABLE_NAME ... TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

sampling_method — метод сэмплирования, по умолчанию в PostgreSQL 9.5 их два: SYSTEM и BERNOULLI, в качестве аргумента они принимают число с плавающей запятой (или любое валидное выражение, результатом которого является число), которое интерпретируется как процент для выборки: от 0 до 100.

Давайте посмотрим на примерах, как работает сэмплирование в PostgreSQL 9.5.
Пусть у нас есть таблица с транзакциями, в которой хранится id транзакции, сумма транзакции и дата со временем, когда транзакция была завершена. В таблицу добавим 100000 записей.

CREATE TABLE transactions (
  id           SERIAL PRIMARY KEY,
  amount       NUMERIC(15,2),
  ending_time  TIMESTAMP
);
 
INSERT INTO transactions (amount, ending_time)
  SELECT
    (round(CAST(random() * 100000 AS NUMERIC), 2)),
    now() - random() * CAST('1 day' AS INTERVAL)
FROM generate_series(1, 50000000);
</source>

Попробуем взять сэмпл записей размером 0.1% от исходной таблицы (100 записей):

SELECT * FROM transactions TABLESAMPLE SYSTEM (0.1)
 
Total query runtime: 213 ms.
157 rows retrieved.

Почему же мы получили не 100 записей, а 157? Дело в том, что PostgreSQL хранит данные таблицы в виде массива страниц размером 8 kb (по умолчанию, этот параметр можно изменить при сборке сервера из исходных кодов) и при методе сэмплирования SYSTEM просто берет нужное количество случайных страниц для заданного числа процентов и отдает их «как есть». В данном случае в одну страницу помещается 157 записей. Если запросить в 2 раза больше записей для сэмпла, то будут взяты данные с 2 страниц:

SELECT * FROM transactions TABLESAMPLE SYSTEM (0.2)
 
Total query runtime: 21 ms.
314 rows retrieved.

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

SELECT MAX(ending_time) FROM transactions TABLESAMPLE SYSTEM(0.1)

max
2014-11-08 22:30:32.720855

SELECT MAX(ending_time) FROM transactions TABLESAMPLE SYSTEM(0.1)

max
2014-12-02 11:42:32.720855

SELECT MAX(ending_time) FROM transactions TABLESAMPLE SYSTEM(0.1)

max
2014-10-21 09:40:32.720855

Тогда как реальное значение будет:

SELECT MAX(ending_time) FROM transactions

max
2014-12-07 04:04:32.720855

Для того, чтобы получить более распределенную выборку, можно использовать метод сэмплирования BERNOULLI, который сканирует всю таблицу (фактически, «бросает монетку» для каждой записи) и выбирает случайные записи:

SELECT MAX(ending_time) FROM transactions TABLESAMPLE BERNOULLI(0.1)

max
2014-12-07 00:06:32.720855

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

1) Без сэмпла:

EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions
"Aggregate  (cost=1887.00..1887.01 rows=1 width=8) (actual time=25.795..25.795 rows=1 loops=1)"
"  ->  Seq Scan on transactions  (cost=0.00..1637.00 rows=100000 width=8) (actual time=0.005..12.438 rows=100000 loops=1)"
"Planning time: 0.055 ms"
"Execution time: 25.816 ms"
 
SELECT AVG(amount) FROM transactions
50028.8742828

2) Сэмпл по методу SYSTEM:

EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions TABLESAMPLE SYSTEM(0.1)
"Aggregate  (cost=1.25..1.26 rows=1 width=8) (actual time=0.088..0.088 rows=1 loops=1)"
"  ->  Sample Scan (system) on transactions  (cost=0.00..1.00 rows=100 width=8) (actual time=0.017..0.048 rows=157 loops=1)"
"Planning time: 0.068 ms"
"Execution time: 0.120 ms"
 
SELECT AVG(amount) FROM transactions TABLESAMPLE SYSTEM(0.1)
53628.223694267516

3) Сэмпл по методу BERNOULLI:

EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions TABLESAMPLE BERNOULLI(0.1)
"Aggregate  (cost=638.25..638.26 rows=1 width=8) (actual time=2.847..2.847 rows=1 loops=1)"
"  ->  Sample Scan (bernoulli) on transactions  (cost=0.00..638.00 rows=100 width=8) (actual time=0.020..2.780 rows=104 loops=1)"
"Planning time: 0.145 ms"
"Execution time: 2.872 ms"
 
SELECT AVG(amount) FROM transactions TABLESAMPLE BERNOULLI(0.1)
50285.863240740741

Видим, что сэмплирование по методу SYSTEM работает быстрее, но при этом точность у него ниже, в то время как сэмплирование по методу BERNOULLI работает медленнее, но и точность у него выше. Вы можете выбирать компромисс между скоростью и точностью. Также отметим, что для сэмплирования используется новый вид сканирования: Sample scan.
Добавим в таблицу еще записей, пусть в ней будет 20 миллионов записей:

INSERT INTO transactions (amount, ending_time)
  SELECT
    (round(CAST(random() * 100000 AS DECIMAL), 2)),
    now() - INTERVAL '1 year'  + (i * INTERVAL '1 minute' )
  FROM generate_series(100001, 20000000) i;
 
EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions;
 
"Aggregate  (cost=377372.70..377372.71 rows=1 width=8) (actual time=4604.297..4604.297 rows=1 loops=1)"
"  ->  Seq Scan on transactions  (cost=0.00..327375.96 rows=19998696 width=8) (actual time=0.027..2043.846 rows=20000000 loops=1)"
"Planning time: 0.063 ms"
"Execution time: 4604.325 ms"
 
SELECT AVG(amount) FROM transactions;
50002.888681451
 
EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions TABLESAMPLE SYSTEM (0.1)
"Aggregate  (cost=757.99..758.00 rows=1 width=8) (actual time=7.309..7.309 rows=1 loops=1)"
"  ->  Sample Scan (system) on transactions  (cost=0.00..707.99 rows=19999 width=8) (actual time=0.057..4.588 rows=20096 loops=1)"
"Planning time: 0.073 ms"
"Execution time: 7.340 ms"
SELECT AVG(amount) FROM transactions TABLESAMPLE SYSTEM (0.1)
50323.198322551752
 
EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions TABLESAMPLE BERNOULLI (0.1)
"Aggregate  (cost=127638.99..127639.00 rows=1 width=8) (actual time=751.831..751.832 rows=1 loops=1)"
"  ->  Sample Scan (bernoulli) on transactions  (cost=0.00..127588.99 rows=19999 width=8) (actual time=0.260..747.682 rows=19899 loops=1)"
"Planning time: 0.055 ms"
"Execution time: 751.879 ms"
 
SELECT AVG(amount) FROM transactions TABLESAMPLE BERNOULLI (0.1)
50043.386386377336

Видим, что при увеличении количества записей метод BERNOULLI больше теряет в производительности. Это обусловлено тем, что он делает, фактически, полное сканирование таблицы, тогда как SYSTEM просто возвращает несколько страниц.

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

EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions TABLESAMPLE SYSTEM (1)
"Aggregate  (cost=7591.84..7591.85 rows=1 width=8) (actual time=65.055..65.055 rows=1 loops=1)"
"  ->  Sample Scan (system) on transactions  (cost=0.00..7091.87 rows=199987 width=8) (actual time=0.043..37.939 rows=200018 loops=1)"
"Planning time: 0.053 ms"
"Execution time: 65.083 ms"
 
EXPLAIN ANALYZE SELECT AVG(amount) FROM transactions TABLESAMPLE BERNOULLI (1)
"Aggregate  (cost=129888.84..129888.85 rows=1 width=8) (actual time=799.826..799.826 rows=1 loops=1)"
"  ->  Sample Scan (bernoulli) on transactions  (cost=0.00..129388.87 rows=199987 width=8) (actual time=0.035..769.899 rows=199682 loops=1)"
"Planning time: 0.063 ms"
"Execution time: 799.859 ms"

Как видно, метод SYSTEM больше теряет в производительности при увеличении процента выборки. Это логично, так как BERNOULLI как делал полное сканирование, так и делает его, в то время как SYSTEM должен вернуть в 10 раз больше страниц.
В итоге можно отметить, что метод SYSTEM на небольших процентах выборки работает значительно быстрее, чем BERNOULLI, но при этом дает менее случайную выборку записей. Но с увлеичением процента это преимущество теряется.

С помощью опционального ключевого слова REPEATABLE мы можем задать seed для генератора случайных величин. Если у двух запросов один и тот же метод сэмплирования, процент выборки и seed, то для этих двух запросов будет выбран один и тот же сэмпл:

SELECT MAX(amount) FROM transactions TABLESAMPLE SYSTEM (0.1) REPEATABLE (50)
99997.91
 
SELECT MAX(amount) FROM transactions TABLESAMPLE SYSTEM (0.1) REPEATABLE (300)
99999.15
 
SELECT MAX(amount) FROM transactions TABLESAMPLE BERNOULLI (0.1) REPEATABLE (50)
99995.9
 
SELECT MAX(amount) FROM transactions TABLESAMPLE SYSTEM (0.1) REPEATABLE (50)
99997.91

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

Отдельно стоит отметить, что сэмплирование выполняется ДО условия WHERE, то есть не получится выбрать сэмпл по условию. В данном случае, сначала выберется сэмпл, а затем применится условие WHERE, но, так как вероятность получить записи c id<100 из таблицы в 20000000 записей очень мала, то в итоге выборка будет пустой:

SELECT * FROM transactions TABLESAMPLE SYSTEM (1) WHERE id<100
Total query runtime: 31 ms.
0&nbsprows retrieved.

SYSTEM и BERNOULLI — не единственные возможные варианты сэмплирования, при желании можно написать собственный метод для сэмплирования. Документация для этого лежит тут. При этом кастомные методы сэмплирования могут принимать более одного аргумента или не принимать их вовсе. Также кастомные методы могут не учитывать ключевое слово REPEATABLE.

На этом мой краткий рассказ о сэмплировании в PostgreSQL 9.5 завершается. Спасибо за внимание!

P.S. Можно примерно оценивать количество записей в таблице с помощью сэмпла :)

SELECT COUNT(*)*100.0 FROM transactions TABLESAMPLE SYSTEM (1);
20001800

P.P.S. Не делайте так, как написано выше, это шутка и не всегда работает так, как надо.
В следующей части: GROUPINS SETS, ROLLUP, CUBE.

© Habrahabr.ru