选择随机行PostgreSQL的最佳方法

Question

我想在 PostgreSQL 中随机选择行，我试过这个：

 select * from table where random() < 0.01;

但其他一些人建议这样做：

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

我有一个非常大的表，有 5 亿行，我希望它快。

哪种方法更好？有什么区别？选择随机行的最佳方法是什么？

原文由 nanounanue 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 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 / relpages * (pg_relation_size(oid) / 8192))::bigint AS ct
FROM   pg_class
WHERE  oid = 'big'::regclass;  -- your table name

详细解释：

• 在 PostgreSQL 中发现表的行数的快速方法

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

与静态版本的性能大致相同。

有关的：

• 重构 PL/pgSQL 函数以返回各种 SELECT 查询的输出- “各种完整表类型” 一章
• 从 PostgreSQL 函数返回 SETOF 行
• 执行格式（）中整数变量的格式说明符？
• 在触发器函数中插入动态表名

这对 SQL 注入是安全的。看：

• 表名作为 PostgreSQL 函数参数
• Postgres 函数中的 SQL 注入与准备好的查询

可能的替代方案

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

Postgres 9.5 引入 TABLESAMPLE SYSTEM (n)

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

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

大胆强调我的。它 _非常快_，但结果 _并不完全随机_。再看说明书：

当指定较小的采样百分比时， SYSTEM 方法明显快于 BERNOULLI 方法，但由于聚类效应，它可能会返回表的随机样本较少。

返回的行数可以变化很大。对于我们的示例，要获得 大约 1000 行：

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

有关的：

• 在 PostgreSQL 中发现表的行数的快速方法

或者 安装附加模块 tsm_system_rows 以准确获取请求的行数（如果有足够的）并允许使用更方便的语法：

 SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

有关详细信息，请参阅 埃文的答案。

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

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我认为 postgreSQL 中最好的方法是：

 SELECT * FROM tableName ORDER BY random() LIMIT 1

原文由 Saurabh Agrawal 发布，翻译遵循 CC BY-SA 4.0 许可协议