如何在 PostgreSQL 中高效地选择随机行？

PostgreSQL高效随机行选择方法

在PostgreSQL中选择随机行，最佳方法取决于表的大小、可用索引以及所需的随机性级别。

对于拥有5亿行且包含数值ID列（例如，id）的超大型表：

• 最快方法：

  • 使用CTE和random()函数在ID空间内生成随机ID。
  • 使用id列将生成的ID与表连接。
  • 过滤掉重复项并去除多余的ID。

<code class="language-sql">WITH params AS (
   SELECT 1       AS min_id,           -- 最小id
        , 5100000 AS id_span          -- 四舍五入。(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                        -- 去除重复项
) r
JOIN   big USING (id)
LIMIT  1000;                          -- 去除多余项</code>

• 改进方法：

  • 使用递归CTE (random_pick) 消除ID空间中的任何间隙。
  • 合并递归结果以消除重复项。
  • 应用外部LIMIT以满足限制条件。

<code class="language-sql">WITH RECURSIVE random_pick AS (
   SELECT *
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   generate_series(1, 1030)  -- 1000 + 百分之几 - 根据需要调整
      LIMIT  1030                      -- 查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失

   UNION                               -- 消除重复项
   SELECT b.*
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   random_pick r             -- 加上百分之三 - 根据需要调整
      LIMIT  999                       -- 小于1000，查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失
   )
TABLE  random_pick
LIMIT  1000;  -- 实际限制</code>

• 通用函数：

  • 将上述查询包装到一个函数中，以便可以对任何具有唯一整数列的表重用它们。

<code class="language-sql">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
   _tbl text := pg_typeof(_tbl_type)::text;
   _estimate int := (...);
BEGIN
   RETURN QUERY EXECUTE format(
   $$
   WITH RECURSIVE random_pick AS (
      SELECT ...
      FROM  ...
     ...
   )
   TABLE  random_pick
   LIMIT  ;
   $$
 , _tbl, _id
   )
   USING (...);
END
$func$;</code>

对于不需要精确随机性或重复调用的场景：

• 物化视图：

  • 创建一个物化视图来存储近似随机选择的行。
  • 定期刷新物化视图。

• TABLESAMPLE SYSTEM (n)：

  • 在PostgreSQL 9.5中引入，TABLESAMPLE SYSTEM (n)提供了一种快速且非精确的随机抽样方法。
  • n参数表示要抽样的表百分比。

<code class="language-sql">SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);</code>

其他注意事项：

• 为获得最佳性能，请在ID列上使用索引。
• PostgreSQL中的random()函数不是密码学安全的。
• 建议的方法为大多数实际用例提供了高度的随机性。

以上是如何在 PostgreSQL 中高效地选择随机行？的详细内容。更多信息请关注PHP中文网其他相关文章！