Получение рандомных строк с помощью ORDER BY random() быстро работает на небольших таблицах, но скорость выполнения падает с ростом количества строк в таблице.
В связи с этим я стал искать решение, которое выбирало бы рандомные строки и работало быстро, как на небольших, так и на огромных таблицах.
Первым делом наткнулся на решение из
статьи , отредактировал его под себя так
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
`WITH RECURSIVE r AS (
WITH b AS (
SELECT
min(t.id),
(
SELECT t.id
FROM items AS t
WHERE
t.sale_price > 100 AND
t.sale_price < 2000
AND t.views = 0
AND t.category IN(6, 7)
ORDER BY t.id DESC
LIMIT 1
OFFSET ${custom.numRows} - 1
) max
FROM items AS t
WHERE
t.sale_price > 100 AND
t.sale_price < 2000
AND t.views = 0
AND t.category IN(6, 7)
)
(
SELECT
id, min, max, array[]::bigint[] || id AS a, 0 AS n
FROM items AS t, b
WHERE
id >= min + ((max - min) * random())::int AND
t.sale_price > 100 AND
t.sale_price < 2000
AND t.views = 0
AND t.category IN(6, 7)
LIMIT 1
) UNION ALL (
SELECT t.id, min, max, a || t.id, r.n + 1 AS n
FROM items AS t, r
WHERE
t.id > min + ((max - min) * random())::int AND
t.id <> all( a ) AND
r.n + 1 < ${custom.numRows} AND
t.sale_price > 100 AND
t.sale_price < 2000
AND t.views = 0
AND t.category IN(6, 7)
LIMIT 1
)
)
SELECT * FROM items AS t, r WHERE r.id = t.id`
Работает он очень быстро, но не совсем корректно. Имея таблицу с 3750 строк и значения id идущие подряд до 1183, а затем идет пропуск и далее значения id сразу начинаются с 2084. (строки с пропущенными значениями были удалены). В таком случае запрос намного чаще возвращает строку с id 2084 и 2086 и почти никогда не возвращает строки с id > 3000. (Скрин)
Этот вариант мне не нравится, хоть и работает быстро.
Затем я взял запрос из
данной темы, немного переписал его, для получения всей строки по условию. Получилось это
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
`WITH q AS (
WITH RECURSIVE
r AS (
SELECT array[]::bigint[] AS res, min(id) AS min, max(id)-min(id) AS range, 0 AS n FROM items
UNION ALL
SELECT res||ARRAY(SELECT id FROM items WHERE id IN (SELECT (min+range*random())::int FROM generate_series(1,1000)) AND NOT id=ANY(res) AND sale_price > 1000 AND sale_price < 5000 AND views = 0 AND category IN(6), min, range, n+1
FROM r
WHERE
n < ${custom.numRows}+1
)
SELECT * FROM (
SELECT * FROM r ORDER BY array_length(res,1) DESC NULLS LAST LIMIT 1
) AS t LIMIT 1
)
SELECT * FROM items WHERE id IN(SELECT unnest(res) FROM q) LIMIT ${custom.numRows}`
custom.numRows = 1; // Количество строк
Данный код конечно работает, но имея в таблице только одну совпадающею с условием строку я получаю её грубо говоря через раз. Т.е выполнил я один раз запрос - получил строку, выполнил второй раз точно такой же запрос - не получил строку, хотя в таблице она есть. Я думаю, что это связано с generate_series(1,1000).
И, наконец, всеми хвалимый TABLESAMPLE. Он не подходит из-за того что сначала выбирает строки, а уже после применяет условие. Из-за этого в выборке может не оказаться строк, подходящих под условие, несмотря на то, что в базе они есть.
Я думал сделать выборку по такому алгоритму:
1) Выбрать все id подходящие под условие.
2) Скриптом (javascript) выбрать рандоное значение из результирующего массива с idшниками.
3) Получить запись со значением id из пункта 2.
Но мне он дико не нравится из-за того, что придется в первом пункте тянуть весь список id из базы. (а если строк миллионы...) Я думаю это сильно скажется на производительность.
Как можно поступить в моём случае? Какие есть варианты? Postgresql v9.5
