选择随机行PostgreSQL的最佳方法

Question

我想要在PostgreSQL中随机选择行，我尝试了以下方法：

select * from table where random() < 0.01;

但是其他一些建议：

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

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

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

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 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）。

• 将ids加入大表。有了适当的索引，这应该非常快。

• 最后，修剪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在rCTE中被消除。

LIMIT一旦我们有足够的行，外部将使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列的名称和表作为多态类型并使用EXECUTE…但这超出了此问题的范围。

可能的选择

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

Postgres 9.5简介 [`TABLESAMPLE SYSTEM

(n)`](https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM)

n 百分比在哪里。手册：

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

大胆强调我的。这是 非常快的 ，但结果 并非完全是随机的 。再次手册：

该SYSTEM方法比BERNOULLI指定较小采样百分比时的方法要快得多，但是由于聚类的影响，它可能会返回表中的随机性较低的样本。

返回的行数可能相差很大。对于我们的示例，要获得 大约 1000行：

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

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

SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

但这还不是完全随机的。