Skip to content

Instantly share code, notes, and snippets.

@godfat
Last active August 29, 2015 14:22
Show Gist options
  • Save godfat/1debe7d452ef83b8303e to your computer and use it in GitHub Desktop.
Save godfat/1debe7d452ef83b8303e to your computer and use it in GitHub Desktop.
PostgreSQL fast random rows for tables with integer id with uniform distribution
WITH RECURSIVE results(id, i, picked, roll) AS (
WITH bounds AS (SELECT min(id), max(id) - min(id) AS delta FROM table)
(
SELECT NULL::integer
, 0
, ARRAY[]::integer[]
, min + round(delta * random())
FROM bounds
)
UNION ALL
(
SELECT target.id
, i + 1
, picked || target.id
, min + round(delta * random())
FROM bounds, results, table AS target
WHERE target.id >= roll -- why `min + round(delta * random())` here would get weird result?
AND NOT target.id = ANY (picked)
AND i < 8
ORDER BY target.id
LIMIT 1
)
)
SELECT target.id FROM table AS target INNER JOIN results USING (id);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment