Select 随机行PostgreSQL的最佳方法

根据您的规格(加上 comments 中的其他信息)，

• 您有一个数字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(*)(用于大桌子).鉴于以上规格，您不需要它.一份估价就可以了，几乎不需要花费(detailed explanation here美元):

SELECT reltuples AS ct FROM pg_class
WHERE oid = 'schema_name.big'::regclass;

只要ct不比id_span小much，查询的性能就会优于其他方法.

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个没有被重复和缺口吃掉的部分.每一排都有一个completely equal chance.

短版

你可以在这个查询中输入simplify.上述查询中的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

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

重复项由rCTE中的UNION项消除.

当我们有足够的行数时，外部LIMIT使CTE停止.

这个查询是精心设计的，以使用可用的索引，生成实际上是随机的行，并且在我们达到限制之前不会停止(除非递归没有运行).如果你要重写它，这里有很多trap .

包装成函数

对于不同参数的重复使用:

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

可能的替代方案

如果您的要求允许identical sets for repeated个呼叫(我们正在谈论重复呼叫)，我会考虑materialized view个.执行上述查询一次，并将结果写入表中.用户以闪电般的速度获得准随机 Select .在你 Select 的时间间隔或事件中刷新你的随机 Select .

Postgres 9.5 introduces TABLESAMPLE SYSTEM (n)

其中101是一个百分比.The manual:

BERNOULLI和SYSTEM种抽样方法都接受一个

我的.它是very fast，但结果是not exactly random.再看一下手册:

SYSTEM法比BERNOULLI法快得多

返回的行数可能相差很大.对于我们的示例，要获得roughly行和roughly0行:

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

相关的:

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

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

SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);

详情见Evan's answer.

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