How to quickly select random postgresql records?

Question

PostgreSQL

You need to select a certain number of random records, for example 10 with a condition.
ORDER BY random() becomes very slow on large tables, and tablesample is not suitable, because it selects rows first and then applies the condition, because of this, the selection may be empty.

There is code that works fast, but most of the rows with each selection fall on id 600+ and very rarely 1000+.

WITH RECURSIVE r AS (
    WITH b AS (
      SELECT
      min(t.id),
      (
        SELECT t.id
        FROM items AS t
        WHERE
          t.sale_price > 10 AND
          t.sale_price < 10000
        ORDER BY t.id DESC
        LIMIT 1
        OFFSET 5 - 1
      ) max
      FROM items AS t
      WHERE 
        t.sale_price > 10 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 > 10 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 < 5 AND
        t.sale_price > 10 AND
        t.sale_price < 10000
      LIMIT 1
    )
  )
  SELECT * FROM items AS t, r WHERE r.id = t.id

Source
With maximum and minimum values, the id set will be like this (array "a"):

min: '601',
    max: '2914',
    a: [ '638', '656', '652', '846', '1274' ]

That is, most values ​​are around 600, although all other values ​​meet the condition. And so with each sample.

Maybe there are much better practices for getting random strings according to a certain condition with normal randomness?

Answer 1

and why not ?

Answer 2

postgres=# SELECT floor(random()*(10-1+1))+1;
?column?
----------
8
(1 row)
An example of issuing an integer random number, in your code it should return a fractional number