选择随机行PostgreSQL的最佳方法

select * from table where random() < 0.01;

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

#1 楼

给定您的规范（加上注释中的其他信息），


您有一个数字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 AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass;

只要ct不比id_span小很多，查询就会胜过其他方法。

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）。
将id加入大表。有了适当的索引，这应该很快。
最后修剪未被伪造和缺口吃掉的多余id。每行都有完全相等的机会被选择。

短版本

您可以简化此查询。上面查询中的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
   )
SELECT *
FROM   random_pick
LIMIT  1000;  -- actual limit

我们可以在基本查询中使用较小的盈余。如果间隙太多，那么在第一次迭代中我们找不到足够的行，则rCTE会继续使用递归项进行迭代。我们仍然需要在ID空间中保持相对较少的间隙，否则在达到限制之前递归可能会枯竭-或者我们必须从足够大的缓冲区开始，这不利于优化性能。



外部UNION使CTE一旦我们有足够的行就立即停止。

此查询是为使用可用索引精心设计的，生成实际上是随机的行，直到我们达到限制为止（除非递归耗尽），它才会停止。如果要重写它，这里有很多陷阱。

包装到函数中

重复使用各种参数：

CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03)
  RETURNS SETOF big AS
$func$
DECLARE
   _surplus  int := _limit * _gaps;
   _estimate int := (           -- get current estimate from system
      SELECT c.reltuples * _gaps
      FROM   pg_class c
      WHERE  c.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
   )
   SELECT *
   FROM   random_pick
   LIMIT  _limit;
END
$func$  LANGUAGE plpgsql VOLATILE ROWS 1000;

调用：

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

您甚至可以使此泛型适用于任何表：以PK列和表的名称为多态类型并使用LIMIT ...但这超出了此问题的范围。请参阅：


重构PL / pgSQL函数以返回各种SELECT查询的输出

可能的选择

如果您的要求允许相同的重复调用集（我们正在谈论重复调用）我将考虑一个物化视图。一次执行上述查询，然后将结果写入表中。用户以闪电般的速度获得准随机选择。在选定的时间间隔或事件中刷新随机选择。

Postgres 9.5引入了EXECUTE


其中TABLESAMPLE SYSTEM (n)是百分比。手册：


n和BERNOULLI采样方法均接受单个
参数，该参数是要采样的表的分数，表示为0之间的百分比。和100。此参数可以是任何SYSTEM值表达式。


大胆强调我的。速度非常快，但结果并非完全随机。再次说明手册：指定采样率较小时，real方法显着快于SYSTEM方法
，但它可能会返回
随机性较低的示例表的聚集效果。


返回的行数可以有很大的不同。对于我们的示例，要获取大约1000行：

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

相关：


查找a行数的快速方法PostgreSQL中的表格

，或者安装其他模块tsm_system_rows以获得确切的请求行数（如果有的话），并允许使用更方便的语法：

SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

有关详细信息，请参见Evan的答案。

但这仍然不是完全随机的。

#2 楼

您可以通过使用

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

检查并比较两者的执行计划，对大型表进行快速测试1显示，ORDER BY首先对完整表进行排序，然后选择前1000个项目。对大型表进行排序不仅可以读取该表，还可以读取和写入临时文件。 where random() < 0.1只扫描一次完整的表。

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

第三个建议是

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

一旦找到1000行，该表就会停止表扫描，因此会更快地返回。当然，这会稍微降低随机性，但是对于您的情况，这也许就足够了。

编辑：除了这些考虑因素之外，您还可以查看有关此问题的信息。使用查询[postgresql] random返回相当多的命中。


在Postgres中快速选择随机行
如何从postgreSQL表中检索随机数据行？
postgres：获取表中的随机条目-太慢了

和depez的链接文章概述了其他几种方法：


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


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

#3 楼

通过random（）进行PostgreSQL排序，以随机顺序选择行：

select your_columns from your_table ORDER BY random()

通过random（）进行postgresql排序，并带有不同的：

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

#4 楼

从PostgreSQL 9.5开始，有一种新语法专用于从表中获取随机元素：

SELECT * FROM mytable TABLESAMPLE SYSTEM (5);

本示例将为您提供qq12020qq元素的5％。
有关文档的更多说明： http://www.postgresql.org/docs/current/static/sql-select.html

#5 楼

具有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中实现混洗听起来确实是个挑战。

#6 楼

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);

#7 楼

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

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

#8 楼

如果只需要一行，则可以使用从offset派生的计算得出的count。

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

#9 楼

可能会有Erwin Brandstetter概述的物化视图“可能的选择”的变体。

例如，您不希望返回的随机值重复。因此，您需要在包含您的（非随机）值集的主表上设置一个布尔值。

假设这是输入表：

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中选择id_values_randomized连接，并应用所需的条件仅获得相关的可能性。例如：

 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 5)
RETURNING id;
 

根据需要更改LIMIT －如果只需要一个随机值一次将LIMIT更改为1。

在id_values上使用适当的索引，我相信UPDATE-RETURNING应该在很小的负载下非常快速地执行。它通过一次数据库往返返回随机值。 “合格”行的条件可以根据要求复杂。可以随时将新行添加到id_values表中，并且只要刷新实例化视图（它们可能在非高峰时间运行），应用程序就可以访问它们。物化视图的创建和刷新将很慢，但是仅当将新ID添加到id_values表中时才需要执行。

#10 楼

根据我的经验教训：

offset floor(random() * N) limit 1并不比order by random() limit 1快。

我认为offset方法会更快，因为它可以节省在Postgres中进行排序的时间。原来不是。

#11 楼

添加名为r的列，类型为serial。索引r。

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

选择带有r > n的行，对它们进行排序并选择最小的一个。

代码：

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快速估算表行数。

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

#12 楼

我知道我参加聚会有点晚了，但是我刚刚发现了这个很棒的工具pg_sample：


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


我使用350M行数据库进行了尝试，它的速度非常快，不了解随机性。

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