sql - 选择随机行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(*) (对于大 table )。鉴于上述规范，您不需要它。估计就可以了，几乎可以免费获得( detailed explanation here):

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 s 到大 table 。有了索引，这应该非常快。

最后修剪剩余id没有被骗子和差距吃掉的s。每行都有一个 完全平等的机会被挑选。

简洁版本
您可以 简化 这个查询。上述查询中的 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 空间中相对较少的间隙，否则递归可能会在达到限制之前干涸 - 或者我们必须从一个足够大的缓冲区开始，这违背了优化性能的目的。 UNION 消除了重复项在 rCTE 中。
外 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 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
   )
   TABLE  random_pick
   LIMIT  _limit;
END
$func$;

称呼:

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

你甚至可以使这个泛型适用于任何表:将 PK 列的名称和表作为多态类型并使用 EXECUTE ...但这超出了这个问题的范围。看:

Refactor a PL/pgSQL function to return the output of various SELECT queries

可能的替代方案
如果您的要求允许 重复的相同集电话(我们正在谈论重复电话)我会考虑 物化 View .执行一次上述查询并将结果写入表。用户以闪电般的速度获得准随机选择。每隔一段时间或您选择的事件刷新您的随机选择。
Postgres 9.5 引入 TABLESAMPLE SYSTEM (n)
在哪里 n是一个百分比。 The manual:

The BERNOULLI and SYSTEM sampling methods each accept a singleargument which is the fraction of the table to sample, expressed as apercentage between 0 and 100. This argument can be any real-valued expression.

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

The SYSTEM method is significantly faster than the BERNOULLI methodwhen small sampling percentages are specified, but it may return aless-random sample of the table as a result of clustering effects.

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

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

有关的:

Fast way to discover the row count of a table in PostgreSQL

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

SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

见 Evan's answer详情。
但这仍然不是完全随机的。