Существует несколько способов для получения случайных строк из таблицы. В этой статье мы сравним наиболее популярные способы, разберемся в их плюсах и минусах и определимся в каких случаях какой способ лучше использовать.
Давайте, в начале создадим тестовую таблицу products с 10 млн строк, на которой будем тестировать разные способы:
CREATE TABLE products (id int, name varchar);
INSERT INTO products SELECT i, md5(random()::text) FROM generate_series(1,10000000) AS i;
CREATE INDEX products_id_key ON products (id);Из таблицы будем выбирать 50 случайных строк.
Способ 1 – ORDER BY random()
SELECT * FROM products ORDER BY random() LIMIT 50; Limit (cost=540526.81..540526.93 rows=50 width=45) (actual time=143484.564..143485.538 rows=50 loops=1)
-> Sort (cost=540526.81..565526.81 rows=10000000 width=45) (actual time=143439.405..143439.721 rows=50 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 31kB
-> Seq Scan on products (cost=0.00..208334.00 rows=10000000 width=45) (actual time=0.031..71253.970 rows=10000000 loops=1)
Planning Time: 0.121 ms
JIT:
Functions: 3
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.917 ms, Inlining 5.433 ms, Optimization 25.189 ms, Emission 13.572 ms, Total 45.111 ms
Execution Time: 143486.892 msЗапрос выполнился за 143 сек. Как мы видим из плана выполнения запроса, в начале происходит выборка всех 10 млн строк (rows напротив строки Seq Scan), а затем из них выбираются случайные 50 (rows напротив строки LIMIT), поэтому запрос выполняется медленно. Чем больше будет таблица, тем медленнее будет выполняться запрос.
Пример выборки:
6713420 | 75a9cf740bd16b42dc1e43ce880c25a0
7621610 | f562ff34caaf769791dfc48663969df1
2284385 | fad4514cd3245e60fe5c48c64f98b6b8
792226 | 039b7f34de7ee8d882351c4eb93815e0
1881245 | 5316b953acc4285b2623f34cedbdd011
6286124 | 6d03a97c23c89bdfd52db92f4c33e656
9582555 | 4a4b442a8d7b63b66796e4181fe42a90
508950 | eaeaa945bd54fc8e4e2977b1cc086db4
3597563 | e1e735de8ec03ec72ebf978c7b6b3177
8707436 | 0110b19d81a218c5b3b3b40e817c0e6bДанный способ выборки случайных строк подходит только для небольших таблиц. В качестве эксперимента я создал 3 отдельные таблицы products: c 10 тыс, 100 тыс и 1 млн строк. Выборка 50 случайных строк их них заняло 4 мс, 28 мс и 207 мс соответственно. В итоге, если в таблице более 100 тыс строк, то лучше воспользоваться другим методом.
Способ 2 – TABLESAMPLE
В PostgreSQL 9.5 появилась инструкция TABLESAMPLE, при помощи которой можно получить случайную выборку из таблицы. После данной инструкции указывается название метода, который определяет, как именно будут выбираться строки.
PostgreSQL, по умолчанию, для TABLESAMPLE предоставляет методы SYSTEM и BERNOULLI, при помощи которых можно выбрать определенный процент случайных строк (напр, 0.005% от всех строк). Используя расширение tsm_system_rows можно также добавить метод SYSTEM_ROWS, который позволит выбрать фиксированное количество случайных строк.
Давайте подробнее рассмотрим каждый метод, чтобы понять их преимущества и недостатки.
SYSTEM
В методе SYSTEM качестве аргумента указывается вероятность выбора каждой страницы (все строки таблицы физически хранятся в виде страниц; размер страницы, по умолчанию, равен 8 КБ).
SELECT * FROM products TABLESAMPLE SYSTEM(0.005); Sample Scan on products (cost=0.00..21.00 rows=500 width=37) (actual time=0.314..4.909 rows=480 loops=1)
Sampling: system ('0.005'::real)
Planning Time: 1.671 ms
Execution Time: 9.324 msЗапрос выполнился быстро (за 9 мс), т.к. из таблицы сразу были выбраны случайные страницы со строками без последовательного сканирования.
Т.к. количество выбранных страниц и количество строк в каждой странице может быть разным, то метод SYSTEM может возвращать разное количество строк от запроса к запросу. В нашем примере мы задали вероятность выбора каждой страницы 0.005%. Если бы данный метод выбирал точное количество строк, то мы бы получили ровно 500 строк (0.005% от 10 млн). В нашем случае мы получили 480 строк.
Давайте попробуем вызвать этот запрос несколько раз:
SELECT COUNT(*) FROM products TABLESAMPLE SYSTEM(0.005); -- 480
SELECT COUNT(*) FROM products TABLESAMPLE SYSTEM(0.005); -- 720
SELECT COUNT(*) FROM products TABLESAMPLE SYSTEM(0.005); -- 600
SELECT COUNT(*) FROM products TABLESAMPLE SYSTEM(0.005); -- 360Важно понимать, что строки возвращаются в том же порядке, в котором они располагаются на страницах. Это значит, что в итоговой выборке строки будут скорее всего располагаться последовательно.
Пример выборки:
19796 | 5721e954c9e0ffdae2f89a02f29d723c
19797 | 53e008d3b3740a65afb4ba81e5f29f40
19798 | e097788740c30af4db0dc232b2fa514b
19799 | 61c54409df64eb05dad999ec9056246f
19800 | bc6239e67be89ecd3244488070986a4c
559321 | f4cec3ac0666cf2ffe32afe1ee84b619
559322 | 159b7ca0746b47c4b48af58937105bb1
559323 | 9d7e64f419a237e2613a160e9e79afcc
559324 | 1c85669659c7264d053bceee1dc50b16
559325 | 509af792853a8d82cf45ed65bf5b685dBERNOULLI
Если в методе SYSTEM мы указывали вероятность выбора каждой страницы (строки хранятся в страницах), то в методе BERNOULLI указывается вероятность выбора каждой строки. Таким образом, выборка из таблицы более случайна и количество выбранных строк меньше «гуляет» от запроса к запросу.
SELECT * FROM products TABLESAMPLE BERNOULLI(0.005); Sample Scan on products (cost=0.00..83339.00 rows=500 width=37) (actual time=0.248..229.548 rows=513 loops=1)
Sampling: bernoulli ('0.005'::real)
Planning Time: 0.049 ms
Execution Time: 233.994 msМетод BERNOULLI выполняется дольше (233 мс), чем SYSTEM (9 мс), однако выборка более случайна:
186606 | 6dd5f0abc1af80599c15341f396e14c3
215946 | 7cddbe553116bd4951bb77d1a7c25dcf
248615 | b5828f7cc3ee249fedb031f2d2cb1b41
278561 | c6a6f67617566dfbbae4c53196d144e8
305718 | 44e748600746f0ca3545969e6ae1d606
348908 | a6de8624f028c5fe76dc9b7c1ca87b5a
363781 | cb527dcdbe87f4356ffe234f8a80b0c7
402060 | 42fd9855687a597e43168ea14b75fada
450987 | 4f073aa6e70d4d0ed97db6edd50da87d
459344 | f07ef3e8c2e01472341d76d920702552Метод BERNOULLI, как и SYSTEM возвращает разное количество строк от запроса к запросу, однако оно «гуляет» не так сильно:
SELECT COUNT(*) FROM products TABLESAMPLE BERNOULLI(0.005); -- 503
SELECT COUNT(*) FROM products TABLESAMPLE BERNOULLI(0.005); -- 505
SELECT COUNT(*) FROM products TABLESAMPLE BERNOULLI(0.005); -- 489
SELECT COUNT(*) FROM products TABLESAMPLE BERNOULLI(0.005); -- 484SYSTEM_ROWS
Чаще всего требуется выбрать фиксированное количество случайных строк. Это позволяет сделать метод SYSTEM_ROWS. Чтобы воспользоваться им, необходимо добавить расширение tsm_system_rows.
CREATE EXTENSION tsm_system_rows;
SELECT * FROM products TABLESAMPLE SYSTEM_ROWS(50); Sample Scan on products (cost=0.00..4.50 rows=50 width=37) (actual time=0.020..0.642 rows=50 loops=1)
Sampling: system_rows ('50'::bigint)
Planning Time: 0.059 ms
Execution Time: 1.201 msЗапрос выполнился за 1 мс. Было выбрано ровно 50 строк.
Как и SYSTEM, метод SYSTEM_ROWS осуществляет выборку на уровне страниц, поэтому мы сталкиваемся с той же проблемой – строки из 1 страницы будут скорее всего располагаться последовательно:
7473356 | d9f944c78aa457d941908374686b2f4e
7473357 | f151b2a93d62014c8282889f2cc8b36a
7473358 | 83eb095b5523aa3b0fd8f349e8d31d51
7473359 | 09db36738765a0dccd5a88a7f7357902
7473360 | 0cf56782d2b18fff6dc5ff7cd82cdab4
5215921 | 6decd1ca973bf7b52f16cb5fecc8d4b1
5215922 | 9d941a0f32f6d9a3b834d481f05cc88b
5215923 | 831278c9cec605d3bca29e5ca66ca043
5215924 | fcfa6406b6170b6a29e4fd855c364baa
5215925 | f408f1d6a4935f5068126d0c9d8aa009Способ 3 – recursive random()
В данном случае производится выборка случайных строк N раз. Для этого используется вспомогательная конструкция WITH RECURSIVE. В начале выполняется первый запрос (до UNION ALL), затем итеративно выполняется второй запрос (после UNION ALL). В первом запросе вычисляются значения, которые будут использоваться во втором запросе для выборки случайных строк: минимальный ID min_id, максимальный ID max_id и текущий номер строки n. После того, как второй запрос будет выполнен N раз (в нашем случае 50 раз), выборка строк завершается и выбранные ID передаются в главный запрос для получения всех колонок.
WITH RECURSIVE rand AS (
SELECT NULL::int id, min(id) min_id, max(id) max_id, 0 n
FROM products
UNION ALL
SELECT products.id, rand.min_id, rand.max_id, rand.n + CASE WHEN products.id IS NULL THEN 0 ELSE 1 END
FROM rand
LEFT JOIN products ON products.id = (
SELECT floor(random() * (rand.max_id - rand.min_id + 1))::int
)
WHERE rand.n < 50
)
SELECT products.*
FROM rand
INNER JOIN products ON rand.id = products.id; Nested Loop (cost=179.02..441.54 rows=31 width=37) (actual time=2.576..86.295 rows=50 loops=1)
CTE rand
-> Recursive Union (cost=0.93..178.58 rows=31 width=16) (actual time=0.183..46.175 rows=51 loops=1)
-> Result (cost=0.93..0.94 rows=1 width=16) (actual time=0.169..0.233 rows=1 loops=1)
InitPlan 2 (returns $1)
-> Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.060..0.086 rows=1 loops=1)
-> Index Only Scan using products_id_key on products products_1 (cost=0.43..285061.74 rows=10009960 width=4) (actual time=0.046..0.053 rows=1 loops=1)
Index Cond: (id IS NOT NULL)
Heap Fetches: 0
InitPlan 3 (returns $2)
-> Limit (cost=0.43..0.46 rows=1 width=4) (actual time=0.055..0.081 rows=1 loops=1)
-> Index Only Scan Backward using products_id_key on products products_2 (cost=0.43..285061.74 rows=10009960 width=4) (actual time=0.042..0.048 rows=1 loops=1)
Index Cond: (id IS NOT NULL)
Heap Fetches: 1
-> Nested Loop Left Join (cost=0.46..17.70 rows=3 width=16) (actual time=0.835..0.872 rows=1 loops=51)
-> WorkTable Scan on rand rand_1 (cost=0.00..0.22 rows=3 width=12) (actual time=0.008..0.016 rows=1 loops=51)
Filter: (n < 50)
Rows Removed by Filter: 0
-> Index Only Scan using products_id_key on products products_3 (cost=0.46..5.81 rows=1 width=4) (actual time=0.794..0.802 rows=1 loops=50)
Index Cond: (id = (SubPlan 1))
Heap Fetches: 0
SubPlan 1
-> Result (cost=0.00..0.03 rows=1 width=4) (actual time=0.008..0.015 rows=1 loops=50)
-> CTE Scan on rand (cost=0.00..0.62 rows=31 width=4) (actual time=1.417..46.770 rows=50 loops=1)
Filter: (id IS NOT NULL)
Rows Removed by Filter: 1
-> Index Scan using products_id_key on products (cost=0.43..8.45 rows=1 width=37) (actual time=0.751..0.758 rows=1 loops=50)
Index Cond: (id = rand.id)
Planning Time: 0.344 ms
Execution Time: 86.865 msЗапрос выполнился за 86 мс. Пример выборки:
6830883 | 26fd6b3aafd8fc474a665ea2655cf02e
6097219 | e008f0d9209f59b53af7912bf193fc31
655526 | 815b1cfbf08e64a2e754e585ac2e6e27
9487691 | 46fabc8b003be186eb163b56d7b168e5
7229501 | 098575f64eab9e9d21ab2cc3dffe4616
3535162 | 1ce3c65aa9db72863e2d9f991087d633
3609568 | 9050232f67abe00a7786bcbf5ab8306c
5748236 | 65226bcde08efc34ddc05ad4a3616ed9
2213019 | 92a5f53dcef8fc655f411ebe12f442c5
5555903 | b5d30d266204c346d1ec16842b431358Шпаргалка
-- Выбор 1 случайной строки
SELECT * FROM products TABLESAMPLE SYSTEM_ROWS(1);
-- Выбор N случайных строк для небольших таблиц (до ~100000 строк)
SELECT * FROM products ORDER BY random() LIMIT 50;
-- Выбор N случайных строк для больших таблиц
WITH RECURSIVE rand AS (
SELECT NULL::int id, min(id) min_id, max(id) max_id, 0 n
FROM products
UNION ALL
SELECT products.id, rand.min_id, rand.max_id, rand.n + CASE WHEN products.id IS NULL THEN 0 ELSE 1 END
FROM rand
LEFT JOIN products ON products.id = (
SELECT floor(random() * (rand.max_id - rand.min_id + 1))::int
)
WHERE rand.n < 50
)
SELECT id FROM rand WHERE id IS NOT NULL;
-- Выбор % случайных строк (кол-во строк может меняться от запроса к запросу)
SELECT * FROM products TABLESAMPLE BERNOULLI(0.005)