Как быстро получить рандомную строку из базу postgresql?

Question

[kr_ilya]

Нужно получить рандомную строку из базы.

Из того что есть сейчас:
Медленный:

SELECT * FROM items WHERE sale_price > 100 AND sale_price < 10000 ORDER BY random() LIMIT 1

Быстрее, но никогда не вернет первую запись и у меня бывает вовсе возвращает 0 строк, не могу сказать с чем это связано:

SELECT * FROM items WHERE sale_price > 100 AND sale_price < 10000 OFFSET floor(random() * (SELECT count(*) FROM items)) LIMIT 1

(Источник)

Самый быстрый, взят здесь:

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 < 10000
	      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 < 10000
	  )
	  (
	    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 < 10000
	    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 < 10000
	    LIMIT 1
	  )
	)
	SELECT * FROM items AS t, r WHERE r.id = t.id

*custom.numRows = 1
Меня в нем смущает то, что большинство возвращаемых строк приходится на значения id 330-370, выше или ниже этих значений выдает очень редко. Возможно, я где-то накосячил, когда подстраивал этот запрос под свои условия, хз.

Скрин

ORDER BY last_view DESC

Может есть еще варианты быстрого получения рандомной записи? Или может нужно что-то исправить в третьем варианте?

Answer 1

[Сергей Горностаев]

Просто используйте сэмплирование.

Comments

[kr_ilya]

Неплохой вариант, но я заметил один критичный для меня недостаток.

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

Мне нужно учитывать довольно сложное условие, под которое такая выборка попадает редко и я не получаю не одной строки в ответе.

Answer 2

[d-stream]

Можно слегка модифицировать ORDER BY random() LIMIT 1 используя его для выборки PK, который уже и использовать в основной выборке

p.s. а какова бизнес-сущность таких рандомных выборок?