小开

ORDER BY的那个会比较慢。

select * from table where random() < 0.01;逐个记录，并决定是否随机过滤它。这将是O(N)，因为它只需要检查每条记录一次。

select * from table order by random() limit 1000;将对整个表进行排序，然后选择前1000个。除去幕后的巫毒魔法，顺序是O(N * log N)。

random() < 0.01的缺点是你会得到一个可变数量的输出记录。

注意，有一个比随机排序更好的方法来洗牌一组数据:Fisher-Yates Shuffle，它运行在O(N)中。不过，在SQL中实现shuffle听起来很有挑战性。

小开

您可以通过使用来检查和比较两者的执行计划

EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;

对一个大型表__abc2的快速测试表明，ORDER BY首先对整个表进行排序，然后选择前1000个项。对一个大表进行排序不仅要读取该表，还包括读取和写入临时文件。where random() < 0.1只扫描整个表一次。

对于大型表，这可能不是您想要的，因为即使是一次完整的表扫描也可能需要很长时间。

第三个建议是

select * from table where random() < 0.01 limit 1000;

这个方法在找到1000行后立即停止表扫描，因此返回得更快。当然，这将降低随机性，但也许这对于你来说已经足够好了。

编辑:除了这些考虑因素，你可以检查一下已经问过的问题。使用查询[postgresql] random会返回一些结果。

postgres: get random entries from table-too slow

depez的一篇链接文章概述了更多的方法:

http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

¹“大”，如“完整的表将不适合内存”。

小开

最佳答案

快速的方式

根据您的规格(以及评论中的其他信息)，

您有一个数字ID列(整数)，只有很少(或中等数量)的间隙。
显然没有或很少有写操作。
您的ID列必须被索引!主键很有用。

下面的查询不需要对大表进行顺序扫描，只需要进行索引扫描。

首先，获取主查询的估计值:

SELECT count(*) AS ct              -- optional
, min(id)  AS min_id
, max(id)  AS max_id
, max(id) - min(id) AS id_span
FROM   big;

唯一可能昂贵的部分是count(*)(用于大表)。考虑到上述规格，您不需要它。估计替换全部计数就可以了，几乎不需要任何代价:

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint AS ct
FROM   pg_class
WHERE  oid = 'big'::regclass;  -- your table name

详细解释:

在PostgreSQL中快速发现表的行数

只要ct不小于多，查询将优于其他方法。

WITH params AS (
SELECT 1       AS min_id           -- minimum id <= current min id
, 5100000 AS id_span          -- rounded up. (max_id - min_id + buffer)
)
SELECT *
FROM  (
SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
FROM   params p
, generate_series(1, 1100) g  -- 1000 + buffer
GROUP  BY 1                        -- trim duplicates
) r
JOIN   big USING (id)
LIMIT  1000;                          -- trim surplus

在id空间中生成随机数。您有“很少的空白”，所以添加10%(足以轻松覆盖空白)的行数检索。
每个id都可以被随机抽取多次(尽管对于大id空格来说不太可能)，因此将生成的数字分组(或使用DISTINCT)。
将__abc0连接到大表中。这应该是非常快的索引到位。
最后修剪剩余的__abc0，没有被dupes和gap吃掉。每一行都有一个完全均等的机会要选择。

短的版本

你可以简化这个查询。上面查询中的CTE仅用于教育目的:

SELECT *
FROM  (
SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
FROM   generate_series(1, 1100) g
) r
JOIN   big USING (id)
LIMIT  1000;

使用rCTE进行细化

特别是当你对差距和估计不太确定的时候。

WITH RECURSIVE random_pick AS (
SELECT *
FROM  (
SELECT 1 + trunc(random() * 5100000)::int AS id
FROM   generate_series(1, 1030)  -- 1000 + few percent - adapt to your needs
LIMIT  1030                      -- hint for query planner
) r
JOIN   big b USING (id)             -- eliminate miss


UNION                               -- eliminate dupe
SELECT b.*
FROM  (
SELECT 1 + trunc(random() * 5100000)::int AS id
FROM   random_pick r             -- plus 3 percent - adapt to your needs
LIMIT  999                       -- less than 1000, hint for query planner
) r
JOIN   big b USING (id)             -- eliminate miss
)
TABLE  random_pick
LIMIT  1000;  -- actual limit

我们可以在基本查询中使用< >强小盈余< / >强。如果有太多的间隙，所以我们在第一次迭代中没有找到足够的行，rCTE将继续使用递归项进行迭代。我们仍然需要在ID空间中有相对几的间隙，否则递归可能会在达到限制之前耗尽——或者我们必须从一个足够大的缓冲区开始，这违背了优化性能的目的。

重复项由rCTE中的UNION消除。

外层LIMIT使CTE停止，只要我们有足够的行。

这个查询是精心起草的，使用可用的索引，生成实际上是随机的行，直到达到限制才停止(除非递归耗尽)。如果你要重写它，这里会有很多陷阱。

包装成函数

对于带有不同参数的同一表重复使用:

CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03)
RETURNS SETOF big
LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
_surplus  int := _limit * _gaps;
_estimate int := (           -- get current estimate from system
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'big'::regclass);
BEGIN
RETURN QUERY
WITH RECURSIVE random_pick AS (
SELECT *
FROM  (
SELECT 1 + trunc(random() * _estimate)::int
FROM   generate_series(1, _surplus) g
LIMIT  _surplus           -- hint for query planner
) r (id)
JOIN   big USING (id)        -- eliminate misses


UNION                        -- eliminate dupes
SELECT *
FROM  (
SELECT 1 + trunc(random() * _estimate)::int
FROM   random_pick        -- just to make it recursive
LIMIT  _limit             -- hint for query planner
) r (id)
JOIN   big USING (id)        -- eliminate misses
)
TABLE  random_pick
LIMIT  _limit;
END
$func$;

电话:

SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);

泛型函数

我们可以让这个泛型适用于具有唯一整数列(通常是PK)的任何表:将表作为多态类型传递，并(可选地)传递PK列的名称，并使用EXECUTE:

CREATE OR REPLACE FUNCTION f_random_sample(_tbl_type anyelement
, _id text = 'id'
, _limit int = 1000
, _gaps real = 1.03)
RETURNS SETOF anyelement
LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
-- safe syntax with schema & quotes where needed
_tbl text := pg_typeof(_tbl_type)::text;
_estimate int := (SELECT (reltuples / relpages
* (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class  -- get current estimate from system
WHERE  oid = _tbl::regclass);
BEGIN
RETURN QUERY EXECUTE format(
$$
WITH RECURSIVE random_pick AS (
SELECT *
FROM  (
SELECT 1 + trunc(random() * $1)::int
FROM   generate_series(1, $2) g
LIMIT  $2                 -- hint for query planner
) r(%2$I)
JOIN   %1$s USING (%2$I)     -- eliminate misses


UNION                        -- eliminate dupes
SELECT *
FROM  (
SELECT 1 + trunc(random() * $1)::int
FROM   random_pick        -- just to make it recursive
LIMIT  $3                 -- hint for query planner
) r(%2$I)
JOIN   %1$s USING (%2$I)     -- eliminate misses
)
TABLE  random_pick
LIMIT  $3;
$$
, _tbl, _id
)
USING _estimate              -- $1
, (_limit * _gaps)::int  -- $2 ("surplus")
, _limit                 -- $3
;
END
$func$;

调用默认值(重要!):

SELECT * FROM f_random_sample(null::big);  --!

或者更具体地说:

SELECT * FROM f_random_sample(null::"my_TABLE", 'oDD ID', 666, 1.15);

性能与静态版本基本相同。

可能的替代方法

如果你的要求允许重复的相同集合调用(我们谈论的是重复调用)考虑MATERIALIZED VIEW。执行上述查询一次，并将结果写入一个表。用户以闪电般的速度获得准随机选择。每隔一段时间或你选择的事件刷新你的随机选择。

Postgres 9.5引入了`TABLESAMPLE SYSTEM (n)`

其中n是一个百分比。手册:

BERNOULLI和SYSTEM采样方法都接受一个采样参数，它是要采样的表的分数，表示为a 0到100之间的百分比。这个参数可以是任何__abc0值的表达式。

大胆强调我的。它是非常快，但结果是并非完全随机。再看一下手册:

SYSTEM方法明显比BERNOULLI方法快当指定小的抽样百分比时，但它可能返回一个由于聚类效应，表中的样本随机程度较低

返回的行数变化很大。在我们的例子中，要获取约 1000行:

SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);

Postgresql order by random()，按随机顺序选择行:

这是缓慢的，因为它对整个表进行排序，以保证每一行都有完全相等的机会被选中。全表扫描对于完美的随机性是不可避免的。

select your_columns from your_table ORDER BY random()

Postgresql order by random() with distinct:

select * from
(select distinct your_columns from your_table) table_alias
ORDER BY random()

Postgresql顺序随机限制一行:

这也很慢，因为它必须扫描表，以确保每一行都有相同的机会被选中，就在这一刻:

select your_columns from your_table ORDER BY random() limit 1

常数时间选择随机N行元素周期表扫描:

如果您的表非常大，那么上面的表扫描就需要花费5分钟才能完成。

为了更快，你可以安排一个幕后的夜间表扫描驯鹿，这将保证一个完美的随机选择在O(1)恒定时间速度，除了在夜间索引表扫描，它必须等待维护完成之前，你可能会收到另一个随机行。

--Create a demo table with lots of random nonuniform data, big_data
--is your huge table you want to get random rows from in constant time.
drop table if exists big_data;
CREATE TABLE big_data (id serial unique, some_data text );
CREATE INDEX ON big_data (id);
--Fill it with a million rows which simulates your beautiful data:
INSERT INTO big_data (some_data) SELECT md5(random()::text) AS some_data
FROM generate_series(1,10000000);
 

--This delete statement puts holes in your index
--making it NONuniformly distributed
DELETE FROM big_data WHERE id IN (2, 4, 6, 7, 8);
 

 

--Do the nightly maintenance task on a schedule at 1AM.
drop table if exists big_data_mapper;
CREATE TABLE big_data_mapper (id serial, big_data_id int);
CREATE INDEX ON big_data_mapper (id);
CREATE INDEX ON big_data_mapper (big_data_id);
INSERT INTO big_data_mapper(big_data_id) SELECT id FROM big_data ORDER BY id;
 

--We have to use a function because the big_data_mapper might be out-of-date
--in between nightly tasks, so to solve the problem of a missing row,
--you try again until you succeed.  In the event the big_data_mapper
--is broken, it tries 25 times then gives up and returns -1.
CREATE or replace FUNCTION get_random_big_data_id()
RETURNS int language plpgsql AS $$
declare
response int;
BEGIN
--Loop is required because big_data_mapper could be old
--Keep rolling the dice until you find one that hits.
for counter in 1..25 loop
SELECT big_data_id
FROM big_data_mapper OFFSET floor(random() * (
select max(id) biggest_value from big_data_mapper
)
) LIMIT 1 into response;
if response is not null then
return response;
end if;
end loop;
return -1;
END;
$$;
 

--get a random big_data id in constant time:
select get_random_big_data_id();
 

--Get 1 random row from big_data table in constant time:
select * from big_data where id in (
select get_random_big_data_id() from big_data limit 1
);
┌─────────┬──────────────────────────────────┐
│   id    │            some_data             │
├─────────┼──────────────────────────────────┤
│ 8732674 │ f8d75be30eff0a973923c413eaf57ac0 │
└─────────┴──────────────────────────────────┘


--Get 4 random rows from big_data in constant time:
select * from big_data where id in (
select get_random_big_data_id() from big_data limit 3
);
┌─────────┬──────────────────────────────────┐
│   id    │            some_data             │
├─────────┼──────────────────────────────────┤
│ 2722848 │ fab6a7d76d9637af89b155f2e614fc96 │
│ 8732674 │ f8d75be30eff0a973923c413eaf57ac0 │
│ 9475611 │ 36ac3eeb6b3e171cacd475e7f9dade56 │
└─────────┴──────────────────────────────────┘


--Test what happens when big_data_mapper stops receiving
--nightly reindexing.
delete from big_data_mapper where 1=1;
select get_random_big_data_id();   --It tries 25 times, and returns -1
--which means wait N minutes and try again.

改编自:https://www.gab.lc/articles/bigdata_postgresql_order_by_random

或者，如果以上都是太多的工作。

对于常量时间随机选择行，一个更简单的好方法是在你的大表上创建一个名为big_data的新列。mapper_int使用唯一的索引使其不为空。每晚用1到max(n)之间的唯一整数重置列。为了得到一个随机行，你“在0和__abc3之间选择一个随机整数”。并返回mapper_int为该值的行。如果没有该id对应的行，因为重新索引后行发生了变化，则选择另一个随机行。如果在big_data中添加了一行。Mapper_int然后用max(id) + 1填充它

小开

物化观点的变体“可能的选择”;Erwin Brandstetter概述是可能的。

例如，您不希望在返回的随机化值中出现重复值。一个示例用例是生成只能使用一次的短代码。

包含你的(非随机)值集的主表必须有一些表达式来确定哪些行是“使用”的;而不是——这里我将简单地创建一个名为used的布尔列。

假设这是输入表(可能会添加其他列，因为它们不会影响解决方案):

id_values  id  |   used
----+--------
1   |   FALSE
2   |   FALSE
3   |   FALSE
4   |   FALSE
5   |   FALSE
...

根据需要填充ID_VALUES表。然后，如Erwin所述，创建一个物化视图，对ID_VALUES表随机一次:

CREATE MATERIALIZED VIEW id_values_randomized AS
SELECT id
FROM id_values
ORDER BY random();

注意，物化视图不包含已使用的列，因为这很快就会过时。视图也不需要包含可能在id_values表中的其他列。

为了获得(和"消费")随机值，在id_values上使用UPDATE-RETURNING，从带有连接的id_values_randomized中选择id_values，并应用所需的标准来只获得相关的可能性。例如:

UPDATE id_values
SET used = TRUE
WHERE id_values.id IN
(SELECT i.id
FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
WHERE (NOT i.used)
LIMIT 1)
RETURNING id;

根据需要更改LIMIT——如果你一次需要多个随机值，将LIMIT更改为n，其中n是所需值的数量。

在id_values上有了适当的索引，我相信UPDATE-RETURNING应该执行得非常快，负载也很小。它通过一次数据库往返返回随机值。“合格”的标准;行可以根据需要非常复杂。可以在任何时候将新行添加到id_values表中，并且一旦物化视图刷新(可能在非高峰时间运行)，应用程序就可以访问它们。物化视图的创建和刷新将是缓慢的，但它只需要在添加到id_values表的新id需要可用时执行。

小开

如果你只想要一行，你可以使用从count派生的计算offset。

select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));

小开

添加类型为serial的名为r的列。指数r。

假设我们有20万行，我们将生成一个随机数n，其中0 <n <= 20, 000。

选择带有r > n的行，将它们排序为ASC，并选择最小的行。

代码:

select * from YOUR_TABLE
where r > (
select (
select reltuples::bigint AS estimate
from   pg_class
where  oid = 'public.YOUR_TABLE'::regclass) * random()
)
order by r asc limit(1);

代码是自解释的。中间的子查询用于快速估计https://stackoverflow.com/a/7945274/1271094中的表行数。

在应用程序级别，如果n >的行数或需要选择多行，则需要再次执行语句。

小开

从PostgreSQL 9.5开始，有一个新的语法专门用于从表中获取随机元素:

SELECT * FROM mytable TABLESAMPLE SYSTEM (5);

这个例子将给出mytable中5%的元素。

有关文档的详细说明:http://www.postgresql.org/docs/current/static/sql-select.html

小开

select * from table order by random() limit 1000;

如果你知道你想要多少行，查看tsm_system_rows。

tsm_system_rows

模块提供了表采样方法SYSTEM_ROWS，可以在SELECT命令的TABLESAMPLE子句中使用。

此表抽样方法接受一个整数参数，该参数是要读取的最大行数。结果示例将始终包含相同数量的行，除非表没有包含足够多的行，在这种情况下，将选择整个表。与内置的SYSTEM采样方法一样，SYSTEM_ROWS执行块级采样，因此样本不是完全随机的，而是可能受到聚类效应的影响，特别是在只请求少量行的情况下。

首先安装扩展

CREATE EXTENSION tsm_system_rows;

然后你的问题，

SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);

小开

这是一个对我有用的决定。我想这很容易理解和执行。

SELECT
field_1,
field_2,
field_2,
random() as ordering
FROM
big_table
WHERE
some_conditions
ORDER BY
ordering
LIMIT 1000;

小开

我知道我有点晚了，但我刚刚发现了这个很棒的工具，名为pg_sample:

pg_sample -从较大的PostgreSQL数据库中提取一个小的样本数据集，同时保持参考完整性。

我尝试了一个350M行数据库，它真的很快，不知道随机性。

./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db

小开

我的经验告诉我:

__ABC0并不比order by random() limit 1快。

我认为offset方法会更快，因为它可以节省在Postgres中排序的时间。事实证明并非如此。

小开

我认为在postgreSQL中最好和最简单的方法是:

SELECT * FROM tableName ORDER BY random() LIMIT 1

最好的方法选择随机行PostgreSQL