在 PostgreSQL 中经常需要从表中获取单个随机行或几行以获取代表性数据，执行此操作的最常见方法是使用ORDER BY random()类似：
SELECT id FROM data ORDER BY random() LIMIT 1
复制但是当在大表上运行时，这可能会非常慢，因为它必须扫描整个表才能找到行。Jonathan Katz在 Twitter 上提到了一种不同的方法，这提醒我人们不断想出不同的（有时是非常复杂的）方法来尝试解决这个问题。
虽然乔纳森的方法（他有超级简单的示例代码，结果在一个要点上）仍然比ORDER BY random()我的测试（使用他的数据）快两倍，但它带来了一些问题。例如，它需要一组连续的id值，这些值必须是整数。在我的机器上运行他的 500 万行样本仍然需要大约一秒钟的时间——并且随着表的增长会越来越慢。
事实证明，如果你不需要你的行完全随机，只是大部分随机，并且可以处理一些警告，PostgreSQL 有内置功能，它比 Jonathan 的版本快 20,000 倍，快 40,000 倍比ORDER BY random()。输入TABLESAMPLE。
TABLESAMPLE是一种旨在返回表的样本部分的功能，例如10%. 但是使用插件tsm_system_rows（默认包含在 PostgreSQL 中，但未激活），我们可以返回样本行数。扫描的性能并不完全独立于表的大小，而是几乎，即使在非常大的表上，如果您只需要一行，它也会运行得非常快。
那么它是怎样工作的？
CREATE EXTENSION tsm_system_rows ;
SELECT id FROM data TABLESAMPLE system_rows(1);
复制在我的机器上，使用 Jonathans 的 500 万个 bigint 样本，我的运行时间是：
- ORDER BY random() ~2 秒
- 乔纳森的方法~1秒
- TABLESAMPLE ~0.05 毫秒
随着表格的增加，差异只会变得更大。例如，再向表中添加 1000 万行，现在我得到：
- ORDER BY random() ~ 4-4.5 秒
- 乔纳森的方法 - ~ 2.7-3 秒
- TABLESAMPLE ~0.05 毫秒
（TABLESAMPLE在我的测试中，的执行时间很短，由于分辨率的原因，它们变化很大，但从未超过 0.1 毫秒）。
那么这个“魔法”是如何运作的呢？
该函数的 system_rows版本TABLESAMPLE将在表中随机选择一个磁盘块，然后从那里依次获取行。只需查看表的大小即可选择随机块，因此速度非常快。
只要你只得到一行就可以了。但是如果你得到不止一行，如果你想要随机性，你可能会得到不好的结果：
postgres=# SELECT id FROM data ORDER BY random() LIMIT 3;
id
----------
4545431
772665
12743060
(3 rows)
postgres=# SELECT id FROM data TABLESAMPLE system_rows(3);
id
---------
4815157
4815158
4815159
(3 rows)
复制注意第一行是如何随机选取的，但接下来的行是连续的。
如果您获取的行比该页面上的多，PostgreSQL 将选择另一个随机块。所以你会得到一组连续的行，但这些集合本身是随机的。这些集合的大小将取决于表中行的宽度，因为它控制着每页上的行数。
PostgreSQL 还提供了许多其他方法来对您的表进行采样（作为 PostgreSQL，您当然可以编写自己的，但这是获取该行的快速方法。
那么有什么注意事项呢？肯定不会有那么神奇吧？
需要注意的是，TABLESAMPLE仅适用于表的实际扫描，不能直接与 WHERE 子句结合使用。例如，假设我想从表中随机获取偶数行。使用常规方法很容易：
SELECT id FROM data WHERE id % 2 = 0 ORDER BY random()
复制但是，如果我跑
SELECT id FROM data TABLESAMPLE system_rows(1) WHERE id % 2 = 0;
复制一半的时间我会得到零行。这是因为 theTABLESAMPLE将首先返回一行，然后如果该行不是，则该WHERE子句将把它扔掉。
我们可以使用以下类似的方法来降低可能性，它仍然在 0.1 毫秒内运行。
WITH t AS (
SELECT id FROM data TABLESAMPLE system_rows(10)
)
SELECT id FROM t WHERE id % 2 = 0 LIMIT 1
复制如果在内部扫描中选择的额外行数足够大，这将“几乎一直”成功。但是，根据数据的分布，在某些运行中您仍然可能会得到零行。
因此，如果您在过滤器之后需要确切数量的行，这不是一个足够好的解决方案，除非您的应用程序愿意重新运行它的查询。但是随着数万倍的加速，根据用例，实际上可能值得重新运行几次查询，直到一行回来。
TABLESAMPLE以及tsm_system_rows在所有支持的 PostgreSQL 版本中都可用。该system和bernoulli获得表的一定比例的方法在标准SQL，该system_rows方法是PostgreSQL扩展。
原文地址：https://blog.hagander.net/getting-random-rows-faster-very-much-faster-249/
