logo标签列表

Postgres中快速随机行选择

postgresqlrandom
116

我在Postgres中有一个包含数百万行的表。我在互联网上查了一下，找到以下内容：

SELECT myid FROM mytable ORDER BY RANDOM() LIMIT 1;

它可以工作，但速度非常慢...是否有另一种方法来进行查询，或者直接选择一个随机行而不必读取整个表格？顺便说一下，'myid'是一个整数，但可能是一个空字段。

2
如果您想选择多个随机行，请参考此问题: https://dev59.com/4Woy5IYBdhLWcg3wQrxh - Flimm
8个回答
121

您可能想尝试使用OFFSET，例如：

SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;

N 是 mytable 表中的行数。你可能需要先执行 SELECT COUNT(*) 来确定 N 的值。

更新（由 Antony Hatchkins 提供）

你必须在这里使用 floor：

SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;
考虑一个有2行的表格；random()*N生成0 <= x < 2，例如SELECT myid FROM mytable OFFSET 1.7 LIMIT 1;返回0行，因为隐式四舍五入取整。
1
使用小于“SELECT COUNT(*)”的N是否有意义呢？我的意思是，不使用表中的所有值，而只使用其中的一部分？ - Juan
如果使用EXPLAIN SELECT ...并且对于查询不同的N值得到相同的成本，那么我猜最好选择最大的N值。 - Juan
4
请看下面我回答中的错误修复。 - Antony Hatchkins
2
这里有一个 off by one 的错误。它永远不会返回第一行，并且会生成一个 1/COUNT(*) 的错误，因为它会尝试返回最后一行之后的行。 - Ian
如果N = 记录总数呢？那么你将会扫描整个表。 - tkhuynh
显示剩余2条评论
78

PostgreSQL 9.5 引入了一种更快的样本选择方法：TABLESAMPLE。

语法如下：

SELECT * FROM my_table TABLESAMPLE BERNOULLI(percentage);
SELECT * FROM my_table TABLESAMPLE SYSTEM(percentage);

这不是最佳解决方案，如果你只想选择一行的话，因为你需要知道表的计数来计算准确的百分比。
为了避免慢速计数并且对于从1行到数十亿行的表使用快速TABLESAMPLE，你可以这样做：
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.000001) LIMIT 1;
 -- if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.00001) LIMIT 1;
 -- if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.0001) LIMIT 1;
 -- if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.001) LIMIT 1;
 ...

这可能看起来不太优雅，但可能比其他答案更快。
要决定是否要使用BERNULLI或SYSTEM，请阅读关于差异的内容https://www.2ndquadrant.com/en/blog/tablesample-in-postgresql-9-5-2/。
2
这比任何其他答案都要快得多，也更容易--应该把它放在最顶部。 - Hayden Schiff
1
为什么不能只使用子查询来获取计数？SELECT * FROM my_table TABLESAMPLE SYSTEM(SELECT 1/COUNT(*) FROM my_table) LIMIT 1;？ - machineghost
2
@machineghost "为了避免慢速COUNT..." ... 如果你的数据很小，可以在合理的时间内计数，那就去做吧！ :-) - alfonx
2
@machineghost 使用 SELECT reltuples FROM pg_class WHERE relname = 'my_table' 进行计数估算。 - Hynek -Pichi- Vychodil
@Hynek-Pichi-Vychodil非常好的输入！为了确保估计不过时，最近必须进行VACUUM ANALYZE。但是一个好的数据库应该被适当地分析。这完全取决于具体的用例。通常，大型表不会增长得那么快...谢谢！ - alfonx
显示剩余2条评论
36

我尝试使用子查询，效果很好。在Postgresql v8.4.4中，偏移量(offset)运作良好。

select * from mytable offset random() * (select count(*) from mytable) limit 1 ;
事实上，v8.4对于此操作是必不可少的，<=8.3无法正常工作。 - Antony Hatchkins
1
请查看我下面的答案中的错误修复。 - Antony Hatchkins
32
您需要使用 floor:
SELECT myid FROM mytable OFFSET floor(random()*N) LIMIT 1;
考虑一个有2行的表格；random()*N生成0 <= x < 2，例如SELECT myid FROM mytable OFFSET 1.7 LIMIT 1;由于隐式四舍五入到最近的整数，返回0行。 - Antony Hatchkins
1
三个连续的查询仍然比一个 order by random() 更快，大约为 3*O(N) < O(NlogN) - 由于索引，实际数据可能会略有不同。 - Antony Hatchkins
1
你或者其他人能否扩展一下这个答案，回答一下为什么我需要使用 floor()？它有什么优势呢？ - ADTC
“floor(random()*N)”保证是0..N-1，而不是N。 - Antony Hatchkins
这是正确的解决方案，如果N=1时没有使用floor函数，那么我得到的数字将在0和1之间。有时这会给我零行，有时它会给我一行，我假设这是因为Postgres隐式地四舍五入到最近的整数与OFFSET。 - Will Sewell
显示剩余4条评论
16

点击以下链接以查看不同选项。 http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

更新: (A.Hatchkins)

这篇（非常）长的文章总结如下。

作者列举了四种方法：

1) ORDER BY random() LIMIT 1; -- 慢

2) ORDER BY id where id>=random()*N LIMIT 1 -- 如果存在间隔，则不均匀

3) 随机列 -- 需要不时地更新

4) 自定义随机聚合 -- 狡猾的方法，可能很慢：需要生成 N 次 random()

并建议通过使用以下方式来改进方法 #2

5) ORDER BY id where id=random()*N LIMIT 1 如果结果为空，则进行后续查询。

我想知道为什么他们没有涵盖OFFSET？使用ORDER来获取随机行是不可行的。幸运的是，答案中很好地涵盖了OFFSET。 - androidguy
8

获取随机行最简单、最快的方法是使用tsm_system_rows扩展：

CREATE EXTENSION IF NOT EXISTS tsm_system_rows;

然后您可以选择所需的确切行数：

SELECT myid  FROM mytable TABLESAMPLE SYSTEM_ROWS(1);

此功能仅适用于 PostgreSQL 9.5 及以上版本。

请参见：https://www.postgresql.org/docs/current/static/tsm-system-rows.html

7
提醒一下，这并不完全是随机的。在较小的表格上，我让它始终按顺序返回第一行数据。 - Ben Aubin
3
是的，这在文档中已经清楚地解释了（上面的链接）：“与内置的SYSTEM采样方法一样，SYSTEM_ROWS执行块级采样，因此样本不是完全随机的，而可能受到聚类效应的影响，特别是如果只请求少量行。” 如果您有一个小数据集，ORDER BY random() LIMIT 1; 应该足够快。 - daamien
我看到了。只是想让那些不点击链接或者链接在未来失效的人清楚明白。 - Ben Aubin
3
值得注意的是，这仅适用于从表中选择随机行，然后进行过滤，而不是运行查询，然后随机选择一个或多个记录。 - nomen
3

我想到了一种非常快速的解决方案，不需要使用TABLESAMPLE，比OFFSET random()*N LIMIT 1快得多。它甚至不需要表格计数。

这个想法是创建一个带有随机但可预测数据的表达式索引，例如md5(primary key)。

这里是一个包含100万行样本数据的测试：

create table randtest (id serial primary key, data int not null);

insert into randtest (data) select (random()*1000000)::int from generate_series(1,1000000);

create index randtest_md5_id_idx on randtest (md5(id::text));

explain analyze
select * from randtest where md5(id::text)>md5(random()::text)
order by md5(id::text) limit 1;

结果：

 Limit  (cost=0.42..0.68 rows=1 width=8) (actual time=6.219..6.220 rows=1 loops=1)
   ->  Index Scan using randtest_md5_id_idx on randtest  (cost=0.42..84040.42 rows=333333 width=8) (actual time=6.217..6.217 rows=1 loops=1)
         Filter: (md5((id)::text) > md5((random())::text))
         Rows Removed by Filter: 1831
 Total runtime: 6.245 ms

这个查询有时会返回0行数据（概率约为1/总行数），因此需要进行检查和重新运行。此外，各行的概率并不完全相同 - 有些行比其他行更可能出现。

作为对比：

explain analyze SELECT id FROM randtest OFFSET random()*1000000 LIMIT 1;

结果因情况而异，但可能会非常糟糕：

 Limit  (cost=1442.50..1442.51 rows=1 width=4) (actual time=179.183..179.184 rows=1 loops=1)
   ->  Seq Scan on randtest  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.016..134.835 rows=915702 loops=1)
 Total runtime: 179.211 ms
(3 rows)
3
快速，没错。真正的随机性，不是很好。一个MD5值恰好比另一个现有值大的值有非常小的被选中的机会，而在数字空间中有一个很大的间隔之后的值则有更大的机会（由于其中可能存在的值的数量更多）。由此产生的分布并不是随机的。 - Erwin Brandstetter
非常有趣，这个技术可用于类似彩票查询的用例：查询必须查看所有可用的票并随机返回仅一个单独的票。 另外，我能否在您的技术中使用悲观锁（select ... for update）？ - Mathieu
对于任何与彩票相关的事情，您应该真正使用公平且具有密码学安全性的随机抽样 - 例如，在1和max（id）之间选择一个随机数字，直到找到现有的id。此答案中的方法既不公平也不安全 - 它很快。可用于诸如“获取随机1％的行以测试某些内容”或“显示随机5个条目”的事情。 - Tometzky
0

我在每一行中添加了一个随机生成的数字，并在我的编程语言中生成一个随机数，将其添加到每一行中。 在调用时，我将一个随机数传递给查询（在本例中为0.27）

SELECT * FROM
(
  (SELECT id, random FROM t where <condition> and random >= 0.27 ORDER BY random LIMIT 1)
  UNION ALL
  (SELECT id, random FROM t where <condition> and random < 0.27 ORDER BY random DESC LIMIT 1)
) as results
ORDER BY abs(0.27-random) LIMIT 1;

（查询来自这里）

如果您在条件和随机行（包含随机数）上有一个索引，我可以在我的850万行表格上在6毫秒内获得结果。这比使用类似于order by random()的任何东西要快几个数量级。

为了改善随机性，您还可以为每个命中的结果生成一个新的随机数。（如果没有这个，某些数字会比其他数字更频繁地出现。）

与TABLESAMPLE不同，这也支持条件。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接