PostgreSQL选择随机行的最佳方法

Question

PostgreSQL选择随机行的最佳方法

488

我想在PostgreSQL中随机选择行，我尝试了以下代码：

select * from table where random() < 0.01;

但是其他人建议这样做：

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

我有一个有500万行的非常大的表格，我希望它运行速度很快。

哪种方法更好？它们之间有什么差异？选择随机行的最佳方法是什么？

- nanounanue

2

嗨，杰克，感谢您的回复。按顺序执行的时间较慢，但我想知道是否有什么不同... - nanounanue

嗯...不用谢。那么，你尝试过对不同的方法进行基准测试吗？ - user554546

还有*更快的方式。这完全取决于您的需求和您拥有的工具。您需要确切地1000行吗？表格是否具有数字ID？没有/很少/有许多间隔吗？速度有多重要？每个时间单位有多少请求？每个请求是否需要不同的设置，或者它们可以在定义的时间片段内相同？ - Erwin Brandstetter

10

第一个选项“(random() < 0.01)”在数学上是不正确的，因为如果没有随机数小于0.01，则响应中可能没有行，在任何情况下都可能发生（尽管不太可能），无论表有多大或门槛有多高。第二个选项总是正确的。 - Herme

2

如果您只想选择一行，请参阅此问题：https://dev59.com/HG435IYBdhLWcg3wpxyw - Flimm

显示剩余2条评论

13个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Raman · Answer 1

“Possible alternative” 物化视图的变体由 Erwin Brandstetter 概述是可能的。

例如，假设您不想在返回的随机值中有重复。一个示例用例是生成只能使用一次的短代码。

包含您的（非随机化）值集的主表必须具有确定哪些行是“已使用”的表达式和哪些行是“未使用”的 —— 在这里我将简单地创建一个名为 used 的布尔列。

假设这是输入表（可以添加其他列，因为它们不会影响解决方案）：

id_values  id  |   used
           ----+--------
           1   |   FALSE
           2   |   FALSE
           3   |   FALSE
           4   |   FALSE
           5   |   FALSE
           ...

根据需要填充 ID_VALUES 表。然后，按照 Erwin 的描述，创建一个材料化视图，对 ID_VALUES 表进行随机排序：

CREATE MATERIALIZED VIEW id_values_randomized AS
  SELECT id
  FROM id_values
  ORDER BY random();

请注意，物化视图不包含已使用的列，因为这将很快过时。视图也不需要包含可能在id_values表中的其他列。

为了获取（并“消耗”）随机值，请在id_values上使用UPDATE-RETURNING，在连接中从id_values_randomized选择id_values，并应用所需的条件以仅获取相关的可能性。例如：

UPDATE id_values
SET used = TRUE
WHERE id_values.id IN 
  (SELECT i.id
    FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
    WHERE (NOT i.used)
    LIMIT 1)
RETURNING id;

根据需要更改LIMIT -- 如果您需要一次获取多个随机值，请将LIMIT更改为所需值的数量n。

如果在id_values上有适当的索引，我相信UPDATE-RETURNING应该非常快速地执行，负载很小。它通过一个数据库往返返回随机值。 "可用"行的标准可以是任意复杂的条件。可以随时向id_values表中添加新行，并且只要刷新物化视图（可能可以在非高峰时间运行），它们就会变得可访问。物化视图的创建和刷新将很慢，但只需要在需要使新的ID可用于id_values表时执行它。

- Daniel Gerber · Answer 2

我知道我来晚了，但我刚刚发现了这个很棒的工具叫做pg_sample：

pg_sample - 从一个较大的PostgreSQL数据库中提取一个小的样本数据集，同时保持引用完整性。

我尝试过在一个有350M行的数据库上使用它，速度非常快，不知道关于“随机性”怎么样。

./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db

- MK Yung · Answer 3

添加一个名为r的列，类型为serial。索引r。

假设我们有200,000行数据，我们将生成一个随机数n，其中0 < n <= 200,000。

选择r > n的行，按ASC排序并选择最小的一行。

代码：

select * from YOUR_TABLE 
where r > (
    select (
        select reltuples::bigint AS estimate
        from   pg_class
        where  oid = 'public.YOUR_TABLE'::regclass) * random()
    )
order by r asc limit(1);

代码本身很容易理解。中间的子查询用于快速估算来自 https://dev59.com/wGsz5IYBdhLWcg3wYGkz#7945274 的表行数。

在应用程序级别，如果 n > 行数或需要选择多行，则需要再次执行该语句。