Postgres 的快速随机行选择

我在 postgres 中有一个包含几百万行的表。我在网上查了一下，发现了以下内容

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

它工作，但它真的很慢... 是否有另一种方法，使查询，或直接选择一个随机行，而不读取所有的表？顺便说一下，“ myid”是一个整数，但它可以是一个空字段。

83948 次浏览
小开
最佳答案

您可能需要尝试使用 OFFSET，比如

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

N是 mytable中的行数。您可能需要先做一个 SELECT COUNT(*)来计算出 N的值。

更新 (by Antony Hatchkins)

你必须在这里使用 floor:

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

考虑一个包含2行的表; random()*N生成 0 <= x < 2，例如，由于隐式四舍五入到最接近的 int，SELECT myid FROM mytable OFFSET 1.7 LIMIT 1;返回0行。

小开

查看这个链接以获得一些不同的选项。 Http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

更新: < em > (A.Hatchkins)

这篇(非常)长的文章的摘要如下。

作者列举了四种方法:

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

2) ORDER BY id where id>=random()*N LIMIT 1-如果有间隙就不均匀

3) random column -- needs to be updated every now and then

4)自定义 随机聚集体随机聚集体——狡猾的方法，可以很慢: 随机()需要生成 N 次

并建议改进方法二

5) ORDER BY id where id=random()*N LIMIT 1 如果结果为空，则使用后续请求。

小开

我用子查询尝试了一下，它工作得很好，至少在 Postgresql v8.4中是这样的。

select * from mytable offset random() * (select count(*) from mytable) limit 1 ;
小开

你需要使用 floor:

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

PostgreSQL 9.5为更快的示例选择引入了一种新方法: 桌子样本

语法是

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

如果您只希望选择一行，那么这不是最佳解决方案，因为您需要知道表的 COUNT 来计算确切的百分比。

为了避免计数速度慢，并对从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，请阅读 http://blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2/处的差异

小开

我想出了一个没有 TABLESAMPLE的非常快的解决方案。比 OFFSET random()*N LIMIT 1快得多。它甚至不需要计算表。

The idea is to create an expression index with random but predictable data, for example md5(primary key).

下面是一个包含1M 行示例数据的测试:

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

这个查询有时可以(大约1/Number _ of _ rows 概率)返回0行，因此需要检查并重新运行它。而且概率也不完全相同——有些行比其他行更有可能。

作为比较:

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)
小开

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

CREATE EXTENSION IF NOT EXISTS tsm_system_rows;

Then you can select the exact number of rows you want :

SELECT myid  FROM mytable TABLESAMPLE SYSTEM_ROWS(1);

这在 PostgreSQL 9.5及更高版本中可用。

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


提交答案