How to quickly get a random row from a postgresql database?

Question

PostgreSQL

You need to get a random string from the database.

From what we have now:
Slow:

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

Faster, but it will never return the first record and sometimes it returns 0 rows at all, I can’t say what this is connected with:

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

( Source )

Fastest, taken from here :

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
What confuses me about it is that most of the returned rows fall on the id values ​​330-370, it rarely returns above or below these values. Perhaps I screwed up somewhere when I adjusted this request to my conditions, xs.

Screen

ORDER BY last_view DESC

Maybe there are other options for quickly getting a random entry? Or maybe you need to fix something in the third option?

Answer 1

Just use sampling .

Answer 2

It is possible to slightly modify ORDER BY random() LIMIT 1 using it to sample PK, which is already used in the main sample
ps and what is the business essence of such random samples?